第八篇：MySQL之锁详解

1、加锁的原因

事务并发造成的数据不一致，举例如下：
事务A：update test set k = k + 1 where id = 1;
事务B：update test set k = k + 1 where id = 1;
按照正常执行的话，提交完事务A和B后，k会变成k+2;
如果不对对应记录加锁的话，事务B和事务A同时更新的话，会导致k变成k+1，导致数据丢失。

2、快照读和当前读区别

我们都知道MySQL中的读分为两种：
1、快照读： 读取的不一定是最新的数据，读取的是MVCC为事务提供的快照。
除了串行化的隔离级别之外，其他级别的快照读是不会加任何锁的。
串行化的加锁和可重复读隔离级别的当前单独加锁是一样的。

select xx from xx;

2、当前读： 每次读取的就是当前数据记录对应的最新值！
select xx from xx lock in share mode
select xx from xx for update
update xx set xx = xx
delete from xx where xx = xx
insert into xx values()

对于不同的隔离级别，会加不同的锁。

3、MySQL中的加锁方式：

MySQL中事务采用两阶段加锁方式：
两阶段指的是：
阶段1、当事务中的语句开始执行时，才会申请对应的锁
阶段2、当事务提交或者回滚后，事务中所有语句申请的锁才会释放。

如上图所示，当执行sql语句2的时候才会申请对应的锁，当事务Acommit后才会sql语句2对应的锁。sql语句1也是。

4、MySQL中锁的类型：

MySQL中的锁按照读写分为两种：
1、X锁
X锁叫做排他锁，也叫做写锁，当写数据的时候需要申请X锁。当申请X锁时，数据上不能有X锁和S锁。也就是当其他事务在数据上写数据或者读数据的时候，新的事务不能申请X锁，写数据，当前事务只能被阻塞，等待其他事务释放锁之后，才能申请。
2、S锁
S锁叫做共享锁，也叫做读锁，当读数据的时候需要申请S锁。当申请S锁时，数据上不能有X锁。可以有S锁，也就是当其他事务正在写数据的时候，当前事务不能读取当前数据。S锁读取数据指的是当前读，快照读是不需要加锁的。

MySQL中锁按照加锁对象分为以下四种：
1、行锁
行锁就是加在数据行上的锁，分为两种：
1、S锁：加在数据行上的读锁 sql语句为：select xx from xx lock in share mode;
2、X锁：加在数据行上的写锁 sql语句为：select xx from xx for update

2、表锁
表锁是直接加在表上的锁，分为四种：
1、S锁：加在表上的读锁。sql语句为 lock tables xx read;
2、X锁：加在表上的写锁。sql语句为 lock tables xx write;
3、IS锁：加在表上的意向读锁，当事务申请行锁中的S锁时，MySQL会 自动为表申请IS锁。
4、IX锁：加在表上的意向写锁。当事务申请行锁中的X锁时，MySQL会自动为表申请IX锁。

	IS 锁	IX锁	行S锁	行X锁	表S锁	表X锁
IS锁	兼容	兼容	兼容	兼容	兼容	冲突
IX锁	兼容	兼容	兼容	兼容	冲突	冲突
行S锁	兼容	兼容	兼容	冲突	兼容	冲突
行X锁	兼容	兼容	冲突	冲突	冲突	冲突
表S锁	兼容	冲突	兼容	冲突	兼容	冲突
表X锁	冲突	冲突	冲突	冲突	冲突	冲突

为什么会有意向锁呢？
当事务申请表X锁时，需要表中的每条数据行都不能有行S锁和行X锁。如果没有意向锁的话，MySQL需要从头到尾依次扫描每行来判断是否存在行锁，这种做法效率低下。所以，引入意向锁，当事务申请行S锁时，会自动申请IS锁，当事务申请行X锁时，会自动申请IX锁。

IS锁和IX锁是兼容的
IS锁和IX锁是用来克制表锁的，IS锁和IX锁分别对应的为行S锁和行X锁，因为一个表中有很多数据行，同一个数据行的S锁和X锁才会冲突。所以IS锁和IX锁是兼容的。在行锁申请完意向锁后，还需要进一步申请对应的行锁，如果对应的行锁获取不到，事务仍然要被阻塞。

3、间隙锁

