How to ensure MySQL selects do not interfere?
I have a table in my MySQL DB which basically contains "cron"-like tasks. Basically a user visits a page and the script (php) checks the DB cron table, gets the latest 5 results that are "available" and executes the scrip开发者_如何学编程ts related to the tasks.
Only issues I foresee at the moment is that 2 users might get the same tasks. Note that currently I first run an UPDATE query which assigns 5 tasks to the current user. After that I do a SELECT query to get 5 tasks assigned to the current user and when hes done I mark the tasks as completed.
Theoretically no 2 users should ever get the same tasks but I'm uncertain. I'm simple wondering if MySQL possibly has a build in mechanism to ensure this or if there are known methods for it?
Thanks.
You want to use Transactions. This way you can ensure that a multi-step operation, such as [UPDATE, SELECT, UPDATE] is either wholly completed, or does not happen at all.
This is a classic concurrency problem, it's worth reading up about concurrency and transactions in general so that you understand the principals. This will help you avoid problems down the line (there are lots of knotty problems in concurrency!).
精彩评论