A queue is a common data structure. Its basic feature is to use it in terms of first-in-first-out and transaction processing. Sometimes it is a queue with a priority. When the queue has concurrent access, for example, multithreading, You need to lock the machine to ensure that the same element in the queue is not obtained multiple times.
A queue is a common data structure. Its basic feature is to use it in terms of first-in-first-out and transaction processing. Sometimes it is a queue with a priority. When the queue has concurrent access, for example, multithreading, You need to lock the machine to ensure that the same element in the queue is not obtained multiple times.
A MySQL table can be considered as a queue, with each row being an element. Each query gets the first row that meets a condition, and deletes it from the table or changes its status so that it will not be obtained in the next query. Without concurrent access, you can simply use SELECT to get a row and then use the UPDATE (or DELETE) statement to modify it.
The Code is as follows:
SELECT * FROM targets WHERE status = 'C' LIMIT 1;
UPDATE targets SET status = 'd 'where id = 'id ';
If concurrent access exists, other SELECT queries may exist between the SELECT and UPDATE statements, resulting in the same row being retrieved multiple times. To ensure normal operation in the case of concurrency, one idea is to use database locks to prevent it, just as in a multi-threaded environment. In short, it is necessary to query and modify it into an atomic operation without being disturbed by other accesses. MySQL 5 supports stored procedures and can be used for implementation.
A single UPDATE statement should be atomic. This feature can be used to ensure the normal operation of the queue during concurrent access. Each time an element is retrieved, UPDATE the first row that meets the condition first, and then obtain the row. Unfortunately, the UPDATE statement does not return a value. If you use the normal SELECT statement again, it is difficult to find the record you just modified.
Here is a tips: Add id = LAST_INSERT_ID (id) during UPDATE, and then SELECT LAST_INSERT_ID () to get the id of the modified record. Another problem is that when no matching record exists in the table and the UPDATE fails, LAST_INSERT_ID () will keep the original value unchanged, so it cannot distinguish whether there are any elements in the queue.
ROW_COUNT () returns the number of rows affected by the previous statement. Using ROW_COUNT () as a condition for SELECT can help solve this problem.
Finally, the complete solution to support concurrent access is:
The Code is 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 has a problem when implementing a queue with priority. The UPDATE statement with the order by... condition is very slow, for example:
The Code is as follows: UPDATE targets SET status = 'd 'where status = 'C' order by schedule asc limit 1;
Separate queries and updates are fast:
The Code is as follows:
SELECT id FROM targets WHERE status = 'C' order by schedule asc limit 1;
UPDATE targets SET status = 'd 'where id = 'id ';
It turned out that this was a MySQL Bug-12915. It was proposed more than a year ago. Although it was closed, it only solved some problems and does not support WHERE. For details, see the Changlog of MySQL 5.0.15. Helpless, the above clever method has no practical value.
Finally, a compromise scheme is adopted, as follows:
The Code is as follows:
UPDATE targets, (SELECT id FROM targets WHERE status = 'C' AND schedule SELECT * FROM targets WHERE ROW_COUNT ()> 0 and id = LAST_INSERT_ID ();