我们都知道，MySQL中的数据是以B+树的形式存在的。一个索引对应一个B+树。非叶子节点存放的是索引数据，叶子节点中存放的是对应的数据行。并且叶子节点是按照某一列的顺序排列的。
比如某一列的数据为[0,5,10,15];
则该列对应区间有（-无穷,0）,(0,5)，（5，10），（10，15），（15，+无穷）。区间对应的就是间隙。间隙锁就是在间隙上加锁，与插入本区间的插入操作冲突，防止插入，用来解决幻读问题，这个我们到后面再细说。

4、NEXT-KEY 锁
NEXT-KEY锁是MySQL中加锁的基本单位。实际就是间隙锁加行锁的组合。对一条数据行加NEXT-KEY锁，实际就是对该数据行加行锁和其前一个间隙加间隙锁。
比如对10加NEXT-KEY锁，为（5，10]，间隙右边变成了闭区间，也就是加了一个行锁。

加锁实验详解

我们都知道MySQL有4个隔离级别：读未提交、读提交、可重复读、串行化。等会我们对这四个隔离级别逐一分析。

都哪些操作会引起加锁呢？
1、当前读：直接读取最新的数据，快照读是读取MVCC提供的快照，是不会加锁的。
2、更新操作
3、删除操作

我们都知道MySQL中的数据是以B+树也就是索引的方式存在的，对于不同的索引类型，加锁的方式也不一样。

MySQL中的锁是加在索引上的

所以我们从四个隔离级别，三种操作，和索引方面来逐一实践加锁原则。

首先看一下我们的表结构和数据

create table t(c1 int primary key, c2 int, c3 int, c4 int, unique index i_c2(c2), index i_c3(c3));

insert into t values (10, 11, 12, 13), (20, 21, 22, 23), (30, 31, 32, 33), (40, 41, 42, 43);

注意事项：
1、别忘了切换事务隔离级别。MySQL默认的隔离级别是可重复读。
2、别忘了手动开启事务，因为MySQL默认的是隐式事务，就是在语句执行前后自动的开启和提交事务。需要用start transaction来手动开启事务，通过commit 和rollback来提交和回滚事务。
3、我们可以使用MySQL自带的sql语句来查询当前存在的锁信息。以我使用的MySQL8.0.22为例，查询语句为select * from performance_schema.data_locks; ，不同的MySQL版本，查询语句可能有出入。

查询得到的锁信息如上图所示，其中有很多的列，我只截取了关键信息列：
1、INDEX_NAME:加锁的索引名字
1、LOCK_TYPE：加锁的类型，TABLE代表表锁，RECORD代表行锁。
2、LOCK_MODE：加锁的模式：
X代表NEXT-KEY的X锁
X，REC_NOT_GAP代表行X锁
X，GAP代表间隙锁
S，代表NEXT-KEY的S锁
S，REC_NOT_GAP代表行S锁
S，GAP代表间隙锁
3、LOCK_DATA:锁对应的数据，主键索引对应的为主键ID，非主键索引对应的是索引列数据和对应的主键ID。

1、读未提交

1.1、主键索引
1.1.1、主键索引上等值查询
a、查询的记录存在

start transaction;
select * from t where c1 = 10 for update;
select * from performance_schema.data_locks;
commit;

如上图所示，加了两个锁，一个是IX锁，一个加在主键上的c1=10的数据记录的行X锁

start transaction;
select * from t where c1 = 10 lock in share mode;
select * from performance_schema.data_locks;
commit;

如上图所示，加了一个IS锁，一个是c1=10的数据记录的行S锁。

b、查询的记录不存在

start transaction;
select * from t where c1 = 11 lock in share mode;
select * from performance_schema.data_locks;
commit;

找不到对应的数据记录，所以只加了一个IS锁。

start transaction;
select * from t where c1 = 11 for update;
select * from performance_schema.data_locks;
commit;

1.1.2、主键索引上的范围查询

start transaction;
select * from t where c1 >= 10 for update;
select * from performance_schema.data_locks;
commit;

如上图所示，加了一个IX锁，对满足查询条件c1>=10的数据记录10,20,30,40加了行X锁。

start transaction;
select * from t where c1 >= 10 lock in share mode;
select * from performance_schema.data_locks;
commit;

如上图所示，加了一个IS锁，对满足c1>=10的数据记录 10，20，30，40加了行S锁。

start transaction;
select * from t where c1 < 40 for update;
select * from performance_schema.data_locks;
commit;

