Mysql锁详解（行锁、表锁、意向锁、Gap锁、插入意向锁）

最新推荐文章于 2024-04-26 16:01:44 发布

Hello，C++！

最新推荐文章于 2024-04-26 16:01:44 发布

阅读量2.6k

点赞数 3

分类专栏：数据库文章标签： mysql

原文链接：https://blog.csdn.net/u010841296/article/details/84204701?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1-84204701-blog-115849091.pc_relevant_aa&depth_1-utm_source=distribute.pc_relevant_t0.n

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

锁：对 “某种范围” 的数据上 “某种锁”
1.“某种范围”：行、表
2.“某种锁”
2.1 共享锁Shared Locks（S锁）
2.2 排他锁Exclusive Locks（X锁）
2.3 表锁：意向锁 Intention Locks，意向锁相互兼容
2.4 行锁：记录锁(Record Locks)
2.5 行锁：间隙锁(Gap Locks)
2.6 *行锁：临键锁(Next-Key Locks)
2.7 行锁：插入意向锁(Insert Intention Locks)
2.8 锁的兼容性
2.9 表锁：自增锁(AUTO-INC Locks)
3.锁的选择
帮助知识
1.查看事务、锁的sql

锁：对 “某种范围” 的数据上 “某种锁”

1.“某种范围”：行、表 2.“某种锁”

2.1 共享锁Shared Locks（S锁）

1、兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁

2、加锁方式：select…lock in share mode

2.2 排他锁Exclusive Locks（X锁）

1、兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁

2、加锁方式：select…for update

2.3 表锁：意向锁 Intention Locks，意向锁相互兼容

1、表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”

2、意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存，。

3、例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

4、1）意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁2）意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

q1：为什么意向锁是表级锁呢？
当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

（1）如果意向锁是行锁，则需要遍历每一行数据去确认；

（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

q2：意向锁怎么支持表锁和行锁并存？
（1）首先明确并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。
（2）如果事务A对某一行上锁，其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，让行锁与表锁共存，就会带来很多问题。于是有了意向锁的出现，如q1的答案中，数据库不需要在检查每一行数据是否有锁，而是直接判断一次意向锁是否存在即可，能提升很多性能。

5、下图表示意向锁和共享锁、排他锁的兼容关系。
意思是当事务A对某个数据范围（行或表）上了“某锁”后，另一个事务B是否能在这个数据范围上“某锁”。

在这里插入图片描述

意向锁相互兼容，因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。

因为上了表级S锁后，不允许其他事务再加X锁，所以表级S锁和X、IX锁不兼容

上了表级X锁后，会修改数据，所以表级X锁和 IS、IX、S、X（即使是行排他锁，因为表级锁定的行肯定包括行级速订的行，所以表级X和IX、行级X）不兼容。

注意：上了行级X锁后，行级X锁不会因为有别的事务上了IX而堵塞，一个mysql是允许多个行级X锁同时存在的，只要他们不是针对相同的数据行。

2.4 行锁：记录锁(Record Locks)

（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
（2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

2.5 行锁：间隙锁(Gap Locks)

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

比如在 1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, ∞)，
（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

2.6 *行锁：临键锁(Next-Key Locks)

（1）record lock + gap lock, 左开右闭区间。
（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update
（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
（4）Next-Key Lock在不同的场景中会退化:

在这里插入图片描述

2.7 行锁：插入意向锁(Insert Intention Locks)

（1）插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生。
（2）在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
（3）假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

（4）插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁。

本例子和插入意向锁无关：是Gap锁和排它锁的关系
例如test表存在若干数据的数据，先开始一个事务A，插入一条n=5的数据；（图中步骤1）
此时如果开始一个事务B，执行查询 select * from test where n > 4 for update，事务B会申请Gap锁（4, ∞)，申请成功后，被事务A的x锁阻塞，直到x锁被释放。（图中步骤2）
可以看到图中步骤3的信息，在等待事务释放X锁

在这里插入图片描述

2.8 锁的兼容性

在这里插入图片描述

2.9 表锁：自增锁(AUTO-INC Locks)

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

3.锁的选择

1.如果更新条件没有走索引，例如执行”update test set name=“hello” where name=“world”;” ，此时会进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。

2.如果更新条件为索引字段，但是并非唯一索引（包括主键索引），例如执行“update test set name=“hello” where code=9;” 那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因：

首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；

还要保证锁定的区间不能插入新的数据。

如果更新条件为唯一索引，则使用Record Lock（记录锁）。

帮助知识

1.查看事务、锁的sql
select from information_schema.innodb_locks;
select from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;