MySQL 死锁常常被人提起,在大学期间的数据库课程也会有所提及,但真正接触的 case 是没有过的。前段时间在线上发现了死锁,导致 MySQL 负载升高,连接不能及时释放,造成业务小范围瘫痪。这里死锁的场景是并发的 deleteinsert...on duplicate update

发生死锁的表结构如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE `passengers_account_mapping` (
	`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
	`cert_no` varchar(25) NOT NULL DEFAULT '' COMMENT '证件号码',
	`account_name` varchar(40) NOT NULL DEFAULT '' COMMENT '用户账号名',
	`cert_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '证件类型',
	`passenger_name` varchar(45) NOT NULL DEFAULT '' COMMENT '乘客姓名',
	PRIMARY KEY (`id`),
	UNIQUE `uniq_account_passengers_name` (`cert_no`, `account_name`, `passenger_name`),
	KEY `idx_account_name` (`account_name`)
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARSET = utf8mb4;

事务隔离级别为:RR

死锁日志分析

首先通过 show engine innodb status; 查处死锁相关日志信息。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
2019-12-20 14:54:35 7fc9f8fe9700
*** (1) TRANSACTION: //事务1
TRANSACTION 83389013326, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1 //使用一个表
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1 //有 4 个锁结构,3个行锁
MySQL thread id 22442, OS thread handle 0x7fcdd8cd5700, query id 1334027037 10.88.104.204 t_account_rw updating
`delete from passengers_account_mapping where account_name='c196cf184'` //死锁发生时执行的 SQL 语句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: //等待锁的信息
RECORD LOCKS space id 2238 page no 28003 n bits 304 index `uniq_account_passengers_name` of table `account`.`passengers_account_mapping` trx id 83389013326 lock_mode X locks rec but not gap waiting //唯一索引上的排他锁(Record-Lock)
*** (2) TRANSACTION: //事务2
TRANSACTION 83389013325, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 46111, OS thread handle 0x7fc9f8fe9700, query id 1334027030 10.88.104.202 t_account_rw update

`INSERT INTO passengers_account_mapping (id, account_name, cert_no, cert_type, passenger_name) VALUES (0, 'c196cf184', '6344_2iMxhPferW498', 0, '杨琳') ON DUPLICATE KEY UPDATE updated_ts = '2019-12-20 14:54:35.029';`

*** (2) HOLDS THE LOCK(S): // 持有锁的信息
RECORD LOCKS space id 2238 page no 28003 n bits 304 index `uniq_account_passengers_name` of table `account`.`passengers_account_mapping` trx id 83389013325 lock_mode X //唯一索引上的排他锁(Next-Key)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: //等待锁的信息
RECORD LOCKS space id 2238 page no 160778 n bits 216 index `PRIMARY` of table `account`.`passengers_account_mapping` trx id 83389013325 lock_mode X locks rec but not gap waiting //主键上的排他锁(Record-Lock)
*** WE ROLL BACK TRANSACTION (2)//回滚事务2

死锁日志的具体分析在上面的注释已说明,这里补充一些基础知识:

  1. 事务隔离级别
    1. RC:针对当前读,RC隔离级别保证对读取到的记录加锁 (记录锁),存在幻读现象
    2. RR:针对当前读,RR隔离级别保证对读取到的记录加锁 (记录锁),同时保证对读取的范围加锁,新的满足查询条件的记录不能够插入 (间隙锁),不存在幻读现象。
  2. 锁的类型
    1. 按性质分
      1. 排他锁
      2. 共享锁
    2. 按作用级别分
    3. 行锁的具体分类:
      1. Record Lock:加到 索引记录 上的锁
      2. Gap Lock:对索引记录中的一段连续区域的锁
      3. Next-Key Lock:是记录锁和记录前的间隙锁的结合,解决了幻读问题

从日志中可以看到,事务1当前想要获取唯一索引上的锁;事务2持有唯一索引上的锁,想要主键索引上的锁。这里造成死锁,可以猜测是事务1已获取主键索引上的锁。根据 MySQL 的加锁规则:Locks Set by Different SQL Statements in InnoDB。官方文档的规则较多,这里我先解释两种语句的加锁规则:

  1. DELETE 会对所有符合条件的唯一索引或主键索引加 Record Lock、 非唯一索引加 Next-Key Lock

    DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

  2. INSERT ... ON DUPLICATE KEY UPDATE 当发生 duplicate-key 时,会对主键索引加 Record Lock,对唯一索引加 Next-Key Lock

    INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

我们可以得到以下加锁流程:

执行序列 事务1 事务2
0 Hold NK Lock In idx_account_name
1 Hold Record Lock In PRIMARY
2 Hold NK Lock In uniq_account_passengers_name
3 Want Record Lock In uniq_account_passengers_name
4 Want Record Lock In PRIMARY

在第3、4步发生死锁,发生死锁主要的原因是加锁顺序不一致,那如何进行顺序一致的加锁处理?这里就需要对 MySQL 索引的存储结构有些理解。

索引结构

idx_account_name 索引属于非聚集索引(二级索引),非聚集索引的存储结构如下:

二级索引结构

二级索引的B树结构中,指向的是聚集索引(主键索引)。所以针对 delete from passengers_account_mapping where account_name='c196cf184',首先通过 where 子句条件找到 account_name='c196cf184'idx_account_name 索引并加锁,再找到对应的聚集索引,最后再将其他索引加锁。

解决方案

上面说过死锁的问题在于加锁顺序不一致,所以解决方案就是再删除的业务逻辑中,使用 PRIMARY 或者 uniq_account_passengers_name 索引进行删除。

参考