記一次MySQL innodb insert 死鎖問題
你知道的越多，不知道的就越多，業(yè)余的像一棵小草！
你來，我們一起精進！你不來，我和你的競爭對手一起精進！
編輯：業(yè)余草
blog.csdn.net/ignorewho
推薦：https://www.xttblog.com/?p=5306
前兩天在視頻號發(fā)了一個“價值百萬的百度網盤限速核心代碼”在不到 1 天內，瀏覽量達到了 10K，今天在文章開始之前，分享給公眾號朋友！
如果是間隙鎖、或者是行鎖的話，那么就可能會導致死鎖。但是最近公司的同事在進行單純的插入意向鎖的過程中，也導致死鎖。也就是說單純地插入操作也可能會導致死鎖，所以也模擬下這種場景，以后遇到類似問題也不至于慌亂。
同一條插入sql引發(fā)的死鎖
環(huán)境準備
1.創(chuàng)建表：
CREATE TABLE `test_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` char(10) DEFAULT NULL,
`status` int(10) DEFAULT NULL,
`unqiue_id` bigint(20) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `index_unique` (`unqiue_id`) USING BTREE,
KEY `index_user` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
2.插入數據：
INSERT INTO `test_user` VALUES ('1', 'a', '1', '1');
INSERT INTO `test_user` VALUES ('3', 'c', '2', '2');
INSERT INTO `test_user` VALUES ('5', 'e', '3', '3');
模擬死鎖
1.啟動事務 A。
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25);
Query OK, 1 row affected (0.00 sec)
2.啟動事務 B(插入阻塞)。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25);
3.啟動事務 C(插入阻塞)。
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25);
4.回滾事務 A。
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
結果
事務 A 正?；貪L，事務 B 正常執(zhí)行插入 sql，事務 C 發(fā)生死鎖。
分析：
1.在模擬死鎖步驟3時 查看innodb狀態(tài)信息：show engin innodb status\\G;
事務B在執(zhí)行插入操作
---TRANSACTION 118519640, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 675673, OS thread handle 0x7feca6334700, query id 228664842 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000007107711; asc w ;;
2: len 7; hex 94000002040110; asc ;;
3: len 9; hex e5a4a7e58584e5bc9f; asc ;;
4: len 4; hex 80000019; asc ;;
------------------
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519640 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000007107711; asc w ;;
2: len 7; hex 94000002040110; asc ;;
3: len 9; hex e5a4a7e58584e5bc9f; asc ;;
4: len 4; hex 80000019; asc ;;
事務C在執(zhí)行插入操作
---TRANSACTION 118519627, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 675672, OS thread handle 0x7fec297e1700, query id 228664797 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000007107711; asc w ;;
2: len 7; hex 94000002040110; asc ;;
3: len 9; hex e5a4a7e58584e5bc9f; asc ;;
4: len 4; hex 80000019; asc ;;
------------------
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519627 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000007107711; asc w ;;
2: len 7; hex 94000002040110; asc ;;
3: len 9; hex e5a4a7e58584e5bc9f; asc ;;
4: len 4; hex 80000019; asc ;;
事務A持有鎖
---TRANSACTION 118519569, ACTIVE 324 sec
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 675670, OS thread handle 0x7fecaee69700, query id 228748549 10.10.1.1 testdata init
show engine innodb status
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519569 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock mode S locks rec but not gap
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000710df31; asc 1;;
2: len 7; hex f9000002180110; asc ;;
3: len 9; hex e5a4a7e58584e5bc9f; asc ;;
4: len 4; hex 80000019; asc ;;
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519569 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000710df31; asc 1;;
2: len 7; hex f9000002180110; asc ;;
3: len 9; hex e5a4a7e58584e5bc9f; asc ;;
4: len 4; hex 80000019; asc ;;
由上可以看出：
1.事務A(id:118519569)持有:意向排他鎖(表級鎖)、共享記錄鎖、插入意向鎖(間隙鎖的一種)、排他記錄鎖
2.事務B(id:118519640)等待獲取共享記錄鎖，事務C(id:118519627)等待獲取共享記錄鎖
3.發(fā)生死鎖后，查看innodb狀態(tài)信息：show engin innodb status\G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-01-11 11:51:38 7feca6334700
*** (1) TRANSACTION:
TRANSACTION 118519627, ACTIVE 725 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 675672, OS thread handle 0x7fec297e1700, query id 228672111 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
事務C等待加：插入意向鎖和排他記錄鎖
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 118519640, ACTIVE 719 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 675673, OS thread handle 0x7feca6334700, query id 228672122 10.10.1.1 testdata update
insert into test_insert_deadlock(id,name,age) values(1,'大兄弟',25)
事務B持有：共享鎖
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
事務B等待加：插入意向鎖和排他記錄鎖
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519640 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
事務B鎖狀態(tài)信息：
---TRANSACTION 118519627, ACTIVE 731 sec
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 675672, OS thread handle 0x7fec297e1700, query id 228672111 10.10.1.1 testdata cleaning up
TABLE LOCK table `test`.`test_insert_deadlock` trx id 118519627 lock mode IX
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks rec but not gap
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock_mode X insert intention
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 6644 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_insert_deadlock` trx id 118519627 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
由上可以看出：
事務 B(id:118519627)此時持有鎖：意向排他鎖、共享記錄鎖、插入意向排他鎖、共享間隙鎖
事務 A 回滾后，事務B和事務C競爭鎖資源，首先事務B獲取了共享記錄鎖，事務C也申請共享記錄鎖，因為共享鎖之間是兼容的，所以申請成功，然后事務B、事務C再申請插入意向排他鎖。
因為排他鎖和共享鎖之間是沖突的，所以事務 B 和事務 C 互相等待對方釋放共享鎖，這樣就出現死鎖了。
個人總結：
根據分析過程中，查看 innodb 鎖狀態(tài)信息，可以推出 insert 語句的加鎖順序是：意向排他鎖(表級鎖)、共享記錄鎖、插入意向鎖(間隙鎖的一種)、排他記錄鎖 上面的死鎖例子屬于插入意向鎖死鎖
評論
圖片
表情