建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
【Mysql】兩條insert語句產(chǎn)生的死鎖
背景：查看status日志發(fā)現(xiàn)兩條insert 出現(xiàn)了死鎖
可以確定，這個x鎖不是由于INSERT產(chǎn)生的，因為 INSERT可能產(chǎn)生的鎖包括檢查dup key時的s鎖，隱式鎖轉(zhuǎn)換為顯式鎖（not gap，要在二級索引上產(chǎn)生lock_mode為X的LOCK_ORDINARY類型的鎖(包括記錄及記錄前面的gap)，據(jù)我所知一般是根據(jù)二級索引掃描進(jìn)行記錄更新導(dǎo)致的。
3.根據(jù)
LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
有7個undo entires，而單純的INSERT一條記錄只有一個undo entry，因此可以推斷除了INSERT，必然還有別的操作
基于以上，事務(wù)除了INSERT，可能還存在DELETE/UPDATE，并且這些操作是走的二級索引來查找更新記錄。
- RECORD LOCKS space id 388 page no 27032 n bits 616 index `idx_svcorderserviceitem_workorderid_quantity` of table `ecejservice`.`svc_order_service_item` trx id 596252578 lock_mode X insert intention waiting
3.根據(jù)
LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
有7個undo entires，而單純的INSERT一條記錄只有一個undo entry，因此可以推斷除了INSERT，必然還有別的操作
基于以上，事務(wù)除了INSERT，可能還存在DELETE/UPDATE，并且這些操作是走的二級索引來查找更新記錄。
一個簡單但不完全相同的重現(xiàn)步驟：
DROP TABLE t1;
CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB ;
insert into t1(a, b,c) values(1,2,3),(5,4,6),(8, 7,9),(12,12,19),(15,15,11);
session1:
成都創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站建設(shè)、成都做網(wǎng)站、臥龍網(wǎng)絡(luò)推廣、成都小程序開發(fā)、臥龍網(wǎng)絡(luò)營銷、臥龍企業(yè)策劃、臥龍品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供臥龍建站搭建服務(wù)，24小時服務(wù)熱線：18982081108，官方網(wǎng)址：www.cdcxhl.com
begin;
delete from t1 where b = 12;
//二級索引上lock_mode X、lock_mode X locks gap before rec以及主鍵上的lock_mode X locks rec but not gap
二級索引：heap_no=5, type_mode=3 （12上的LOCK_ORDINARY類型鎖，包括記錄和記錄前的GAP）
聚集索引：heap_no=5，type_mode=1027
二級索引：heap_no=6，type_mode=547(15上的GAP鎖)
session2:
begin;
delete from t1 where b = 7;
//二級索引上lock_mode X、lock_mode X locks gap before rec以及主鍵上的lock_mode X locks rec but not gap
二級索引：heap_no=4，type_mode=3 （7上的LOCK_ORDINARY類型鎖，包括記錄和記錄前的GAP）
聚集索引：heap_no=4，type_mode=1027
二級索引：heap_no=5，type_mode=547 (記錄12上的GAP鎖)
session1:
insert into t1 values (NULL, 6,10);
//新插入記錄聚集索引無沖突插入成功，二級索引等待插入意向鎖（lock_mode X locks gap before rec insert intention waiting）
二級索引,heap_no=4, type_mode=2819 （請求記錄7上面的插入意向鎖LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION, 需要等待session2
session2:
insert into t1 values (NULL, 7,10);
二級索引:heap_no=5, type_mode=2819 (請求記錄12上的插入意向鎖LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION，需要等待session1)
互相等待，導(dǎo)致發(fā)生死鎖
從打印的死鎖信息來看，基本和線上發(fā)生的死鎖現(xiàn)象是一致的。
再舉一個例子
session1：
mysql> select now();start TRANSACTION;
+---------------------+
| now() |
+---------------------+
| 2018-01-25 16:08:46 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test01 where app='05' for update; ---第1步 鎖住【6.04】-【7.05】以及【7.05】-【8.06】 兩段區(qū)間
+----+-----+
| id | app |
+----+-----+
| 7 | 05 |
+----+-----+
1 row in set (0.00 sec)
mysql> insert into test01(app) values ('07'); --第三步 等待第二步釋放
Query OK, 1 row affected (23.24 sec)
session2：
mysql> select * from test01 where app='08' for update; --第二步 鎖住【12,07】-【13，08】以及【13,08】-【14,09】兩段區(qū)間
+----+-----+
| id | app |
+----+-----+
| 13 | 08 |
+----+-----+
1 row in set (0.00 sec)
mysql> insert into test01(app) values ('04'); ----第四步 等待第一步釋放，，于是死鎖
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查看死鎖日志:
死鎖日志是不是和上面的一樣？
再舉一個例子
-
MySQL> select * from test01;
-
+----+-----+
-
| id | app |
-
+----+-----+
-
| 1 | 01 |
-
| 2 | 02 |
-
| 5 | 03 |
-
| 10 | 03 |
-
| 6 | 04 |
-
| 7 | 05 |
-
| 8 | 06 |
-
| 9 | 06 |
-
| 11 | 06 |
-
| 12 | 07 |
-
| 13 | 08 |
-
| 14 | 09 |
-
| 15 | 09 |
-
+----+-----+
- 13 rows in set (0.00 sec)
查看死鎖日志:
-
LATEST DETECTED DEADLOCK
-
------------------------
-
2018-01-25 16:09:54 0x7f07d23ff700
-
*** (1) TRANSACTION:
-
TRANSACTION 5375, ACTIVE 51 sec inserting
-
mysql tables in use 1, locked 1
-
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
-
MySQL thread id 2294, OS thread handle 139671567841024, query id 42463 localhost root update
-
insert into test01(app) values ('07')
-
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5375 lock_mode X locks gap before rec insert intention waiting
-
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-
0: len 2; hex 3038; asc 08;;
-
1: len 4; hex 0000000d; asc ;;
-
-
*** (2) TRANSACTION:
-
TRANSACTION 5376, ACTIVE 38 sec inserting
-
mysql tables in use 1, locked 1
-
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
-
MySQL thread id 2293, OS thread handle 139671568905984, query id 42464 localhost root update
-
insert into test01(app) values ('04')
-
*** (2) HOLDS THE LOCK(S):
-
RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 lock_mode X
-
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-
0: len 2; hex 3038; asc 08;;
-
1: len 4; hex 0000000d; asc ;;
-
-
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 lock_mode X locks gap before rec insert intention waiting
-
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-
0: len 2; hex 3035; asc 05;;
-
1: len 4; hex 00000007; asc ;;
-
-
*** WE ROLL BACK TRANSACTION (2)
- ------------
死鎖日志是不是和上面的一樣？
參考：
http://blog.itpub.net/22664653/viewspace-2145068/ ----楊奇龍
http://www.sohu.com/a/169663059_610509 ---insert ..select 語句產(chǎn)生死鎖
http://blog.itpub.net/7728585/viewspace-2146183/ ---insert ..select 語句產(chǎn)生死鎖--八怪
文章名稱：【Mysql】兩條insert語句產(chǎn)生的死鎖
文章源于：http://biofuelwatch.net/article/gpdcdc.html
