MySql InnoDB Repeatable Read Unexpected behavior of locks

Question

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.

Rick James Rick James · Accepted Answer · 2017-04-01T20:00:52

Don't do that. If you might be updating a row, but meanwhile need the value, use SELECT ... FOR UPDATE;. Just do that, and forget about tx_isolation. Normally, this will turn a Deadlock into a delay. (See innodb_lock_wait_timeout, which defaults to an over-generous 50 seconds.)

Also, when you do get a Deadlock, re-run the entire transaction. Deadlocks will happen no matter how hard you try to avoid them.

MySql InnoDB Repeatable Read Unexpected behavior of locks

1 Answers