Mysql 锁

发表于 更新于 分类于 ， ， 本文字数： 12k 阅读时长 ≈ 11 分钟

Mysql 锁

不同存储引擎对于锁的支持粒度是不同的，由于 InnoDB 是 Mysql 的默认存储引擎，所以本文以 InnoDB 对于锁的支持进行阐述。

锁的分类

为了解决并发一致性问题，Mysql 支持了很多种锁来实现不同程度的隔离性，以保证数据的安全性。

独享锁和共享锁

InnoDB 实现标准行级锁定，根据是否独享资源，可以把锁分为两类：

  • 独享锁（Exclusive），简写为 X 锁，又称为“写锁”、“排它锁”。
    • 独享锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。
    • 使用方式：SELECT ... FOR UPDATE;
  • 共享锁（Shared），简写为 S 锁，又称为“读锁”。
    • 共享锁锁定的资源可以被其他用户读取，但不能修改。在进行 SELECT 的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。
    • 使用方式：SELECT ... LOCK IN SHARE MODE;

为什么要引入读写锁机制？

实际上，读写锁是一种通用的锁机制，并非 Mysql 的专利。在很多软件领域，都存在读写锁机制。

因为读操作本身是线程安全的，而一般业务往往又是读多写少的情况。因此，如果对读操作进行互斥，是不必要的，并且会大大降低并发访问效率。正式为了应对这种问题，产生了读写锁机制。

读写锁的特点是：读读不互斥、读写互斥、写写互斥。简言之：只要存在写锁，其他事务就不能做任何操作。

注：InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。

悲观锁和乐观锁

基于加锁方式分类，Mysql 可以分为悲观锁和乐观锁。

  • 悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
    • 在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）
    • 实现方式：使用数据库中的锁机制。
  • 乐观锁 - 假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。只在更新的时候，判断一下在此期间是否有其他线程更新该数据。
    • 实现方式：更新数据时，先使用版本号机制或 CAS 算法检查数据是否被修改。

为什么要引入乐观锁？

乐观锁也是一种通用的锁机制，在很多软件领域，都存在乐观锁机制。

锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。那么，为了提高并发度，能不能尽量不加锁呢？

乐观锁，顾名思义，就是假设最好的情况——每次访问数据时，都假设数据不会被其他线程修改，不必加锁。虽然不加锁，但不意味着什么都不做，而是在更新的时候，判断一下在此期间是否有其他线程更新该数据。乐观锁最常见的实现方式，是使用版本号机制或 CAS 算法（Compare And Swap）去实现。

  • 乐观锁的优点是：减少锁竞争，提高并发度。

  • 乐观锁的缺点是：

    • 存在 ABA 问题。所谓的 ABA 问题是指在并发编程中，如果一个变量初次读取的时候是 A 值，它的值被改成了 B，然后又其他线程把 B 值改成了 A，而另一个早期线程在对比值时会误以为此值没有发生改变，但其实已经发生变化了
    • 如果乐观锁所检查的数据存在大量锁竞争，会由于不断循环重试，产生大量的 CPU 开销。

【示例】Mysql 乐观锁示例

假设，order 表中有一个字段 status，表示订单状态：status 为 1 代表订单未支付；status 为 2 代表订单已支付。现在，要将 id 为 1 的订单状态置为已支付，则操作如下：

1
2
3
4
5
select status, version from order where id=#{id}

update order
set status=2, version=version+1
where id=#{id} and version=#{version};

乐观锁更多详情可以参考：使用 mysql 乐观锁解决并发问题

全局锁、表级锁、行级锁

前文提到了，锁，意味着互斥，意味着阻塞。在高并发场景下，锁越多，阻塞越多，势必会拉低并发性能。在不得不加锁的情况下，显然，加锁的范围越小，锁竞争的发生频率就越小，系统的并发程度就越高。但是，加锁也需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销，锁粒度越小，系统的锁操作开销就越大。因此，在选择锁粒度时，也需要在锁开销和并发程度之间做一个权衡。

