MySQL进阶知识（四）--InnoDB锁的问题

本系列文章主要是本人在游戏服务端开发过程中，遇到的一些不那么为人熟知但我又觉得比较重要的MySQL知识的介绍。希望里面浅薄的文字能为了提供一点点的帮助。

前文

本文需要读者对InnoDB事务、锁机制有一定的基础。如果能先阅读InnoDB锁介绍 和 InnoDB不同SQL如何加锁 这两篇官网介绍将会极大帮助你理解本文的内容。

加锁是实现数据库并发控制的一个非常重要的技术（另一个是数据的版本控制）。不同的存储引擎有不同的加锁策略。MyISAM存储引擎采用的是表锁级别；InnoDB为了实现更高的并发将锁粒度设计为粒度更小的行锁级别。更小的锁粒度意味着更少的锁竞争，并发自然会上去（当然InnoDB也会通过版本控制的方式解决并发的问题）。但是在保证多用户并发存取数据时数据一致性情况下，更小的锁粒度就会带来更复杂的锁管理问题。其中开发面临的最为严重、棘手和常见的就是死锁问题。本文就是想通过锁类型、事务、死锁案例来介绍关于死锁的问题。

为什么会死锁，或者说死锁的条件是什么：

1. 锁的互斥（排它）条件：某个锁具有排它性，即一次只能由一个线程占用。如果此时还有其它线程请求该锁，则请求者只能等待，直至占有锁的线程释放；
2. 不剥夺条件：线程A当前已经持有一个锁a，但又提出了锁b请求，而锁b已被其它线程B占有。此时线程A会阻塞直到持有锁b为止。等待锁b过程对自己持有的锁a持有不放；
3. 环路等待条件：指在发生死锁时，必然存在一个【线程】<—>【资源】的环形链，即线程集合{T0，T1，T2，···，Tn}中的T0正在等待一个T1占用的锁、T1正在等待T2占用的锁、……、Tn正在等待已被T0占用的锁。环路等待的结果就是因为大家都在等待对方而卡死；
   
   简单说就是不同线程相互等待对方已经持有的锁，同时又不肯释放自己已经持有且对方需要的锁。

InnoDB的锁简单介绍：

本文的主要目标是介绍本人在工作中遇到过的一些数据库死锁的情况。先简单介绍一下InnoDB中常见的行锁和这些锁的一些工作原理与目的，介绍的程度只限于能理解后面死锁，如果想了解更多关于InnoDB锁的知识可以看官网介绍。

按照锁类型来介绍：

• 共享锁（S Lock）、排它锁（X Lock）：

简单来理解这两个锁就是读锁和写锁。它们的原则就是四点：

1. 不同事务能同时持有同一个共享锁（读锁之间的共享）；
2. 一个事务持有共享锁时，其他事务不能持有排它锁（读锁和写锁之间的互斥）；
3. 一个事务持有排它锁时，其他事务不能持有共享锁（读锁和写锁之间的互斥）；
4. 一个事务持有排它锁时，其他事务不能持有排它锁（写锁之间的互斥）。

通过读写锁分离增加读的并发性能（有效的老套路）。

