Disadvantages of MySQL Row Locking
- by Nyxynyx
I am using row locking (transactions) in MySQL for creating a job queue. 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 webpage,
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 query is executed, which takes some time to finish the transaction before, when the second similar query occurs before the first transaction is committed, it will have to wait for it to finish before the query is executed? If this is true, then I do not understand why the row locking of a single row (which I assume) will affect the next transaction query that would not require reading that locked row?
Additionally, can this problem be solved (and still achieve the effect row locking does for a job queue) by doing a UPDATE instead of the transaction?
UPDATE mytable SET status = 1
WHERE status IS NULL
ORDER BY timestamp DESC
LIMIT 1
