As I know, innoDB use mechanism of consistent non blocking read,
so every transaction works with its own snapshot.
it is told also in official documentation
A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.
But I unexpectedly faced with behavior when classic 'read/update' deadlock appears:
Isolation level REPEATABLE READ (also is reproduced with READ COMMITTED)
Transaction 1 reads row (NOT lock in share mode).
Transaction 2 reads the same row ( ALSO NOT lock in share mode).
then
Transaction 1 tries to update this row.
Transaction 2 also tries to update this row.
After last step, innoDB detects deadlock (there is LATEST DETECTED DEADLOCK below):
----------------
2017-03-31 16:07:03 0x1f58
*** (1) TRANSACTION:
TRANSACTION 413412, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3
MySQL thread id 33, OS thread handle 8148, query id 102005 localhost 127.0.0.1 root updating
/* update Order */ update `Order` set ... <fields to update>
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2151 page no 709 n bits 88 index PRIMARY of table `ooapp2`.`order` trx id 413412 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 413413, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3
MySQL thread id 28, OS thread handle 8024, query id 102008 localhost 127.0.0.1 root updating
/* update Order */ update `Order` set ...<fields to update>
*** (2) **HOLDS THE LOCK(S):**
RECORD LOCKS space id 2151 page no 709 n bits 88 index PRIMARY of table `ooapp2`.`order` trx id 413413 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2151 page no 709 n bits 88 index PRIMARY of table `ooapp2`.`order` trx id 413413 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 54; compact format; info bits 0
*** WE ROLL BACK TRANSACTION (2)
I can't understand, what happens, why Transaction 2
HOLDS THE LOCK(S)
so if innoDB nevertheless not use Consistent Read with snapshot and sets S-locks
this does not correspond to the fact that is written in official manual.
Copyright Notice：Content Author:「Gleb Egunov」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/43142338/mysql-innodb-repeatable-read-unexpected-behavior-of-locks