Geek asks and answers

Disadvantages of row locking in MySQL

I use row (transaction) locking in MySQL to create a job queue. The engine used is InnoDB.

SQL Query

START TRANSACTION;
SELECT * 
FROM mytable 
WHERE status IS NULL 
ORDER BY timestamp DESC LIMIT 1 
FOR UPDATE;
UPDATE mytable SET status = 1;
COMMIT;

According to this web page ,

The problem with SELECT FOR UPDATE is that it usually creates a single synchronization point for all of the worker processes, and you see a lot of processes waiting for the locks to be released with COMMIT.

Question: Does this mean that when the first request is executed, which takes some time to complete the transaction earlier, when the second similar request occurs before the first transaction is completed, it will have to wait until the request is completed? If this is true, then I don’t understand why blocking rows of one row (which I assume) will affect the next transaction request, which does not require reading this locked row?

, ( - ), UPDATE ?

UPDATE mytable SET status = 1
WHERE status IS NULL
ORDER BY timestamp DESC
LIMIT 1
+5
1

FOR UPDATE , , , , . LOCK IN SHARE MODE , , update or delete .

UPDATE mytable SET status = 1
WHERE status IS NULL
ORDER BY timestamp DESC
LIMIT 1

innodb

SQL-, , .

+1

More articles:


All Articles