MySql = v5.6
Table engine = InnoDB
I have one mysql cli open. I run:
SELECT id FROM my_table WHERE id=1 FOR UPDATE;
SELECT id FROM my_table WHERE id=1;
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.)
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.