背景:查看status日志发现两条insert 出现了死锁
注意:
在innodb 日志中如果提示 lock mode S /lock mode X ,其实都是gap锁,如果是行记录锁 会提示but not gap ,请读者朋友们在自己分析死锁日志的时候注意。
2.
可以确定,这个x锁不是由于INSERT产生的,因为 INSERT可能产生的锁包括检查dup key时的s锁,隐式锁转换为显式锁(not gap,要在二级索引上产生lock_mode为X的LOCK_ORDINARY类型的锁(包括记录及记录前面的gap),据我所知一般是根据二级索引扫描进行记录更新导致的。
3. 根据
LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
有7个undo entires,而单纯的INSERT一条记录只有一个undo entry,因此可以推断除了INSERT,必然还有别的操作
基于以上,事务除了INSERT,可能还存在DELETE/UPDATE,并且这些操作是走的二级索引来查找更新记录。
参考:
http://blog.itpub.net/22664653/viewspace-2145068/ ----杨奇龙
http://www.sohu.com/a/169663059_610509 ---insert ..select 语句产生死锁
http://blog.itpub.net/7728585/viewspace-2146183/ ---insert ..select 语句产生死锁 --八怪
- ------------------------
- LATEST DETECTED DEADLOCK
- ------------------------
- 2017-10-24 12:53:56 7f8fbc165700
- *** (1) TRANSACTION:
- TRANSACTION 596252578, ACTIVE 0 sec inserting
- mysql tables in use 1, locked 1
- LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
- MySQL thread id 17066316, OS thread handle 0x7f8eef3cf700, query id 2080508355 192.168.1.13 userservice update
- insert into
- svc_order_service_item
- ( work_order_id,
- quantity
- )
- values ( 2373314,1
- )
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- 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
- 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 596252580, ACTIVE 0 sec inserting
- mysql tables in use 1, locked 1
- 15 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 8
- MySQL thread id 17061883, OS thread handle 0x7f8fbc165700, query id 2080508367 192.168.1.12 userservice update
- insert into
-
- svc_order_service_item
-
- ( work_order_id,
- quantity,
- )
- values ( 2373315,
- 1
-
- )
- *** (2) HOLDS THE LOCK(S): --持有一个读锁
- 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 596252580 lock_mode X
- 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:
- 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 596252580 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 (1)
- ------------
分析:
1.表上有聚集索引和二级索引,死锁发生在二级索引idx_svcorderserviceitem_workorderid_quantity上2.
- 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. 根据
LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
有7个undo entires,而单纯的INSERT一条记录只有一个undo entry,因此可以推断除了INSERT,必然还有别的操作
基于以上,事务除了INSERT,可能还存在DELETE/UPDATE,并且这些操作是走的二级索引来查找更新记录。
一个简单但不完全相同的重现步骤:
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:
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)
互相等待,导致发生死锁
从打印的死锁信息来看,基本和线上发生的死锁现象是一致的。
再举一个例子
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】 两段区间
+----+-----+
| 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】两段区间
+----+-----+
| 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)
+----+-----+
| 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)
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】 两段区间
+----+-----+
| 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】两段区间
+----+-----+
| 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
查看死锁日志:
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')
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)
------------
------------------------
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 语句产生死锁
http://blog.itpub.net/7728585/viewspace-2146183/ ---insert ..select 语句产生死锁 --八怪
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-2146310/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-2146310/