Ensuring app SQL select statement retrieve different rows
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?
精彩评论