FOR UPDATE doesn't seem to lock the row in MySql InnoDB

Question

MySql = v5.6

Table engine = InnoDB

I have one mysql cli open. I run:

START TRANSACTION;

SELECT id FROM my_table WHERE id=1 FOR UPDATE;

I then have a second cli open and run:

SELECT id FROM my_table WHERE id=1;

I expected it to wait until I either committed or rolled back the first transaction but it doesn't, it just brings back the row straight away as if no row-locking had occurred.

I did another test where I updated a status field in the first cli and I couldn't see that change in the 2nd cli until I committed the transaction, proving the transactions are actually working.

Am I misunderstanding FOR UPDATE or doing something wrong?

update:

Needed FOR UPDATE on the 2nd SELECT query

It is possible to read when table are locked and select is read function — Rafiqul Islam, Sep 23 '16 at 13:28
you can see this document http://www.mysqltutorial.org/mysql-table-locking/ — Rafiqul Islam, Sep 26 '16 at 04:13
@Rafiq - _table_ locking does not seem to be relevant to this question. _Row_ locking is relevant. — Rick James, Sep 26 '16 at 18:45

Rick James · Accepted Answer · 2016-09-27T15:42:34.813

That action you saw is valid. With "MVCC", different connections can see different versions on the row(s).

The first connection grabbed a type of lock that prevents writes, but not reads. If the second connection had done FOR UPDATE or INSERT or other "write" type of operation, it would have been either delayed waiting for the lock to be released, or deadlocked. (A deadlock would require other locks going on also.)

Common Pattern

BEGIN;
SELECT ... FOR UPDATE; -- the row(s) you will update in this transaction
miscellany work
UPDATE...;  -- those row(s).
COMMIT;

If two threads are running that code at the "same" time on the same row(s), the second one will stalled at the SELECT..FOR UPDATE. After the first thread finished, the SELECT will run, getting the new values. All is well.

Meanwhile, other threads can SELECT (without for update) and get some value. Think of these threads as getting the value before or after the transaction, depending on the exact timing of all the threads. The important thing is that these 'other' threads will see a consistent view of the data -- either none of the updates in that transaction have been applied, or all have been applied. This is what "Atomic" means.

Thanks Rick. Is there such a thing as a READ lock? Basically the table holds queued items and I have multiple threads processing them - I don't want one item to be processed twice. Is there a MySql pattern for this? — Force Hero, Sep 27 '16 at 10:41
`FOR UPDATE` allows reading, but prevents writing. I'll add to my answer in a minute. — Rick James, Sep 27 '16 at 15:35
Thank you! The key bit I was missing is the 2nd `SELECT` query must also use `FOR UPDATE`. This works as expected now. — Force Hero, Sep 27 '16 at 15:56
But they do need to be enclosed in transaction? or there is another way? — Miguel, May 10 '21 at 09:25

FOR UPDATE doesn't seem to lock the row in MySql InnoDB

1 Answers1