欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

当 Mysql - InnoDB 行锁遇到复合主键和多列索引

程序员文章站 2022-06-02 12:24:39
...

背景

今天在配合其他项目组做系统压测，过程中出现了偶发的死锁问题。分析代码后发现有复合主键的 update 情况，更新复合主键表时只使用了一个字段更新，同时在事务内又有对该表的 insert 操作，结果出现了偶发的死锁问题。

比如表t_lock_test中有两个主键都为 primary key(a,b)，但是更新时却通过update t_lock_test .. where a = ?，然后该事务内又有insert into t_lock_test values(...)

InnoDB 中的锁算法是Next-Key Locking，很可能是因为这个点导致的死锁，但是复合主键下会触发Next-Key Locking吗，那多列联合 unique 索引下又会触发Next-Key Locking吗，书上并没有找到答案，得实际测试一下。

InnoDB 中的锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访 [插图]。InnoDB 存储引擎会在行级别上对表数据上锁，这固然不错。不过 InnoDB 存储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的 LRU 列表，删除、添加、移动 LRU 列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

由于使用锁时基本都是在 InnoDB 存储引擎下，所以跳过 MyISAM，直接讨论 InnoDB。

锁类型

InnoDB 存储引擎实现了如下两种标准的行级锁：

  • 共享锁 (S Lock)，允许事务读一行数据
  • 排它锁 (x lOCK)，允许事务删除或更新一条数据

如果一个事务 T1 已经获得了 r 的共享锁，那么另外的事务 T2 可以立即获得行 r 的共享锁，因为读取并没有改变 r 的数据，成这种情况为锁兼容（Lock Compatible）。但若有其他的事务 T3 箱获得行 r 的排它锁，则其必须等待 T1、T2 释放行 r 上的共享锁 —— 这种情况称为锁不兼容。

排它锁和共享锁的兼容性：

\ X S
X 不兼容 不兼容
S 不兼容 兼容

InnoDB 中对数据进行 UPDATE/DELETE 操作会产生行锁，也可以显示的添加行锁（也就是平时所说的 “悲观锁”）

select for update
lock in share mode

锁算法

InnoDB 有 3 种行锁的算法，其分别是：

Record Lock：单个行记录上的锁，就是字面意思的行锁

Record Lock 会锁住索引记录 (注意这里说的是索引，因为 InnoDB 下主键索引即数据), 如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时对 InnoDB 存储引擎会使用隐式的主键来进行锁定。

Gap Lock：间隙锁，锁定一个范围，但不包含记录本身

Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Gap Lock 和 Next-Key Lock 的锁定区间划分原则是一样的。

例如一个索引有 10/11/13 和 20 这四个值，那么该索引被划分的的区间为：

(-∞,10]
(10,11]
(11,13]
(13,20]
(20,+∞)

采用 Next-Key Lock 的锁定技术称为 Next-Key Locking。其设计的目的是为了解决 Phantom Problem，这将在下一小节中介绍。而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁（predict lock）的一种改进。

当查询的索引含有唯一 (unique) 属性时（主键索引，唯一索引）InnoDB 存储引擎会对 Next-Key Lock 优化，将其降级为 Record Lock，即仅锁住索引本身，不是范围。

下面来看一个辅助索引（非唯一索引）下的锁示例：

CREATE TABLE z ( a INT, b INT, PRIMARY KEY(a), KEY(b) );

INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;

表 z 的列 b 是辅助索引，若果事务 A 中执行：

SELECT * FROM z WHERE b=3 FOR UPDATE

由于 b 列是辅助索引，所以此时会使用Next-Key Locking算法，锁定的范围是 (1,3]。**特别注意，InnoDB 还会对辅助索引的下一个值加上 Gap Lock，即还有一个辅助索引范围为 (3,6] 的锁。**因此，若在新事务 B 中运行以下 SQL，都会被阻塞：

1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;//S锁
2. INSERT INTO z SELECT 4,2;
3. INSERT INTO z SELECT 6,5;

第 1 个 SQL 不能执行，因为在事务 A 中执行的 SQL 已经对聚集索引中列 a=5 的值加上 X 锁，因此执行会被阻塞。

第 2 个 SQL，主键插入 4，没有问题，但是插入的辅助索引值 2 在锁定的范围 (1,3] 中，因此执行同样会被阻塞。

第 3 个 SQL，插入的主键 6 没有被锁定，5 也不在范围 (1,3] 之间。但插入的 b 列值 5 在另下一个 Gap Lock 范围 (3,6] 中，故同样需要等待。

而下面的 SQL 语句，由于不在 Next-Key Lock 和 Gap Lock 范围内，不会被阻塞，可以立即执行：

INSERT INTO z SELECT 8,6;
INSERT INTO z SELECT 2,0;
INSERT INTO z SELECT 6,7;