根据加锁的范围，MySQL 的锁大致可以分为：

  • 全局锁 - “全局锁”会锁定整个数据库。
  • 表级锁（table lock） - “表级锁”锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。表级锁有：
    • 表锁 - 表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。表锁一般是在数据库引擎不支持行锁的时候才会被用到的。
    • 元数据锁（MDL） - MDL 不需要显式使用，在访问一个表的时候会被自动加上。
    • 意向锁（Intention Lock）
    • 自增锁（AUTO-INC）
  • 行级锁（row lock） - “行级锁”锁定指定的行记录。这样其它线程还是可以对同一个表中的其它行记录进行操作。行级锁有：
    • 记录锁（Record Lock）
    • 间隙锁（Gap Lock）
    • 临键锁（Next-Key Lock）
    • 插入意向锁

以上各种加锁粒度，在不同存储引擎中的支持情况并不相同。如：InnoDB 支持全局锁、表级锁、行级锁；而 MyISAM 只支持全局锁、表级锁。

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

全局锁

“全局锁”会锁定整个数据库。

要给整个数据库加全局锁，可以执行以下命令：

1
flush tables with read lock

执行命名后，整个库处于只读状态，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

如果要释放全局锁，可以执行以下命令：

1
unlock tables

此外，在客户端断开的时候会自动释放锁。

全局锁的典型使用场景是，做全库逻辑备份。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。对于全部是 InnoDB 引擎的库，建议选择使用 –single-transaction 参数，对应用会更友好。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。

表级锁

“表级锁”会锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。

表锁

表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。

**表锁的语法是 lock tables … read/write**，示例如下：

1
2
3
4
5
// 为 xxx 表加 MDL 读锁
lock tables xxx read;

// 为 xxx 表加 MDL 写锁
lock tables xxx write;

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现应用程序里有 lock tables 这样的语句，需要追查一下，比较可能的情况是：

  • 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
  • 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

元数据锁（MDL）

元数据锁，英文为 metadata lock，缩写为 MDL。MySQL 5.5 版本中引入了 MDL。MDL 的作用是，保证读写的正确性。MDL 不需要显式使用，在访问一个表的时候会被自动加上。

  • 对一个表做“增删改查”操作的时候，加 MDL 读锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • 对一个表做“结构变更”操作的时候，加 MDL 写锁。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL 会直到事务提交才释放。在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那么在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

  1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 SELECT 语句，此时就先对该表加上 MDL 读锁；
  2. 然后，线程 B 也执行了同样的 SELECT 语句，此时并不会阻塞，因为“读读”并不冲突；
  3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 SELECT 语句，就都会被阻塞。如果此时有大量该表的 SELECT 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁（Intention Lock）

InnoDB 支持不同粒度的锁定，允许行锁和表锁共存。当存在表级锁和行级锁的情况下，必须先申请意向锁，再获取行级锁。意向锁是表级锁，表示事务稍后需要对表中的行使用哪种类型的锁（共享或独享）。意向锁是 InnoDB 自动添加的，不需要用户干预。

意向锁有两种类型：

  • 意向共享锁（IS） - 表示事务有意向对表中的行设置共享锁（S）。

  • 意向独享锁（IX） - 表示事务有意向对表中的行设置独享锁（X）。

比如 SELECT ... FOR SHARE 设置 IS 锁， SELECT ... FOR UPDATE 设置 IX 锁。

意向锁的规则如下：

  • 一个事务在获得某个数据行的共享锁（S）之前，必须先获得表的意向共享锁（IS）或者更强的锁；
  • 一个事务在获得某个数据行的独享锁（X）之前，必须先获得表的意向独享锁（IX）。

也就是，当执行插入、更新、删除操作，需要先对表加上 IX 锁，然后对该记录加 X 锁。而快照读（普通的 SELECT）是不会加行级锁的，快照读是利用 MVCC 实现一致性读，是无锁的。

不过，SELECT 也是可以对记录加共享锁和独享锁的，具体方式如下：