共享锁和排它锁是按照锁类型介绍。下面我按照锁粒度来介绍，先给出测试表的表结构和初始测试数据：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t` VALUES ('0', '0', '0'),('5', '5', '5'),('10', '10', '10'),('15', '15', '15'),('20', '20', '20'),('25', '25', '25');

• 记录锁（R Lock）：

InnoDB对指定索引的行数据加的锁，加锁范围只在指定行的数据（当where条件命中数据不止一条时，加锁行数会是多行）。记录锁是单个点的锁，在数轴上的表示一个个的点。我这里使用最简单的主键等值FOR UPDATE查询（并且表中有该指定主键值的数据）来介绍记录锁：
SELECT sleep(20),id,c FROM t WHERE id=10 FOR UPDATE;这条SQL就对id=10的数据加上了排它记录锁（还会有表级的意向排它锁，这里不做讨论）。
如果想观察锁等待和持有的过程可以通过如下方式（当然也可以通过begin;显式地进行事务的控制来做到调试，这里只是想模拟SQL耗时的行为）：

1. 在操作界面A执行：SELECT sleep(30),id,c,d FROM t WHERE id = 10 FOR UPDATE（sleep(20)起到的作用是卡住这条命令，方便调试）；

2. 在操作界面B马上执行：SELECT sleep(30),id,c,d FROM t WHERE id = 10 FOR UPDATE；

3. 在操作界面B马上执行：SHOW ENGINE INNODB STATUS，这个时候就能看到1和2步骤SQL（蓝色是步骤1，红色是步骤2）的锁持有和锁等待情况（如果想查看更具体的锁持有信息需要执行：set global innodb_status_output_locks=on;）：
   

   image.png

4. 第3步可以换成通过performance_schema.data_lock_waits和performance_schema.data_locks这两张表来看锁持有和锁等待。（低版本的用户可以看：INFORMATION_SCHEMA下的INNODB_TRX、INNODB_LOCKS以及INNODB_LOCK_WAITS）
   
   performance_schema.data_locks事务锁持有记录的表（蓝框：1步骤的事务，红框：2步骤的事务）。这张表就非常清晰地展示了正在使用的锁的相关信息（比SHOW ENGINE INNODB STATUS好用太多了），事务1就持有了id=10的记录锁、事务2就在等待id=10的记录锁：
   

   image.png

PS：如果有兴趣的同学可以测试一下排它记录锁和共享记录锁。SELECT sleep(20),id,c FROM t WHERE c1 = 10 LOCK IN SHARE MODE会对id=10的数据加上了记录共享锁。

• 间隙锁（G Lock）：

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引之前或最后一个索引之后的间隙的锁。间隙锁是一个区间的锁，在数轴上表示就是一个区间。我这里使用主键等值FOR UPDATE查询，但表中没有该指定主键值的数据：SELECT * FROM t WHERE id=11 FOR UPDATE表中没有id=11这条数据，SQL会锁住id在（11，15）这个范围，当其他事务打算插入id=12的数据时，就会和(11,15)间隙锁有冲突而等待阻塞住。下面是具体的操作：

事务A	事务B
begin; select * from t where id=11 for update;
	begin;insert into t values(12,12,12); // 这个时候这条SQL就会被阻塞住

image.png
这个图就展示了这两个事务的锁的详细信息。蓝色：事务A；黄色：事务B；。红色框框就是事务B的锁等待，它打算持有的就是排它间隙意向锁（这个间隙范围就是(11,15)，15是t中最靠近11的索引的值）。关于insert语句是如何持有锁、持有什么锁的介绍看下这里。

说明：

我后面关于死锁展开的讨论，其实只要了解记录锁和间隙锁就已经足够。所以这里InnoDB一些锁我并没有介绍，比如Next-Key Lock; Insert Intention Lock; AUTO-INC Lock。

InnoDB事务和锁使用介绍：

对数据库来说，事务的核心特性就是ACID。

• A：保证事务中所有的操作要么全成功要么全失败；
• C：确保在任何情况下（即使多事务并发），当前事务操作结果和操作预期一致；
• I：确保多个并发事务同时对同一数据进行读写和修改的能力（隔离有不同的级别，本文只讨论可重复读RR）；
• D：确保事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的加锁规则：

事务的加锁规则就是两阶段锁：当一个事务逐步执行事务内部的各个操作，对锁的操作分为两个阶段：

• 扩展阶段：单个事务内部的操作被执行时，事务会尝试持有这个操作需要用到的锁（如果这时这个锁被其他事务持有则会等待该锁（等待锁的时间有限制））
• 释放阶段：事务持有的锁会在事务提交或回滚的时候才释放。
  在事务执行完所有操作之前，事务都处于扩展阶段，这时只会持有锁或尝试持有锁而不会释放锁。当事务全部操作都执行完或者事务因特殊原因被迫中断回滚，则事务进入释放阶段，这个阶段将会把事务所持有的所有锁进行释放。

数据库关于锁的基本性能监控：

锁争用的情况可以查看sys库的metrics这个视图，相关的数据如下：

image.png

死锁案例：

InnoDB是默认开启死锁检测的（innodb_deadlock_detect=ON），这种情况的死锁报错：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

如果禁用死锁检测（innodb_deadlock_detect=OFF），出现死锁时事务会等待50s（innodb_lock_wait_timeout的默认值）后提示等待超时：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

还是建议维持默认值，虽然可能会有一点性能的损耗，但是这样死锁发生能立即定位问题。

案例1：

先介绍一个最简单的死锁情况，两个事务对同一张表的update操作。

事务A	事务B	备注
begin; update t set c = c+1 where id = 5;		事务A持有id=5的记录锁
	begin; update t set c = c-1 where id = 10;	事务B持有id=10的记录锁
update t set c = c+1 where id = 10;		这个时候事务A会阻塞住，等待事务B释放id=10的记录锁
	update t set c = c-1 where id = 5;	环路等待条件形成，死锁发生了

简化后的业务模型：

• 事务A - 玩家1（id=5）对玩家2（id=10）点赞，需要同时对两个玩家的友好度加一（字段c代表玩家的友好度）；
  
• 事务B - 玩家2（id=10）对玩家1（id=5）点不喜欢，需要同时对两个玩家的友好度减一；
• 两个事务同时发生；

死锁解释：UPDATE ... WHERE ...语句对唯一索引进行定值操作时，是对指定行加记录锁。比如：update t set c=5 where id=5，这条SQL只对id=5这唯一的一行加一个记录锁。这种情况就是典型的同表锁交叉的情况，也是线上最常遇到的一种情况。避免这种死锁情况的方法也很简单，就是对事务中的多个update操作进行排序。比如我们在代码中对事务B的update语句排序，排序后的SQL就是：begin;update t set c=c+1 where id=5;update t set c=c+1 where id=10;。这样避免不同事务出现锁交叉的情况即可。

PS：replace into在批量更新数据的情况下，也会出现update这种情况的死锁，解决方案也是如此。

案例2：

案例2和案例1的情况差不多，只不过这一次不是同一张表的死锁（t1的表结构和t表一致）。死锁流程如下：

事务A	事务B	备注
begin; update t set c=c+1 where id = 5; update t set c=c+1 where id=10;		事务A持有表t中id=5、id=10的记录锁
	begin; update t1 set c=c-1 where id=5; update t1 set c=c-1 where id=10;	事务B持有表t1中id=5、id=10的记录锁
update t1 set c=c+1 where id=5; update t1 set c=c+1 where id=10;		事务A阻塞，等待事务B释放表t1中id=5和id=10的记录锁
	update t set c=c-1 where id=5; update t set c=c-1 where id=10	环路等待条件形成，死锁发生了

简化后的业务模型：

• 事务A - 玩家1对玩家2点赞，两个玩家好感度同时加一，并且两个玩家的金币数量（表t1中的c字段表示玩家持有的金币数量）同时加一；
  
• 事务B - 玩家2对玩家1点不喜欢，两个玩家好感度同时减一，并且两个玩家的金币数量扣一；
• 两个事务同时发生；

这个案例和案例1是同一个功能模块，死锁原理都是一样的，算是同个坑踩了两次。为了避免这种死锁情况，我们在案例1对id排序的基础上再加上对表进行排序。修正后的事务顺序如下：

事务A	事务B
begin; update t set c = c+1 where id = 5; update t set c = c+1 where id = 10;
	begin; update t set c = c-1 where id = 5; update t set c = c-1 where id = 10;
update t1 set c = c+1 where id = 5; update t1 set c = c+1 where id = 10;
	update t1 set c = c-1 where id = 5; update t1 set c = c-1 where id = 10

案例3：

本案例是复合主键（两个字段组合而成的主键）的排序问题导致的死锁，表结构如下：

CREATE TABLE `t2` (
  `id1` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  PRIMARY KEY (`id1`,`id2`)
);

没有初始数据

• 死锁过程：

事务A	事务B	备注
begin; insert into t2 values(1,1);		事务A持有id1=1,id2=1的记录锁
	begin; insert into t2 values(1,2);	事务B持有id1=1,id2=2的记录锁
insert into t2 values(1,2); insert into t2 values(2,3); insert into t2 values(2,4);		事务A等待事务B释放id1=1,id2=2的记录锁
	insert into t2 values(1,1); insert into t2 values(2,4); insert into t2 values(2,3);	死锁发生

• 简化后的业务模型：
  
  两个玩家玩井字旗游戏，需要保存玩家的下棋记录。为了保证性能，就做成批量延迟回写（实际SQL是：insert into t2 values(1,1),(1,2),(2,4),(2,3);，这里为了方便说明就拆开）。id1是玩家的playerId，id2是玩家持有的棋子id。
  
• 死锁的关键:
  
  业务代码只对id1进行排序，而没有对id1+id2两个都排序。其次业务代码也有问题，本来应该只由一个玩家回写数据即可，代码同时回写两个玩家的数据了。

其他案例：

还有一些非常复杂的死锁问题。由于我在实际工作中确实没有遇到，所以这里只把连接贴出来给大家参考一下。

非主键的唯一索没有排序导致的死锁;

https://developer.aliyun.com/article/282229;

https://zhuanlan.zhihu.com/p/282815816;

建议自己手动复现下以上介绍的死锁案例，这样能帮助理解死锁原理和事务的机制。

PS：死锁日志如何查看：https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html

关于死锁的几点建议：

• 只要对InnoDB的每个事务都只包含一个操作那么就不会出现死锁的问题，死锁只可能会出现在包含两个或以上有操作数据的SQL的事务中。但是很多情况下，为了更好的性能表现或者业务的事务特性我们势必要进行批量或者合并操作。当我们写这样的事务时，一定要小心谨慎，确认主键是否排好序，是否含有非主键的唯一索引。当然最重要的前提还是要懂InnoDB的锁机制。

• 每次新活动上线之前都要使用SHOW ENGINE INNODB STATUS查看下最近是否有新的死锁情况出现(看是否有LATEST DETECTED DEADLOCK部分即可)，这个动作可以使用脚本完成，每次构建项目就检查一遍。避免死锁代码流到线上环境。

• 线上监控：死锁告警级别应该调到邮件、短信、电话通知通知开发人员这一级别（死锁的监控可以通过客户端应用程序报错通知或者云服务的异常告警列表加上死锁告警）。

• 数据库帮我们解决死锁的方式，当InnoDB发生死锁，数据库会有两种处理方式：
  1. 等待指定时间然后超时；
  2. 主动探测到死锁，中止一个事务让另一个事务成功执行。当然这个的前提是innodb_deadlock_detect=ON（默认值就是ON），就能保证即使死锁数据库也能快速失败。（建议这种方式，没什么必要就不要改默认值）。