游戏开发

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

2021-09-18  本文已影响0人  higher2017

本系列文章主要是本人在游戏服务端开发过程中，遇到的一些不那么为人熟知但我又觉得比较重要的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锁的知识可以看官网介绍。

按照锁类型来介绍：

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

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');

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的数据加上了记录共享锁。

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引之前或最后一个索引之后的间隙的锁。间隙锁是一个区间的锁，在数轴上表示就是一个区间。我这里使用主键等值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。

事务的加锁规则：

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

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

锁争用的情况可以查看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; 环路等待条件形成，死锁发生了

简化后的业务模型：

死锁解释：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		 环路等待条件形成，死锁发生了

简化后的业务模型：

这个案例和案例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);		 死锁发生

其他案例：

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

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

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

关于死锁的几点建议：

上一篇下一篇

猜你喜欢

热点阅读