从上面的例子可以发现，Gap Lock 的作用是为了组织多个事务将数据插入到统一范围内，这样会导致幻读问题（Phantom Problem）。例子中事务 A 已经锁定了 b=3 的记录。若此时没有 Gap Lock 锁定 (3,6]，其他事务就可以插入索引 b 列为 3 的记录，这会导致事务 A 中的用户再次执行同样查询会返回不同的记录，即导致幻读问题的产生。

用户也可以通过以下两种方式来显示的关闭 Gap Lock（但不推荐）：

  • 将事务的隔离级别设置为 READ COMMITED
  • 将参数 innodb_locks_unsafe_for_binlog 设置为 1

在 InnoDB 中，对于 Insert 的操作，会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许插入。对于上面的例子，事务 A 已经锁定了表 z 中 b=3 的记录，即已经锁定了 (1,3] 的范围，这时若在其他事务中执行如下插入也会导致阻塞：

INSERT INTO z SELECT 2,2

因为在辅助索引列 b 上插入值为 2 的记录时，会监测到下一个记录 3 已经被索引，修改 b 列值后，就可以执行了

INSERT INTO z SELECT 2,0

幻读 (Phantom Problem)

幻读是指在同一事务下，连续执行两次同样的 SQL 语句可能会导致不同的结果，第二次的 SQL 可能会返回之前不存在的行。

在默认的事务隔离级别（REPEATABLE READ）下，InnoDB 存储引擎采用 Next—Key Locking 机制来避免幻读问题。

复（联）合主键与锁

上面的锁机制介绍 (摘自《Mysql 技术内幕 InnoDB 存储引擎 第 2 版》)，只是针对辅助索引和聚集索引，那么复合主键下行锁的表现形式又是怎么样呢？从书上并没有找到答案，实际来测试一下。

首先创建一个复合主键的表

CREATE TABLE `composite_primary_lock_test` (
  `id1` int(255) NOT NULL,
  `id2` int(255) NOT NULL,
  PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (10, 10);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 8);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 6);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 6);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 3);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 1);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 1);
INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (7, 1);

事务 A 先来查询 id2=6 的列，并添加行锁

select * from composite_primary_lock_test where id2 = 6 lock in share mode

此时的锁会降级到 Record Lock 吗？事务 B Update 一条 Next-Key Lock 范围内的数据（id1=1,id2=8）证明一下：

UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

结果是 UPDATE 被阻塞了，那么再来试试加锁时在 where 中把两个主键都带上：

select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode

执行 UPDATE

UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

结果是 UPDATE 没有被阻塞

上面加锁的 id2=6 的数据，不只 1 条，那么再试试对唯一的数据 id2=8，只根据一个主键加锁呢，会不会降级为行级锁：

select * from composite_primary_lock_test where id2 = 8 lock in share mode;


UPDATE `composite_primary_lock_test` SE WHERE `id1` = 12 AND `id2` = 10;

结果也是被阻塞了，实验证明：

复合主键下，如果加锁时不带上所有主键，InnoDB 会使用 Next-Key Locking 算法，如果带上所有主键，才会当作唯一索引处理，降级为 Record Lock，只锁当前记录。

多列索引（联合索引）与锁

上面只验证了复合主键下的锁机制，那么多列索引呢，会不会和复合索引机制相同？多列 unique 索引呢？

新建一个测试表，并初始化数据

CREATE TABLE `multiple_idx_lock_test` (
  `id` int(255) NOT NULL,
  `idx1` int(255) NOT NULL,
  `idx2` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`idx1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `multiple_idx_lock_test` 
ADD UNIQUE INDEX `idx_multi`(`idx1`, `idx2`) USING BTREE;

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (1, 1, 1);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (5, 2, 2);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (7, 3, 3);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (4, 4, 4);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (2, 4, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (3, 5, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (8, 6, 5);
INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (6, 6, 6);

事务 A 查询增加 S 锁，查询时仅使用 idx1 列，并遵循最左原则：

select * from multiple_idx_lock_test where idx1 = 6 lock in share mode;

现在插入一条 Next-Key Lock 范围内的数据：

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

结果是被阻塞了，再试一遍通过多列索引中所有字段来加锁：

select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode;

插入一条 Next-Key Lock 范围内的数据：

INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

结果是没有被阻塞

由此可见，当使用多列唯一索引时，加锁需要明确要锁定的行（即加锁时使用索引的所有列），InnoDB 才会认为该条记录为唯一值，锁才会降级为 Record Lock。否则会使用 Next-Key Lock 算法，锁住范围内的数据。

总结

在使用 Mysql 中的锁时要谨慎使用，尤其时更新 / 删除数据时，尽量使用主键更新，如果在复合主键表下更新时，一定通过所有主键去更新，避免锁范围变大带来的死锁等问题。

参考

  • 《Mysql 技术内幕 InnoDB 存储引擎 第 2 版》 - 姜承尧
相关标签： mysql innodb 锁

上一篇： php的XML文件解释类应用实例

下一篇： php实现根据字符串生成对应数组的方法

推荐阅读