mysqldeletein死锁_deleteupdatewhere…in语句导致死锁问题
分析
1、问题描述
测试中发现delete\update语句在并发测试时经常会导致发⽣死锁。
2、问题分析
在mariadb中，update或delete使⽤ where(…,…,…) in (…,…,..) 的写法会导致全表扫描，加⼤锁冲突的概率，造成死锁。
例1：
MySQL
MariaDB [lzk]> show create table b\G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`a` int(11) NOT NULL DEFAULT '0',
`b` int(11) NOT NULL DEFAULT '0',
`c` int(11) NOT NULL DEFAULT '0',
`d` int(11) NOT NULL DEFAULT '0',
`e` int(11) NOT NULL DEFAULT '0',
`f` int(11) DEFAULT NULL,
PRIMARY KEY (`a`,`b`,`c`,`d`,`e`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
MariaDB [lzk]> explain delete from b where a=1 and b=2 and c=1 and d=1 and e=1;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | lect_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | b | range | PRIMARY | PRIMARY | 20 | NULL | 1 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in t (0.00 c) --直接根据主键定位到记录
MariaDB [lzk]> explain delete from b where (a,b,c,d,e) in ((1,1,1,1,1));
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | lect_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+溥仪墓
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 18 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in t (0.00 c) --全表扫描
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
MariaDB[lzk]>showcreatetableb\G
***************************1.row*************************** Table:b
CreateTable:CREATETABLE`b`(
`a`int(11)NOT NULLDEFAULT'0',
`b`int(11)NOT NULLDEFAULT'0',
`c`int(11)NOT NULLDEFAULT'0',
`d`int(11)NOT NULLDEFAULT'0',
虾仁玉子豆腐`e`int(11)NOT NULLDEFAULT'0',
`f`int(11)DEFAULTNULL,
PRIMARY KEY(`a`,`b`,`c`,`d`,`e`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin
MariaDB[lzk]>explaindeletefrombwherea=1andb=2andc=1andd=1ande=1;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |id|lect_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |1|SIMPLE|b|range|PRIMARY|PRIMARY|20|NULL|1|Usingwhere|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1rowint(0.00c)--直接根据主键定位到记录
MariaDB[lzk]>explaindeletefrombwhere(a,b,c,d,e)in((1,1,1,1,1));
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|id|lect_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|1|SIMPLE|b|ALL|NULL|NULL|NULL|NULL|18|Usingwhere|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1rowint(0.00c)--全表扫描
场景1：
当会话1：start transaction;
delete from b where (a,b,c,d,e) in ((1,1,1,1,1));
会话2：start transaction;
delete from b where (a,b,c,d,e) in ((1,1,2,1,1));
此时会话2会产⽣锁等待。
⽽场景2：
当会话1：start transaction;
联通怎么查流量delete from b where where a=1 and b=1 and c=1 and d=1 and e=1;
会话2：start transaction;
delete from b where where a=1 and b=1 and c=2 and d=1 and e=1;
不会产⽣锁等待。
例2：
MySQL
MariaDB [test]> explain update b t c='qwe' where (a,b) in ((1,10));
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | lect_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | b | index | NULL | PRIMARY | 8 | NULL | 9278400 | Using where | +------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in t (0.00 c)--全索引扫描
MariaDB [test]> explain update b t c='qwe' where a=1 and b=10;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | lect_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | b | range | PRIMARY | PRIMARY | 8 | NULL | 1 | Using where |
时尚围巾
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in t (0.00 c)--直接根据主键定位到记录
1
2
3
4
5
6
7
费列罗热量
8
9
10
11
鼻子肥大
12
13
14
15
MariaDB[test]>explainupdatebtc='qwe'where(a,b)in((1,10));
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|id |lect_type|table|type |possible_keys|key |key_len|ref |rows |Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| 1|SIMPLE |b |index|NULL |PRIMARY|8 |NULL|9278400|Usingwhere|
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1rowint(0.00c)--全索引扫描
MariaDB[test]>explainupdatebtc='qwe'wherea=1andb=10;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|id |lect_type|table|type |possible_keys|key |key_len|ref |rows|Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1|SIMPLE |b |range|PRIMARY |PRIMARY|8 |NULL| 1|Usingwhere|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1rowint(0.00c)--直接根据主键定位到记录
3、解决办法
不使⽤delete\update where… in的写法，改为delete\update where … and …or
4、半⼀致性读仍会死锁问题
在read-committed隔离级别下，update会使⽤半⼀致性读，为什么还是有⼏率发⽣死锁？
根据场景复现后，show engine innodb status查看死锁信息，发现发⽣死锁的事务会占有很多锁，不符合innodb半⼀致读的特性：
MySQL
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-03-30 15:19:46 7f79e7fec700
*** (1) TRANSACTION:
TRANSACTION 132876, ACTIVE 9 c fetching rows
mysql tables in u 1, locked 1
LOCK WAIT 252 lock struct(s), heap size 96696, 226 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 0x7f79e8bb5700, query id 15 redhat64-26 10.47.160.26 root updating
update test.c t c = '94414' where (a,b) in ((1,'5167'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
劳动协议书范本RECORD LOCKS space id 6 page no 6 n bits 496 index `PRIMARY` of table `test`.`c` trx id 132876 lock_mode X locks rec but not gap waiting
Record lock, heap no 211 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
射手座图片
1: len 4; hex 80000358; asc X;;
2: len 6; hex 00000000c7fb; asc ;;
3: len 7; hex 06000001671e10; asc g ;;
