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
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.