MySQL · 源码分析 · MySQL deadlock cause by lock inherit

Author: baotiao

In our user environment, we find deadlock cause by this example.

However, in this case, sometimes session 2 and session 3 lead to dead lock and sometimes it won’t.

  1. create table t(a int AUTO_INCREMENT, b int, PRIMARY KEY (a));
  2. insert into t(a, b) values(10, 8);
  3. insert into t(a, b) values(5, 8); 
  5. session 1: begin; delete from t where a=5;
  6. session 2: insert into t(a, b) values (5, 8) on duplicate key update b = 11;
  7. session 3: insert into t(a, b) values (5, 8) on duplicate key update b = 11;
  9. session 1: commit;
  11. # Then sometimes session 2 and session 3 lead to dead lock and sometimes it won't.

I find the root cause is lock inherit cause the deadlock.

In session 1 get the record 5 X, REC_NOT_GAP lock.

Then session 2 waiting in the record 5 X, REC_NOT_GAP lock.

And session 3 waiting in the record 5 X, REC_NOT_GAP lock.

  1. +-----------+------------+-----------+---------------+-------------+-----------+
  2. | thread_id | index_name | lock_type | lock_mode     | LOCK_STATUS | lock_data |
  3. +-----------+------------+-----------+---------------+-------------+-----------+
  4. |       148 | PRIMARY    | RECORD    | X,REC_NOT_GAP | WAITING     | 5         |
  5. |       150 | PRIMARY    | RECORD    | X,REC_NOT_GAP | WAITING     | 5         |
  6. |       146 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
  7. +-----------+------------+-----------+---------------+-------------+-----------+

Then when session 1 execute commit, there is two scenario:

Whether the record 5 was purged after session 1 commit and before session 2 and session 3 was executing.

If the record was not purged, the record 5 won’t be physical deleted, then only one session, session2 or session 3 will get the 5 X, REC_NOT_GAP lock, then the session 2 and session 3 doing the insert one by one.

This scenario will not cause deadlock.

If the record was purged, the record 5 was physical deleted, then the waiting records will inherit to next record. Then session 2 and session 3 will wait for record 10, X GAP lock. Then session 2 and session 3 will both get the record 10, X GAP lock. Then they will doing the insert work, both sessions hold the X GAP lock and waiting other’s X,GAP,INSERT_INTENTION lock. Then deadlock happend.

Get the lock information by adding a breakpoint before deadlock check.

  1. +-----------+------------+-----------+------------------------+--------------+-----------+
  2. | thread_id | index_name | lock_type | lock_mode              | LOCK_STATUS  | lock_data |
  3. +-----------+------------+-----------+------------------------+--------------+-----------+
  4. |       148 | PRIMARY    | RECORD    | X,GAP                  | GRANTED      | 10        |
  5. |       148 | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING      | 10        |
  6. |       150 | PRIMARY    | RECORD    | X,GAP                  | GRANTED      | 10        |
  7. |       150 | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING      | 10        |
  8. +-----------+------------+-----------+------------------------+--------------+-----------+

The deadlock information from mysql

image-20240321061350101

The default behaviour of lock inherit is Let the next record’s GAP lock inherit the record’s REC_NOT_GAP lock.

However, in this case, the X REC_NOT_GAP lock is conflict with X REC_NOT_GAP lock, after the inherit, X REC_NOT_GAP lock inherit to next record’s X GAP lock. The X GAP lock won’t conflict with X GAP lock. Then two sessions both get the X GAP lock, then the deadlock happened.

I suggest in X REC_NOT_GAP lock inherit case, let the next record inherit the NEXT-KEY lock, then in this case NEXT_KEY lock conflict with NEXT_KEY lock, the deadlock won’t happened.

原文：http://mysql.taobao.org/monthly/2024/03/02/