A MySQL table can be seen as a queue, with each act an element. Each query gets the first line that satisfies a condition, deletes it from the table, or changes its state so that the next query does not get it. In the absence of concurrent access, it is possible to simply get a row with a SELECT and then modify it with the update (or delete) statement.
Copy Code code as follows:
SELECT * from targets WHERE status= ' C ' LIMIT 1;
UPDATE targets SET status= ' D ' WHERE id= ' id ';
If there is concurrent access, there may be other select queries between the SELECT and UPDATE statements, causing the same row to be fetched more than once. In order to ensure that the concurrency is still working, one idea is to use database lock to prevent, just like in a multithreaded environment to do the same. In short, if the query and modify for an atomic operation, not by other access interference. MySQL 5 supports stored procedures that you can use to implement them.
A single UPDATE statement should be atomic, and you can use this feature to ensure that queues work properly under concurrent access. Each time you take an element, you modify the first line that meets the criteria with UPDATE, and then you get the row. Unfortunately, the UPDATE statement does not return a value, and it is difficult to find the record that has just been changed again with a normal select.
Here's a tip: add id=last_insert_id (ID) to UPDATE, and then use SELECT last_insert_id () to get the ID of the record you just modified. Another problem is that when the table does not have a qualifying record that causes the UPDATE to fail, last_insert_id () retains its original value unchanged, and therefore cannot distinguish between the elements in the queue.
Row_count () returns the number of rows affected by the previous statement, as a condition of SELECT to help resolve the problem.
Finally, the complete solution to support concurrent access is:
Copy Code code as follows:
UPDATE targets SET status= ' D ', id=last_insert_id (ID) WHERE status= ' C ' LIMIT 1;
SELECT * from targets WHERE Row_count () >0 and id=last_insert_id ();
Update: This method is problematic when implementing a queue with a priority, with an order by ... The condition's UPDATE statement is very slow, for example:
Copy Code code as follows:
UPDATE targets SET status= ' D ' WHERE status= ' C ' ORDER by schedule ASC LIMIT 1;
Individual queries and updates are quick:
Copy Code code as follows:
SELECT ID from targets WHERE status= ' C ' ORDER by schedule ASC LIMIT 1;
UPDATE targets SET status= ' D ' WHERE id= ' id ';
Originally this is the MySQL Bug-12915, more than a year ago, although closed, but only solve some of the problems, still do not support where, see the MySQL 5.0.15 changlog. Helpless, the above ingenious method also has no practical value.
Finally, a compromise was adopted, as follows:
Copy Code code as follows:
UPDATE targets, (SELECT ID from targets WHERE status= ' C "and Schedule<current_timestamp order by schedule ASC LIMIT 1) TMP SET status= ' D ' WHERE targets.id=last_insert_id (tmp.id);
SELECT * from targets WHERE Row_count () >0 and id=last_insert_id ();
