Disadvantages of MySQL Row Locking

Question

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

Locking is based on the storage engine - what are you using? — Adrian Cornish, Sep 26 '12 at 03:35
Unfortunately, your `status IS NULL ORDER BY` query guarantees a full table scan of `mytable.` Indexes aren't helpful for IS NULL searches. If you could refactor your code to eliminate NULL status values, search for some other value, and use an index for that, you'd save a lot of table-scan time. — O. Jones, Sep 26 '12 at 03:42
Will setting the default value of `status` to `0` and indexing it be better? — Nyxynyx, Sep 26 '12 at 03:43
Yes, that scheme will make your 'find the next mytable row that's eligible for processing' operation much faster. — O. Jones, Sep 26 '12 at 03:45

Arun Killu · Accepted Answer · 2012-09-27T08:58:45.867

If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that permits other transactions to read the examined rows but not to update or delete them. and about this query

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

since innodb

automatically acquire locks during the processing of SQL statements i think it works as the same .

Disadvantages of MySQL Row Locking

1 Answers1