1
2
3
4
5
// 先在表上加上 IS 锁，然后对读取的记录加 S 锁
select ... lock in share mode;

// 当前读：先在表上加上 IX 锁，然后对读取的记录加 X 锁
select ... for update;

意向共享锁和意向独享锁是表级锁，不会和行级的共享锁和独享锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独享表锁（lock tables ... write）发生冲突。

如果申请的锁与现有锁兼容，则锁申请成功；反之，则锁申请失败。锁申请失败的情况下，申请锁的事务会一直等待，直到存在冲突的锁被释放。如果存在与申请的锁相冲突的锁，并且该锁迟迟得不到释放，就会导致死锁。

为什么要引入意向锁？

如果没有意向锁，那么加独享表锁时，就需要遍历表里所有记录，查看是否有记录存在独享锁，这样效率会很低。

有了意向锁，在对记录加独享锁前，会先加上表级别的意向独享锁。此时，如果需要加独享表锁，可以直接查该表是否有意向独享锁：如果有，就意味着表里已经有记录被加了独享锁。这样一来，就不用去遍历表里的记录了。

综上所述，意向锁的目的是为了快速判断表里是否有记录被加锁。

自增锁（AUTO-INC）

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表级锁，锁不是在一个事务提交后才释放，而是在执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是，AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

  • 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
  • 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
  • 当 innodb_autoinc_lock_mode = 1：
    • 普通 insert 语句，自增锁在申请之后就马上释放；
    • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

以上模式中，innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在“主从复制的场景”中会发生数据不一致的问题。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

在 InnoDB 引擎中，行锁是通过给索引上的索引项加锁来实现的。如果没有索引，InnoDB 将会通过隐藏的聚簇索引来对记录加锁。此外，在 InnoDB 引擎中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此，如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

行锁的具体实现算法有三种：Record Lock、Gap Lock 以及 Next-Key Lock。

记录锁（Record Lock）

记录锁（Record Lock）锁定一个记录上的索引，而不是记录本身。例如，执行 SELECT value FROM t WHERE value BETWEEN 10 and 20 FOR UPDATE; 后，会禁止任何其他事务插入、更新或删除 t.value 值在 10 到 20 范围之内的数据，因为该范围内的所有现有值之间的间隙已被锁定。

记录锁始终锁定索引记录，即使表定义为没有索引。如果表没有设置索引，InnoDB 会自动创建一个隐藏的聚簇索引并使用该索引进行记录锁定。

Record Lock 是有 S 锁和 X 锁之分的：

  • 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
  • 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

【示例】记录锁示例

注：测试环境的事务隔离级别为可重复级别

初始化数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建表
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`value` INT(10) DEFAULT 0,
	PRIMARY KEY (`id`)
)
	ENGINE = InnoDB
	DEFAULT CHARSET = `utf8`;

-- 分别插入 id 为 1、10、20 的数据
INSERT INTO `t`(`id`, `value`) VALUES (1, 1);
INSERT INTO `t`(`id`, `value`) VALUES (10, 10);
INSERT INTO `t`(`id`, `value`) VALUES (20, 20);

事务一、添加 X 型记录锁

1
2
3
4
5
6
7
8
9
10
11
-- 开启事务
BEGIN;

-- 对 id 为 1 的记录添加 X 型记录锁
SELECT * FROM `t` WHERE `id` = 1 FOR UPDATE;

-- 延迟 20 秒执行后续语句，保持锁定状态
SELECT SLEEP(20);

-- 释放锁
COMMIT;

事务二、被锁定的行记录无法修改

1
2
3
4
5
-- 修改 id = 10 的行记录，正常执行
UPDATE `t` SET `value` = 0 WHERE `id` = 10;

-- 修改 id = 1 的行记录，由于 id = 1 被 X 型记录锁锁定，直到事务一释放锁，方能执行
UPDATE `t` SET `value` = 0 WHERE `id` = 1;

间隙锁（Gap Lock）