如上图所示，加了一个IX锁和满足c1<40的数据记录10，20，30的数据行加行 X锁。

start transaction;
select * from t where c1 < 40 lock in share mode;
select * from performance_schema.data_locks;
commit;

如上图所示，加了一个IS锁，和对满足查询条件c1<40的数据行10，20，30加行S锁。

1.1.3、主键索引上的update操作

a、更新了其他索引

start transaction;
update t set c2 = c2 + 1 where c1 = 10;
select * from performance_schema.data_locks;
rollback;

如上图所示，加了一个IX锁和主键索引上c1=10的行X锁。

因为这条语句要更新c2索引，所以MySQL要对C2索引上c1=10的对应数据行加锁。其实MySQL没有直接对其进行加锁，但是即便没有加锁，其他事务也无法对c2索引上的这条数据加行X锁和行S锁的。

只有当其他事务尝试给c2索引上的c1=10的数据行加锁时，MySQL才真正会对c2索引加上真正的锁。

 select c2 from t where c2 = 11 lock in share mode;

如上图所示，当其他事务对c2索引中c1=10的数据行加锁时，会触发MySQL将之前为了减少开销资源的锁加上（省不了了，其他事务都盯上这个数据行了，哈哈）。对c2索引i_c2加锁，数据行对应的是c2=11,c1=10的这个数据行。

我个人猜测的是，MySQL为了想要节省对索引c2中c1=10的数据行加锁的资源开销，因为在当前事务执行期间，其他事务有很大可能不会对索引c2中c1=10的数据行加锁，这样 做的话可以减少资源开销。如果真有其他事务对索引c2中c1=10的数据行加锁，则会通过c1=10来判断主键索引上是否有锁，如果对应主键索引上有锁，则不能被其他事务加锁，并直接添加索引c2中c1=10的数据行加锁。

b、没有更新其他索引

start transaction;
update t set c4 = 1 where c1 = 10;
select * from performance_schema.data_locks;
rollback;

如上图所示，只加了IX锁和主键索引上c1=10的行X锁

1.1.3、主键索引上的delete操作

start transaction;
delete from t where c1 = 10;
select * from performance_schema.data_locks;
rollback;

因为c2和c3都有索引，索引c2，c3对应的数据行也要加锁。跟之前更新一样，只有其他事务对其数据行加锁的时候才会真正加锁。

mysql> select c1,c2 from t where c2 = 11 lock in share mode;
1205 - Lock wait timeout exceeded; try restarting transaction

mysql> select c1,c3 from t where c3 = 12 lock in share mode;
1205 - Lock wait timeout exceeded; try restarting transaction

1.2、唯一索引的加锁
1.2.1、等值查询：

start transaction;
select * from t wher c2 = 11 lock in share mode;
select * from performance_schema.data_locks;
rollback;

如上图所示，唯一索引与主键的不同之处为，唯一索引出了给自己加锁之外，还要对主键索引上对应的数据行加锁。唯一索引c2中包含的数据只有c1和c2两列，但是查询语句对应的为*，所以需要回表到主键索引上去查询完整的数据，所以需要对主键索引也加锁。

start transaction;
select c1 ,c2 from t wher c2 = 11 lock in share mode;
select * from performance_schema.data_locks;
rollback;

如果查询操作不需要回表，那么对应的主键索引也不会加锁。虽然没有加锁，但是如果有其他事务，想要修改c2=11对应的这行数据中c1和c2的值，同样会被阻塞。

start transaction;
select c1 ,c2 from t wher c2 = 11 for update;
select * from performance_schema.data_locks;
rollback;

如果加了写锁的话，会额外在主键索引上加行X锁。
我个人猜测因为是加了for update，系统会认为你将会更新数据，所以将主键索引锁住，防止别的事务更新数据。

1.2.2、唯一索引范围查询

start transaction;
select * from t wher c2 >= 21 for update;
select * from performance_schema.data_locks;
rollback;

对满足条件的数据行分别对主键索引和唯一索引加行X锁

1.2.3、唯一索引更新操作

start transaction;
update t set c3 = c3 + 1 where c2 >= 21;
select * from performance_schema.data_locks;
rollback;

对满足条件的数据行分别对主键索引和唯一索引加行X锁。如果更新了其他索引，则也会对其他索引加锁，并且不会立即加锁，当其他事务访问到了这行数据后才会加锁。

