当Mysql行锁遇到复合主键与多列索引详解

2023-08-15 02:43:04 236

背景

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

比如表t_lock_test中有两个主键都为primarykey(a,b)，但是更新时却通过updatet_lock_test..wherea=?，然后该事务内又有insertintot_lock_testvalues(...)

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

InnoDB中的锁

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

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

锁类型

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

共享锁(SLock)，允许事务读一行数据
排它锁(xlOCK)，允许事务删除或更新一条数据

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

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

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

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

selectforupdate

锁算法

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

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

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

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

Next-KeyLock：GapLock+RecordLock，锁定一个范围，并且锁定记录本身

GapLock和Next-KeyLock的锁定区间划分原则是一样的。

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

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

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

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

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

CREATETABLEz(aINT,bINT,PRIMARYKEY(a),KEY(b));

INSERTINTOzSELECT1,1;
INSERTINTOzSELECT3,1;
INSERTINTOzSELECT5,3;
INSERTINTOzSELECT7,6;
INSERTINTOzSELECT10,8;

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

SELECT*FROMzWHEREb=3FORUPDATE

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

1.SELECT*FROMzWHEREa=5LOCKINSHAREMODE;//S锁
2.INSERTINTOzSELECT4,2;
3.INSERTINTOzSELECT6,5;

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

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

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

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

INSERTINTOzSELECT8,6;
INSERTINTOzSELECT2,0;
INSERTINTOzSELECT6,7;

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

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

将事务的隔离级别设置为READCOMMITED
将参数innodb_locks_unsafe_for_binlog设置为1

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

INSERTINTOzSELECT2,0

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

INSERTINTOzSELECT2,0

幻读(PhantomProblem)

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

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

复（联）合主键与锁

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

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

CREATETABLE`composite_primary_lock_test`(
`id1`int(255)NOTNULL,
`id2`int(255)NOTNULL,
PRIMARYKEY(`id1`,`id2`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;

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

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

select*fromcomposite_primary_lock_testwhereid2=6lockinsharemode

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

UPDATE`composite_primary_lock_test`SEWHERE`id1`=1AND`id2`=8;

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

select*fromcomposite_primary_lock_testwhereid2=6andid1=5lockinsharemode

执行UPDATE

UPDATE`composite_primary_lock_test`SEWHERE`id1`=1AND`id2`=8;

结果是UPDATE没有被阻塞

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

select*fromcomposite_primary_lock_testwhereid2=8lockinsharemode;

UPDATE`composite_primary_lock_test`SEWHERE`id1`=12AND`id2`=10;

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

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

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

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

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

CREATETABLE`multiple_idx_lock_test`(
`id`int(255)NOTNULL,
`idx1`int(255)NOTNULL,
`idx2`int(255)DEFAULTNULL,
PRIMARYKEY(`id`,`idx1`)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;

ALTERTABLE`multiple_idx_lock_test`
ADDUNIQUEINDEX`idx_multi`(`idx1`,`idx2`)USINGBTREE;

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

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

select*frommultiple_idx_lock_testwhereidx1=6lockinsharemode;

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

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

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

select*frommultiple_idx_lock_testwhereidx1=6andidx2=6lockinsharemode;

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

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

结果是没有被阻塞

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

总结

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

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。

参考

《Mysql技术内幕InnoDB存储引擎第2版》-姜承尧

当Mysql行锁遇到复合主键与多列索引详解

热门推荐

随机推荐