间隙锁（Gap Lock）锁定索引之间的间隙，但是不包含索引本身。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，它们彼此不冲突，不同事务可以在间隙上持有冲突锁，并不存在互斥关系。例如，事务 A 可以在某个间隙上持有 S 型间隙锁，而事务 B 在同一间隙上持有 X 型间隙锁。允许存在冲突间隙锁的原因是：如果从索引中清除记录，则必须合并不同事务在该记录上持有的间隙锁。

间隙锁只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。如果将事务隔离级别更改为 读已提交，则间隙锁定对搜索和索引扫描禁用，并且仅用于外键约束检查和重复键检查。

在 MySQL 中，间隙锁默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是 RR 事务隔离级别。

【示例】间隙锁示例

注：测试环境的事务隔离级别为可重复级别

初始化数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建表
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`value` INT(10) DEFAULT 0,
	PRIMARY KEY (`id`)
)
	ENGINE = InnoDB
	DEFAULT CHARSET = `utf8`;

-- 分别插入 id 为 1、10、20 的数据
INSERT INTO `t`(`id`, `value`) VALUES (1, 1);
INSERT INTO `t`(`id`, `value`) VALUES (10, 10);
INSERT INTO `t`(`id`, `value`) VALUES (20, 20);

事务一、添加间隙锁

1
2
3
4
5
6
7
8
9
10
11
-- 开启事务
BEGIN;

-- 对 id 为 1 的记录添加间隙锁
SELECT * FROM `t` WHERE `id` BETWEEN 1 AND 10 FOR UPDATE;

-- 延迟 20 秒执行后续语句，保持锁定状态
SELECT SLEEP(20);

-- 释放锁
COMMIT;

事务二、被锁定范围内的行记录无法修改

1
2
3
4
5
6
7
8
9
10
11
12
-- 插入 id 为 1 到 10 范围之外的数据，正常执行
INSERT INTO `t`(`id`, `value`) VALUES (15, 15);

-- 更新 id 为 1 到 10 范围之外的数据，正常执行
UPDATE `t` SET `value` = 0 WHERE `id` = 20;

-- 插入 id 为 1 到 10 范围之内的数据，被阻塞
INSERT INTO `t`(`id`, `value`) VALUES (5, 5);

-- 更新 id 为 1 到 10 范围之内的数据，被阻塞
UPDATE `t` SET `value` = 0 WHERE `id` = 1;
UPDATE `t` SET `value` = 0 WHERE `id` = 10;

临键锁（Next-Key Lock）

临键锁（Next-Key Lock）是记录锁和间隙锁的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙（它锁定一个前开后闭区间）。

假设索引包含值 10、11、13 和 20，那么该索引可能的 Next-Key Lock 涵盖以下区间：

1
2
3
4
5
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

所以，Next-Key Lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。MVCC 不能解决幻读问题，Next-Key 锁就是为了解决幻读问题而提出的。在可重复读（REPEATABLE READ）隔离级别下，使用MVCC + Next-Key 锁可以解决幻读问题。

只有可重复读、串行化隔离级别下的特定操作才会取得间隙锁或 Next-Key Lock。在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取间隙锁或 Next-Key Lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用间隙锁或 Next-Key Lock。

索引分为主键索引和非主键索引两种，如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。在 UPDATE、DELETE 操作时，MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的 Next-Key Lock。

插入意向锁

插入意向锁不是意向锁，而是一种特殊的间隙锁。当一个事务试图插入一条记录时，需要判断插入位置是否已被其他事务加了间隙锁（临键锁（Next-Key Lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻）；在此期间，会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

假设存在值为 4 和 7 的索引记录。分别尝试插入值 5 和 6 的单独事务在获得插入行上的排他锁之前，每个事务都使用插入意向锁锁定 4 和 7 之间的间隙，但不要互相阻塞，因为行不冲突。

【示例】获取插入意向锁

初始化数据

1
2
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

事务 A 对 id 大于 100 的索引记录设置独享锁。独享锁包括了 id=102 之前的间隙锁：

