• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    当Mysql行锁遇到复合主键与多列索引详解

    背景

    今天在配合其他项目组做系统压测,过程中出现了偶发的死锁问题。分析代码后发现有复合主键的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存储引擎实现了如下两种标准的行级锁:

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

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

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

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

    select for update

    锁算法

    InnoDB有3种行锁的算法,其分别是:

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

    Record Lock会锁住索引记录(注意这里说的是索引,因为InnoDB下主键索引即数据),ruguo 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(但不推荐):

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

    INSERT INTO z SELECT 2,0

    因为在辅助索引列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主键UUID和自增主键的区别及优劣分析
    • 深入分析mysql为什么不推荐使用uuid或者雪花id作为主键
    • spring boot整合mybatis利用Mysql实现主键UUID的方法
    • Python3 操作 MySQL 插入一条数据并返回主键 id的实例
    • MySQL中主键与rowid的使用陷阱总结
    • 浅析MySQL 主键使用数字还是uuid查询快
    上一篇:MySQL如何选择合适的索引
    下一篇:mysql随机抽取一定数量的记录实例讲解
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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