Dead lock by inserting after deleting by primary key

About a dead lock by inserting after deleting by primary key on REPEATABLE READ.

Transcript

1. Dead lock by inserting after deleting by primary key Kentoku SHIBA
2. - Transaction isolation level is REPEATABLE READ. - Delete same record using primary key from 2 different connections, 1 connection successes deleting, another connection waits that the lock is released. - The waiting connection gets a DEAD LOCK error when the connection which succeed deleting inserts a row that is same primary key. Detail of the issue
3. connection 1 connection 2 start transaction Connection 2 becomes lock waiting when this connection tries getting the record lock of pk=1. Connection 2 gets a DEAD LOCK error because connection 1 should wait for a GAP lock caused by connection 2 when connection 1 tests an INSERT INTENTION GAP lock. Detail of the issue start transaction delete from t1 where pk = 1; delete from t1 where pk = 1; LOCK waiting insert into t1 (pk) values (1); DEAD LOCK
4. - INSERT INTENTION GAP lock If there are some GAP locks (except INSERT INTENTION GAP locks) or some record locks (except NOT GAP record locks) on the next key of a inserting record, this inserting waits for releasing locks. After passing this check, the INSERT INTENTION GAP lock is released immediately. - Implicit record lock It becomes a NOT GAP record lock when this record is checked by another locks. When inserting, check the lock conflict using INSERT INTENTION GAP lock, and after inserting, check the lock conflict using implicit record lock. Locks (REPEATABLE READ) of INSERT (excerpt)
5. Based on the behavior of locks at the time of inserting, checking INSERT INTENTION GAP lock can be omitted if it falls under any of the following cases. - The exclusive record lock has already been acquired by same connection for the inserting record. (After updating, deleting, etc) Thinking about omitting INSERT INTENTION GAP lock
6. - Consider whether READ COMMITTED is available for your case, and perform deleting and inserting on READ COMMITTED. - Performs additional exclusive control so that processing for the same record is not performed at the same time. - Examines whether retry processing is possible at getting DEAD LOCK error and performs retry processing. Current solutions