1
2
3
4
5
6
7
mysql> BEGIN;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

事务 B 将记录插入到间隙中。事务在等待获取独享锁时获取插入意向锁。

1
2
mysql> BEGIN;
mysql> INSERT INTO child (id) VALUES (101);

死锁

“死锁”是指两个或多个事务竞争同一资源，并请求锁定对方占用的资源，从而导致恶性循环的现象。

产生死锁的场景：

  • 当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。

  • 多个事务同时锁定同一个资源时，也会产生死锁。

死锁示例

（1）数据初始化

1
2
3
4
5
6
7
8
9
10
-- 创建表 test
CREATE TABLE `test` (
	`id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	`value` INT(10) NOT NULL
);

-- 数据初始化
INSERT INTO `test` (`id`, `value`) VALUES (1, 1);
INSERT INTO `test` (`id`, `value`) VALUES (2, 2);
INSERT INTO `test` (`id`, `value`) VALUES (3, 3);

（2）两个事务严格按下表顺序执行，产生死锁

事务 A 事务 B
BEGIN; BEGIN;
– 查询 value = 4 的记录
SELECT * FROM test WHERE value = 4 FOR UPDATE;
– 结果为空
– 查询 value = 5 的记录
SELECT * FROM test WHERE value = 5 FOR UPDATE;
– 结果为空
INSERT INTO test (id, value) VALUES (4, 4);
– 锁等待中
INSERT INTO test (id, value) VALUES (5, 5);
– 锁等待中
– 由于死锁无法执行到此步骤
COMMIT;		 – 由于死锁无法执行到此步骤
COMMIT;

死锁是如何产生的

行锁的具体实现算法有三种：Record Lock、Gap Lock 以及 Next-Key Lock。Record Lock 是专门对索引项加锁；Gap Lock 是对索引项之间的间隙加锁；Next-Key Lock 则是前面两种的组合，对索引项以其之间的间隙加锁。

只有在可重复读或以上隔离级别下的特定操作才会取得 Gap Lock 或 Next-Key Lock，在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 Gap Lock 或 Next-Key Lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用 Gap Lock 或 Next-Key Lock。

在 MySQL 中，Gap Lock 默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是可重复读事务隔离级别。

当我们执行以下查询 SQL 时，由于 value 列为非唯一索引，此时又是 RR 事务隔离级别，所以 SELECT 的加锁类型为 Gap Lock，这里的 gap 范围是 (4,+∞）。

1
SELECT * FROM test where value = 4 for update;

执行查询 SQL 语句获取的 Gap Lock 并不会导致阻塞，而当我们执行以下插入 SQL 时，会在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 Gap Lock 是冲突的，所以当其它事务持有该间隙的 Gap Lock 时，需要等待其它事务释放 Gap Lock 之后，才能获取到插入意向锁。

以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。

1
INSERT INTO `test` (`id`, `value`) VALUES (5, 5);

img

另一个死锁场景

InnoDB 存储引擎的主键索引为聚簇索引，其它索引为辅助索引。如果使用辅助索引来更新数据库，就需要使用聚簇索引来更新数据库字段。如果两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了。

img

出现死锁的步骤：

img

综上可知，在更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。

避免死锁

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。由此可知，要想避免死锁，就要从这几个必要条件上去着手：

  • 更新表时，尽量使用主键更新，减少冲突；
  • 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
  • 设置合理的锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
  • 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
  • 在允许幻读和不可重复读的情况下，尽量使用读已提交事务隔离级别，可以避免 Gap Lock 导致的死锁问题；
  • 还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。

解决死锁

当出现死锁以后，有两种策略：

  • 设置事务等待锁的超时时间。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • 开启死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，我直接把这个时间设置成一个很小的值，比如 1s，也是不可取的。当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。为了解决死锁问题，不同数据库实现了各自的死锁检测和超时机制。InnoDB 的处理策略是：将持有最少行级排它锁的事务进行回滚。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。因此，死锁检测可能会耗费大量的 CPU。

参考资料