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

小伙伴们有没有觉得学习数据库很有意思？有意思就对了！今天就给大家带来《当Mysql - InnoDB行锁遇到复合主键和多列索引》，以下内容将会涉及到MySQL、锁、索引、InnoDB、lock，若是在学习中对其中部分知识点有疑问，或许看了本文就能帮到你！

背景

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

比如表

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列是辅助索引，所以此时会使用

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可能会返回之前不存在的行。

比如在同一个事务内，执行两次查询

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的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《当Mysql - InnoDB行锁遇到复合主键和多列索引》文章吧，也可关注golang学习网公众号了解相关技术文章。