mysql锁类型&加锁区间

前言

InnoDB 通过 MVCC 和 NEXT-KEY Locks，解决了在可重复读的事务隔离级别下出现幻读的问题。

本文主要讲加锁类型及加锁区间

并发处理下的问题

更新丢失

事务T1读取了数据，并执行了一些操作，然后更新数据。事务T2也做处理包含相同行的事，则T1和T2更新数据时可能会覆盖对方的更新，从而引起错误。

更新丢失可以通过加“乐观锁”来解决这个问题。

 

脏读

脏数据是指事务对缓冲池中的行记录record进行了修改，但是还没提交！如果这时读取缓冲池中未提交的行数据就叫脏读，违反了事务的隔离性。

 

不可重复读

在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，插入第二个事务的修改，且第二个事务提交。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。
事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。

事务隔离级别

为了解决“隔离”与“并发”的矛盾，产生了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。

隔离级别(由高到低)：Serializable>Repeatable read>Read committed>Read uncommitted

隔离级别	含义	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	事务中的修改，即使没有提交，对其他事务都是可见的	❌	❌	❌
读已提交（Read Committed）	事务从开始到提交之前，所做的修改对其他事务都不可见	✅	❌	❌
可重复读（Repeatable Read）	同一事务中多次读取同样的记录结果是一致的	✅	✅	❌
可序列化（Serializable）	在读取的每一行数据上加锁，强制事务串行执行	✅	✅	✅

锁的分类

记录锁（Record Lock）

顾名思义，记录锁就是为某行记录加锁，它封锁该行的索引记录：

代码块

SQL

-- id 列为主键列或唯一索引列

SELECT * FROM table WHERE id = 1 FOR UPDATE;

id 为 1 的记录行会被锁住。

需要注意的是：id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。

同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

间隙锁（Gap Lock）

间隙锁锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

在执行某些 SQL 时，InnoDB 会自动加间隙锁，这个我们在下面案例会提到。

临键锁（Next-Key Lock）

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

需要强调的一点是，InnoDB 中行级锁是基于索引实现的。

一个错误认知：临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁和间隙锁。唯一索引也存在临键锁和间隙锁（参考案例一）

意向锁

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在，为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

举例来说，在对记录r加X锁之前，已经有事务对表1加了S表锁，那么表1上已存在S锁，之后事务需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表基表的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。

——出自《MYSQL技术内幕：InnoDB存储引擎》

自增锁

在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。当对含有自增长计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

代码块

SQL

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做自增长锁。

这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

加锁范围

原则 1： 为便于理解，加锁的基本单位认为是 next-key lock。next-key lock 是前开后闭区间。

原则 2： 查找过程中访问到的对象才会加锁。

优化 1： 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2： 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例

初始数据构建语句

SQL

CREATE TABLE `t` (

  `id` int(11) NOT NULL,

  `c` int(11) DEFAULT NULL,

  `d` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `c` (`c`)

) ENGINE=InnoDB;

​

insert into t values(0,0,0),(5,5,5),

(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：等值查询间隙锁

sessionA 加锁范围：(5,10)

 

案例二：非唯一索引等值锁

sessionA 加锁范围：(0,5] (5,10)

 

案例三：主键索引范围锁

sessionA 加锁范围：[10,15]

 

案例四：非唯一索引范围锁

sessionA 加锁范围：(5,10] (10,15]

 

案例五：唯一索引范围锁 bug

sessionA 加锁范围： (10,20]

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。

但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

照理说，这里锁住 id=20 这一行的行为，其实是没有必要的。因为扫描到 id=15，就可以确定不用往后再找了。但实现上还是这么做了，因此我认为这是个 bug。

 

 

案例六：非唯一索引上存在"等值"的例子

准备工作：给表 t 插入一条新记录

SQL

insert into t values(30,10,30);

 

索引C如下图：

如下图案例：

根据原则1，加锁（5,10]，根据优化2加锁(10,15)，故结果如下图：

 

案例七：limit 语句加锁

session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。

案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了

因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示

 

这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围

 

案例八：一个死锁的例子

八.1

 

session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；

session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；

然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

 

Q：session B 的 next-key lock 不是还没申请成功吗，为什么死锁了？

A：session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。

把next-key lock当作间隙锁加行锁来看。 间隙锁直接不互锁；而间隙锁只保护间隙，不让insert

 

八.2

下面来看一个出自《MYSQL技术内幕：InnoDB存储引擎》(283页) 的死锁例子

书中把死锁原因解释的带上了情感色彩。我们应该用实际原理来解释：

sessionA加了对4的记录锁

sessionB是范围查询,想加(-∞,4]，依次获取锁，获取到（2，4）的间隙锁后，获取4的记录锁失败。阻塞等待sessionA的释放。

之后，sessionA想插入一条主键值为3的记录，因为sessionB已获取（2，4）的间隙锁，故不能插入，等待sessionB的释放。此时形成死锁