What’s the best way of implementing a messaging queue table in mysql

Your dequeue could be more concise. Rather than relying on the transaction rollback, you could do it in one atomic statement without an explicit transaction:

UPDATE jobs SET process_id = ? WHERE process_id IS NULL ORDER BY ID ASC LIMIT 1;

Then you can pull jobs with (brackets [] mean optional, depending on your particulars):

SELECT * FROM jobs WHERE process_id = ? [ORDER BY ID LIMIT 1];