MySQL高级DBA之第六讲-死锁案例分析

最近项目中某个模块稳定复现MySQL死锁问题,本文记录死锁的发生原因以及解决办法。
测试环境:
[root@node232 ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
[root@node232 ~]# uname -a
Linux node232 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
[root@node232 ~]# mysql -e "\s"
--------------
mysql  Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using  EditLine wrapper
Connection id:                8
Current database:       
Current user:                root@localhost
SSL:                        Not in use
Current pager:                stdout
Using outfile:                ''
Using delimiter:        ;
Server version:                5.7.18-log MySQL Community Server (GPL)
Protocol version:        10
Connection:                Localhost via UNIX socket
Server characterset:        utf8mb4
Db    characterset:        utf8mb4
Client characterset:        utf8mb4
Conn.  characterset:        utf8mb4
UNIX socket:                /tmp/mysql.sock
Uptime:                        3 hours 13 min 15 sec
Threads: 2  Questions: 68  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 104  Queries per second avg: 0.005
--------------

1. 预备知识
1.1 表锁和行锁
表锁
表锁是MySQL中最基本的锁策略,并且是开销最小的策略。表锁会锁定整张数据表,用户的写操作(插入/删除/更新)前,都需要获取写锁(写锁会相互阻塞);没有写锁时,读取用户才能获取读锁(读锁不会相互阻塞)。

行锁(仅限定于InnoDB)
行级锁可以最大程度的支持并发处理(同时也带来了最大的锁开销)。行级锁只在存储引擎实现,而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的具体实现。

1.2 行锁简介
行锁的模式有:读/写意向锁(IS/IX锁),读锁(S锁),写锁(X锁)以及自增锁(AI)。
行锁根据场景的不同又可以进一步细分,依次为Next-Key Lock,Gap Lock间隙锁,Record Lock记录锁和插入意向GAP锁。不同的锁锁定的位置是不同的,比如说记录锁只锁住对应的记录,而间隙锁锁住记录和记录之间的间隔,Next-Key Lock锁住记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。

 

此外,锁对应的死锁日志信息标记如下所示:
记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
间隙锁(LOCK_GAP): lock_mode X locks gap before rec
Next-key锁(LOCK_ORNIDARY): lock_mode X
插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention

1.3 行锁加锁示例
InnoDB是聚簇索引,也就是B+树的叶子节点存储了主键索引以及数据行;InnoDB的二级索引的叶子节点存储的则是主键值,所以通过二级索引查询数据时,需要根据查询到的主键去聚簇索引中再次进行查询。
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 


update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';
(1)第一条SQL使用主键进行查询,则只需要在`id=49`主键上加上写锁(X锁);  
(2)第二条SQL使用二级索引查询,首先在`name='Tom'`上加写锁,然后根据获取的主键索引查询,在`id=49`主键上添加写锁。
具体如下图所示:


以上是基于单条数据讨论,针对多条数据:
update user set age = 10 where id > 49;
执行步骤:  
(1)MySQL Server根据where条件读取满足条件的第一条记录,InnoDB引擎返回行记录并加锁;  
(2)MySQL Server发起更新行记录的update请求,更新此记录;  
(3)反复循环(1)(2)步骤,直到所有满足条件的记录均被修改。  
具体如下图所示:
 
2.准备工作
2.1 创建数据表并初始化
create table dead_lock_test
(
    id int auto_increment
       primary key,
    v1 int not null,
    v2 int not null
);
插入三条数据:
insert into dead_lock_test (v1,v2) value (1,1);
insert into dead_lock_test (v1,v2) value (2,2);
insert into dead_lock_test (v1,v2) value (3,3);

需要注意,数据表中仅存在主键索引。此外,默认数据库引擎为InnoDB,事务隔离级别为RR(可重复读,相对于RC解决了幻读)。

2.2 开启锁监控
使用如下语句,开启MySQL锁监控:
root@localhost[test]>show variables like '%innodb_status_output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | OFF   |
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
# 开启
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
# 关闭
set GLOBAL innodb_status_output_locks=OFF;


3.场景复现
开启两个数据库客户端连接,分别执行如下SQL语句:
# session1
start transaction ;
insert into dead_lock_test (v1,v2) value (4,4);
delete from dead_lock_test where v1 = 4 and v2 = 4;
commit;


# session2
start transaction;
insert into dead_lock_test (v1,v2) value (5,5);
delete from dead_lock_test where v1 = 5 and v2 = 5;
commit;


> 不要问事务里就两条SQL,插入后删除走回滚就可以了之类的问题(我也不知道为什么这么写的)。
事务执行步骤如下表所示:       


------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-07-24 15:03:19 0x7f30dda1c700
*** (1) TRANSACTION:
TRANSACTION 2129934, ACTIVE 17 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 139847853790976, query id 27 localhost root updating
delete from dead_lock_test where v1 = 4 and v2 = 4
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 3 n bits 72 index PRIMARY of table `test`.`dead_lock_test` trx id 2129934 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000208013; asc       ;;
 2: len 7; hex af000001980110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 2129939, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 139847853524736, query id 28 localhost root updating
delete from dead_lock_test where v1 = 5 and v2 = 5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 267 page no 3 n bits 72 index PRIMARY of table `test`.`dead_lock_test` trx id 2129939 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000208013; asc       ;;
 2: len 7; hex af000001980110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 3 n bits 72 index PRIMARY of table `test`.`dead_lock_test` trx id 2129939 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000208007; asc       ;;
 2: len 7; hex a7000001a70110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;

(1)由以上注释可知,事务2129934在尝试删除时,会对表中所有记录添加记录锁。这是因为当前删除记录条件为v1 = 4 and v2 = 4,在v1与v2字段上,并未建立相应的索引。因为无法通过索引确定主键,导致MySQL Server会先尝试锁定当前dead_lock_test表中所有记录添加记录锁(可以设置参数进行优化,根据where条件逐渐解除不满足条件记录上的记录锁)。

(2)事务2129934尝试对dead_lock_test表中所有记录添加锁,发现记录(id=5)已经被事务91327添加记录锁,导致事务2129934只能等待事务2129939放弃记录锁。
 
由上可知: 
(1)事务2129934执行删除操作时,尝试获取表中所有记录的记录锁,其中记录(id=5)的锁被事务2129939持有;
(2)事务2129939执行删除操作时,尝试获取表中所有记录的记录锁,发现记录(id=1)的锁被事务2129934持有;
(3)至此,事务2129934与事务2129939构成互相等待,死锁形成。

4 解决方案
4.1 添加索引
由上述的分析可知,删除时因为where条件无法利用索引,导致MySQL会尝试对表中所有记录加记录锁,产生死锁。
我们仅需在v1以及v2字段上建立联合索引,缩小记录冲突范围。
create index dead_lock_test_v2_v1_index on dead_lock_test (v1, v2);
| dead_lock_test | CREATE TABLE `dead_lock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v1` int(11) NOT NULL,
  `v2` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dead_lock_test_v2_v1_index` (`v1`,`v2`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 
> 此处没有设置唯一索引,如果多个事务根据索引查询,锁定的记录存在重叠,也容易复现死锁现象。  
> 不过当前业务侧的数据插入,可保证在短暂时间范围内,不存在重叠记录,且表中存在一些重复数据,因此不使用唯一索引。
4.2 最终
表中添加索引。
再执行上面的SQL,发现不会再发生锁等待以及死锁的情况
参考文章:https://www.modb.pro/db/65723


微信视频讲解:https://mp.weixin.qq.com/s/I7QujZrzJZRs6S0KdTMoBw

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS