MySQL innodb的锁机制解读-白红宇

MySQL innodb的锁机制解读

阅读量：5154 次

发布时间：2019-06-13

本文共 3793 字，大约阅读时间需要 12 分钟。

网上有许多关于innodb的锁机制的文章，有许多文章讲述的不明白或者有问题，最近研究了好久，结合网上资料和实践操作，记录一下，供大家参考。如果有不对的地方，请随时留言。

一 Innodb具备的锁种类

1. 表锁（MySQL提供的，跟存储引擎无关）

2. 行锁（Innodb存储引擎实现）

二 Innodb内部实现的锁种类

1. 记录锁

对应Innodb的行锁，记录锁锁的是索引记录，不是具体的数据记录。

2. 间隙锁

锁定索引记录间隙的锁，确保索引记录的间隙不变，间隙锁是针对事务隔离等级是可重复读或以上级别而言的！

例如： create table t1(id int, v1 int,v2 int, primary key(id), key `idx_v1` (`v1`)) engine=innodb;

数据行有以下几行：

id	v1	v2
1	1	0
2	3	1
3	4	2
5	5	3
7	7	4
10	9	5

间隙锁一般是针对非唯一索引而言的

上面的数据表中v1的索引区间有

(-∞,1)

(1,3)

(3,4)

(4,5)

(5,7)

(7,9)

(9,+∞)

假如更新v1=5的数据行，那么此时会在索引记录idx_v1加上间隙锁，会把5之前的区间锁定，锁定的区间是(4,5)和(5,7)，也就是区间(4,7)，同时找到v1=5的数据行的主键，在该记录上加上记录锁，锁定该行记录。

3. 后码锁

记录锁和间隙锁的结合，对于innodb中，更新非唯一索引记录时，会加上后码锁。如果更新记录为空，就不能加记录锁，此时只剩下间隙锁。多条更新语句可能导致不同事务中锁定的索引区间重复，导致插入失败。

例子（事务隔离等级为可重复读，主要看一下后码锁是记录锁和间隙锁的结合）

transaction 1	transaction 2
BEGIN;	BEGIN;
update t1 set v2=1 where v1=6;(这句SQL会加上后码锁，但是v1=6的记录不存在，后码锁是记录锁和间隙锁组成的，此时只能加上间隙锁，会锁住idx_v1的索引区间是(5,7))	update t1 set v2=2 where v1=7;(这句SQL也是加上后码锁，v1=7的记录存在，后码锁是由记录锁和区间锁组成，首先会使用记录锁，锁定v1=7的主键，即id=7的记录行，同时会使用间隙锁，会锁住idx_v1的索引区间是(5,9))
INSERT INTO t1 set id=8, v1=6,v2=2;(v1=6在自己的idx_v1锁定的索引区间(5,7));	INSERT INTO t1 set id=9, v1=8,v2=2;(v1=8在自己的idx_v1锁定的索引区间(5,9));
锁等待	插入成功
插入成功	rollback;
rollback;

由此可以看出，一个索引区间是可以被多个间隙锁锁定的，更新不当的时候，会造成死锁。

其中在transaction 2中，如果"INSERT INTO t1 set id=9, v1=8,v2=2;"这条语句换成"INSERT INTO t1 set id=9, v1=6,v2=2;"就会造成死锁，因为两个间隙锁都锁定了(5,7)这个区间。

同时在transaction 2中，执行update语句的时候，已经在id=7的主键索引上加了记录锁，任何在其他事务(例如transaction 1)中尝试更新id=7的行，都会被挂起，直到transaction 2提交或回滚。

三锁选择

执行更新类语句，像SELECT ... FOR UPDATE, UPDATE,DELETE语句

　　 1. 如果更新条件没有索引，例如执行"SELECT * FROM t1 where v2=2 for update"，那么此时更新操作会使用表锁。多条更新SQL语句在不同的事务中同时执行，先取得表锁的事务会将其他事务挂起，直到当前事务提交或回滚。

使用表锁的原因：

由于更新的数据没有索引，MySQL只能做扫表操作，扫表的时候，要阻止其他任何的更新操作，所以会上升为表锁。

2. 如果更新条件为索引字段，但并非唯一索引（包括主键），例如执行"SELECT * FROM t1 where v1=1 for update"，那么此时更新会使用后码锁。

　　使用后码锁的原因：

　　 a)首先要保证在符合条件的记录上加上排他的记录锁，会锁定当前非唯一索引和这些满足条件的记录对应的主键索引；b)还要保证更新的索引记录区间不能插入新数据。

3. 如果更新条件字段为唯一索引，使用记录锁。

Innodb会根据唯一索引，找到记录的主键索引，将符合条件的主键索引和唯一索引加上记录锁。

说明：Innodb的索引结构

Innodb支持聚簇索引，但是聚簇索引只能是主键索引，并且每张表只能有一个聚簇索引，所谓聚簇索引，就是索引在物理存储上是顺序存放的。主键索引就是聚簇索引，主键索引的叶子节点存放的是记录的物理地址，根据主键索引可以直接访问记录内容。非主键索引在B-tree索引的叶子节点上存放的并不是记录的物理地址，而是主键索引的物理地址。

当给非唯一索引加上后码锁的时候（例如更新非唯一主键索引对应的记录内容），Innodb会采用后码锁。首先将满足条件的非唯一索引对应的主键索引和满足条件的非唯一索引加上记录锁。然后会给非唯一索引加上间隙锁，将当前非唯一索引对应的索引区间加上间隙锁，禁止在该区间的任何INSERT操作。

四间隙锁演示

说明：加后码锁的时候，并未锁住间隙两端的记录，那么两端的记录是可以更新的，但是如果更新记录时会影响到间隙锁，那需要被挂起，等待间隙锁被释放。

transaction 3

transaction 4

BEGIN;

SELECT * FROM t1 WHERE v1=5 FOR UPDATE;(加上间隙锁，锁定了idx_v1的索引区间是(4,7)，同时会把满足条件的记录的主键索引上加上行锁)

UPDATE 类操作

UPDATE t1 set v2=2 WHERE v1=4;(OK,不会被挂起，间隙锁锁只锁间隙，而这条更新SQL并未影响idx_v1在区间(4,7)的间隙锁控制范围);

UPDATE t1 set v2=2 WHERE v1=7;(OK,不会被挂起)

UPDATE t1 set v2=2 WHERE v1=6;(OK,不会被挂起)

UPDATE t1 set v1=1 WHERE v1=4;(OK,不会被挂起，更新索引)

UPDATE t1 set v1=5 WHERE v1=4;(由于4,7区间被封锁，这个操作会被挂起)

UPDATE t1 set v1=8 WHERE v1=7;(OK，不会被挂起，更新索引)

UPDATE t1 set v1=1 WHERE v1=9;(OK,不会被挂起，因为条件和目的索引的值都不在封锁的区间)

UPDATE t1 set v1=5 WHERE v1=7;(由于4,7区间被封锁，这个操作会被挂起)

UPDATE t1 set v1=2 WHERE v1=7(这个操作会被挂起)

由上面这两组SQL可以看出来，间隙锁锁住的区间为4,7，当更新这两端的记录的时候，如果不改变区间的值，压根就跟区间索引没关系，那么更新操作就不会被间隙锁挂起。如果更新间隙锁区间的两端的索引值，且更新索引后的区间包含当前锁住的区间，那么可以更新成功。如果更新索引后，不能包含已经锁定的区间，那么更新操作会被挂起。

INSERT 类操作

INSERT INTO t1 set id=11, v1=5, v2=5;(挂起，transaction 3封闭的idx_v1间隙是(4,7)，插入v1=5肯定会被挂起)

INSERT INTO t1 set id=11, v1=8,v2=8(OK,v1不在封锁区间);

主要看一下在封锁区间两端的插入情况

INSERT INTO t1 set id=4,v1=4,v2=2;(v1=4是封锁区间的左侧值，此操作会被挂起)

INSERT INTO t1 set id=0,v1=4,v2=2;(此操作OK，可以执行)

INSERT INTO t1 set id=6, v1=7,v2=2;(v1=7是封锁区间的右侧值，此操作会被挂起)

INSERT INTO t1 set id=8, v1=7,v2=2;(此操作OK，可以执行)

当往封边区间两端插入值的时候，需要看要插入的值的主键是否在封锁区间对应的主键的范围。

具体解释：