Ensuring app SQL select statement retrieve different rows

Question

I have a MySQL InnoDB table containing "tickets". Tickets are either 'open', 'locked', 'closed'. I have multiple remote client apps that run select queries on this table for 'open' tickets. I want to ensure that the different clients don't retrieve the same rows.

My process flow right now is:

Issue select for open... Update table set status to locked... Do stuff with the ticket Update table set status to closed.

Obviously there's a huge gaping race condition hole there between the time a client selects and open ticket and then locks it. How do I prevent other clients accessing the same row in a non-blocking manner?

Answer 1

you can try using stored procedure