MySQL Forums
Forum List  »  InnoDB

Strange supremum gap-lock behaviour leading to deadlocks
Posted by: Elia Martell
Date: August 05, 2016 02:43PM


I recently discovered a deadlock problem in my application and went to investigate it. I managed to discover the cause and fix the code, but still this behaviour seems quite strange to me. So I ask if anyone else finds this strange and what are possible solutions to this.

Default REPEATABLE READ transaction isolation level is in effect.
A quick example:
> create table zu (a int primary key);

t1 > begin;
t1 > update zu set a = 0 where a = 5;

t2 > begin;
t2 > update zu set a = 0 where a = 7;

t1 > insert into zu values (5);

t2 > insert into zu values (6);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

What I think is happening:
1) t1 update scanned an empty index and set a next-key gap-lock on supremum record
2) t2 update did exactly the same
3) t1 insert tried to acquire an exclusive insert intention lock on the new value would-be position in the index
4) the lock was unavailable because of the gap-lock held by t2
5) t1 blocked on t2
6) t2 insert in the same way deadlocked on t1
Am I right in this interpretation?

Same example with READ COMMITED is going just fine. But this REPEATABLE READ strategy doesn't make any sense in this example, there're no selects.

I get the same deadlock with SELECT a FROM zu WHERE a = 5 FOR UPDATE, and again if values are different in transactions then reads would not get any phantom rows.

Options: ReplyQuote

Written By
Strange supremum gap-lock behaviour leading to deadlocks
August 05, 2016 02:43PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.