1.2.4、唯一索引删除操作
删除和更新一致。
只不过删除会对表上的所有索引加锁，但是不会立即加锁，只有其他事务触发之后，才会加锁。

1.3、非唯一索引

和唯一索引一致，不再展开说明，感兴趣的同学可以自己试试。

2、读已提交

读已提交和读未提交的唯一区别就是，读已提交通过MVCC提供的快照读解决了脏读。对于当前读，二者的处理方法一样。所以加锁方法也一样。

3、可重复读

可重复读和读已提交的区别就是可重复读解决了幻读的问题。可重复读通过引入间隙锁来防止插入操作，解决了幻读。

3.1、主键索引
3.1.1 主键索引等值查询

start transaction;
select * from t where c1 = 10 for update;
select * from performance_schema.data_locks;
rollback;

start transaction;
select * from t where c1 = 9 for update;
select * from performance_schema.data_locks;
rollback;

可以看出，当查询不存在的记录时，可重复读隔离级别下，为了防止幻读，MySQL在主键索引上加上了间隙锁（X，GAP），其中间隙为（-无穷，10），间隙锁只与插入操作冲突，和更新以及读操作没有冲突。

3.1.2、主键索引范围查询

start transaction;
select * from t where c1 >= 10 for update;
select * from performance_schema.data_locks;
rollback;

除了对满足查询条件的数据行加锁外，还要对满足查询条件的间隙加锁。
因为c1 = 10的间隙是（-无穷，10）小于10，所以没有加锁。
X指的是NEXT-KEY，包括行锁，以及行锁前面的间隙，比如 c1 = 20 对应的为间隙(10,20)和行锁20；

3.1.3、主键索引更新

start transaction;
update t set c2 = c2 + 1 where c1 >= 10 ;
select * from performance_schema.data_locks;
rollback;

除了满足条件的行锁外，间隙也会加锁。对于更新的其他索引也会加锁。只不过等到其他事务尝试加锁失败之后，才会真正的为其他索引加锁。

3.1.3、主键索引删除

删除和更新大概一致，只不过删除会触发表上的所有索引。

3.2、唯一索引
3.2.1、唯一索引等值查询

start transaction;
select * from t where c2 == 11 for update;
select * from performance_schema.data_locks;
rollback;

因为是唯一索引，所以不会加间隙锁，因为插入的时候会做唯一性判断。对唯一索引和主键索引加锁就行了。

start transaction;
select * from t where c2 == 12 for update;
select * from performance_schema.data_locks;
rollback;

因为c2 = 12的记录不存在，所以对c2的（10，20）间隙加间隙锁就可以了。防止插入导致幻读。

3.2.2、唯一索引范围查询

start transaction;
select * from t where c2 > 11 for update;
select * from performance_schema.data_locks;
rollback;

对于满足查询条件的主键索引和c2索引上的数据行加行X锁，对于c2索引上满足查询条件的间隙加间隙锁，分别为（11，21），（21，31），（31，41），（41，supremum）,supremum就是+无穷。

3.2.3、唯一索引更新操作

start transaction;
update t set c3 = c3 + 1 where c2 > 20;
select * from performance_schema.data_locks;
rollback;

和唯一索引范围查询一样，会对满足条件的主键索引和唯一索引对应的数据行加行X锁，以及对唯一索引满足条件的间隙加间隙锁。

除此之外，如果更新了其他索引，还会对其他索引中的数据行加行X锁，只不过只有其他事务在触发其他索引的数据行后，才会真正加锁，是一种懒加载机制。

mysql> select * from t where c3 = 32 for update;

3.2.4、唯一索引删除操作

start transaction;
delete from t where c2 > 20;
select * from performance_schema.data_locks;
rollback;

和唯一索引更新大概一致，只不过删除操作会对表上所有索引都加锁，同样也是当其他事务触发之后，才会真正加锁。

3.3、非唯一索引
3.3.1、非唯一索引等值查询

start transaction;
select * from t where c3 == 22 for update;
select * from performance_schema.data_locks;
rollback;

如上图所示，对于满足查询条件的主键索引和c3索引上的数据行加行X锁，并且对c3索引上c3=22的数据行的左右两边的间隙都需要加上锁。
（12，22）和（22，32），因为是非唯一索引，其他事务可能会从左右两侧插入c3=22的新数据，如果插入数据的c1小于20就插入左边，插入数据的c1大于20就插入右边，所以要对左右两边的间隙都加上锁。c1是主键。如果理解不了的话，建议同学们补充一下MySQL中索引和B+树的结构，以及非主键索引的基本结构——第七篇：MySQL之索引

