Mysql queue for efficient Concurrent Data Reading

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 a queue has concurrent access, for example, multiple threads, a locking mechanism is required 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.

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:

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:

UPDATE targets SET status='D' WHERE status='C' ORDER BY schedule ASC LIMIT 1;

Separate queries and updates are fast:

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.

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(); 

Articles you may be interested in

• How does mysql export data of a field in a table?
• How to query data of mysql today, yesterday, last 7 days, last 30 days, last month, and last month
• Mysql Server master-slave Database Synchronization Configuration
• Improves the query speed of millions of data records for MySQL
• MySQL uses limit 1 to improve query efficiency
• MySQL index Operation Command (create index, re-index, query index, delete index) Summary
• MySQL Data Table for Chinese provinces and municipalities
• MySQL string truncation Function