MySQL死鎖案例分析一(先delete，再insert，導致死鎖)

一、死鎖案例

MySQL版本：Percona MySQL Server 5.7.19
隔離級別：可重複讀（RR）
業務邏輯：併發下按某個索引欄位先delete記錄，再insert記錄

比如：

begin;delete from tb where order_id = xxx;insert into tb(order_id) values(xxx);
commit;

二、MySQL鎖基本概念

S：共享鎖（行級鎖）
X：排他鎖（行級鎖）
IS：意向共享鎖（表級鎖）
IX：意向排他鎖（表級鎖）

以上4種鎖的相容性見下表：

鎖模式相容性表

• gap鎖與gap鎖之間不衝突

• rec insert intention(插入意向鎖）與gap鎖衝突。

三、模擬復現死鎖

開啟引數，從innodb status獲取更多的鎖資訊。
set GLOBAL innodb_status_output_locks=ON;

表結構：

 CREATE TABLE `tb` (  `order_id` int(11) DEFAULT NULL,
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中資料：

mysql> select * from tb;
+----------+| order_id |+----------+|       10 ||       20 |+----------+2 rows in set (0.00 sec)

事務執行步驟：

session1	session2
begin
	begin
delete from tb where order_id=15;
	delete from tb where order_id=15;
	insert into tb select 15;(等待鎖)
insert into tb select 15;(死鎖)

1. 當session1執行delete from tb where order_id=15;，由於條件order_id=15的記錄不存在，session1 獲得2個鎖結構，分別是意向排他鎖IX（表級鎖）、gap鎖（行級鎖），如下：

---TRANSACTION 1055191443, ACTIVE 20 sec2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 315642, OS thread handle 139960342456064, query id 150462030 localhost root
TABLE LOCK table `db`.`tb` trx id 1055191443 lock mode IX
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec

1. 當session2執行delete from tb where order_id=15;，同樣由於order_id=15的記錄不存在，session2 也獲得2個鎖結構，分別是意向排他鎖IX（表級鎖）、gap鎖（行級鎖），如下：

---TRANSACTION 1055191444, ACTIVE 3 sec2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 315336, OS thread handle 139960562685696, query id 150462412 localhost root
TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec

1. 當session2執行insert into tb select 15;， session2 已經獲取到IX鎖，gap鎖，等待 rec insert intention（插入意向鎖）

---TRANSACTION 1055191444, ACTIVE 68 sec inserting
mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing
insert into tb select 15------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting
------------------
TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting

1. 當session1執行insert into tb select 15;，session1 已獲取到IX鎖，gap鎖， 等待rec insert intention（插入意向鎖）， session1, session2 都在等待插入意向鎖， 插入意向鎖與gap鎖衝突，雙方都沒有釋放gap鎖，又都在等待插入意向鎖，死鎖發生。

LATEST DETECTED DEADLOCK
------------------------2018-11-03 17:15:11 0x7f4b0e7ea700*** (1) TRANSACTION:
TRANSACTION 1055191444, ACTIVE 135 sec inserting
mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing
insert into tb select 15*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 1055191443, ACTIVE 201 sec inserting, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315642, OS thread handle 139960342456064, query id 150463172 localhost root executing
insert into tb select 15*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

四、案例擴充套件

以上死鎖案例，業務程式碼邏輯是多執行緒併發下，有可能多個執行緒會執行相同order_id的job，比如兩個執行緒執行的order_id 都是15。
另外一種情況，多個執行緒間，不會執行到相同order_id的情況，也可能發生死鎖。比如一個執行緒order_id=15，另外一個執行緒order_id=16，如下所示：

事務執行步驟：

session1	session2
begin
	begin
delete from tb where order_id=15;
	delete from tb where order_id=16;
	insert into tb select 16;(等待鎖)
insert into tb select 15;(死鎖)

鎖情況與上述相同，不再贅述，死鎖資訊如下：

LATEST DETECTED DEADLOCK
------------------------2018-11-03 17:28:30 0x7f4b0e667700*** (1) TRANSACTION:
TRANSACTION 1055191450, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 316221, OS thread handle 139960338228992, query id 150467652 localhost root executing
insert into tb select 16*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191450 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 1055191449, ACTIVE 28 sec inserting, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 316222, OS thread handle 139960340870912, query id 150467681 localhost root executing
insert into tb select 15*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

作者：偏執的工匠
連結：