**截图LOCK_MODE中的X并不是行X锁，而是NEXT-KEY锁，包含c3=22的行X锁和（12，22）的间隙锁。
X，GAP是间隙锁。
**

3.3.2、非唯一索引范围查询

start transaction;
select * from t where c3 >= 22 for update;
select * from performance_schema.data_locks;
rollback;

如上图所示，对满足查询条件的主键索引和c3索引的数据行加行X锁，对c3索引上满足条件的间隙加间隙锁。

**这里要特别说明的是（12，22）为什么也要加锁呢？**因为c3索引是依次按照c3的值和c1的值排列的。对于当前索引c3=22对应的叶子节点数据为:（c3:22，c1:20）,。有可能其他事务插入比如（c1:18，c2:21，c3:22）,此时c3 == c3 = 22,而新插入的数据的c1 = 18 < 20,所以新插入的记录落到了c3索引数据行（c3:22，c1:20）的左边，所以对应的左边间隙（12，22）也要加锁。

3.3.3、非唯一索引更新操作

start transaction;
update t set c2 = c2 + 1 where c3 >= 22;
select * from performance_schema.data_locks;
rollback;

和非唯一索引范围查询大概一样，只不过更新操作会对更新的索引中对应的数据行加上行X锁，只不过在其他事务触发后才会真正加锁，是一种懒加载机制。

mysql> select c2 from t where c2 = 21 for update;

3.3.4、非唯一索引删除操作

删除和更新操作大概一致，只不过删除会对表上所有的索引对应的数据行都加上行X锁。同学们感兴趣的可以试一下。

4、串行化

串行化和可重复读的加锁方式基本一致。
有一点不同：
1、当执行普通select语句也就是其他模式下的快照读的时候，也会按照当前读的方式来加锁。

自增锁

我们都知道MySQL中支持主键自增，自增锁就是为了主键自增生成的。
由于事务的并发，语句在申请自增主键时必须要加锁，否则可能会出现主键重复的情况。自增锁是一个表锁。
自增锁有以下三种策略，通过innodb_autoinc_lock_mode设置：
1、取0：插入记录时获取自增锁，当执行完插入语句后才释放自增锁。
2、取1：insert into xx select xx 会一直在执行完插入语句后才释放自增锁，其他语句都是在申请完自增锁后就释放。
3、取2：所有语句都是申请完自增锁后立即释放。

插入意向锁

插入意向锁也是一种间隙锁，和普通的间隙锁不同的是，普通的间隙锁与插入操作冲突，而插入意向锁与插入操作不冲突。

插入意向锁是数据插入之前在间隙中加的一种锁，当有并发事务同时往间隙中插入记录时，只要插入的数据本身不冲突，即使插入意向锁的间隙相同，也可以并发插入。插入意向锁之间并不冲突。

插入意向锁并不属于表锁，而是属于间隙锁，用来提高插入的并发度。

总结

1、对于非主键索引
1、读取的时候，如果读取了覆盖索引，则不会对主键索引加锁。但是其他事务不能通过其他索引来更新当前索引已经读取的数据。
2、读取的时候，如果读取了非主键索引之外的数据，则会对主键索引加锁。
3、当写的时候，不管是否更改了非主键索引之外的数据，都会对主键索引加锁。

2、对于update和delete操作更新其他索引（不包括主键索引）

如果update和delete操作会更新其他索引，则也会为其他索引加上锁，只不过会等到其他事务触发之后，才会真正加锁。因为为其他索引加锁会消耗资源，其他事务并不一定会访问对应的资源，相当于一种懒加载。

3、对于select for update 查询了其他索引中的数据，也不会对其他索引加锁（主键索引除外）。

4、如果lock in share mode查找走了覆盖索引，则不会对主键索引加锁。for update 无论走不走覆盖索引，都会对主键索引加锁。

5、select for update 和select lock in share mode ，只能对本身索引和主键索引加锁，不会对其他索引加锁。因为对主键索引加锁就足够了，如果其他事务根据其他索引更改值的话，肯定会修改主键索引上的记录，这时会修改不成功。