花了一周时间，总算把mysql的加锁搞清楚了，再也不怕间隙锁和next-key了

237 0 0

作者:良工说技术

接触mysql都知道在mysql中有很多锁，共享锁（S）、排他锁（X）、间隙锁（gap）、next-key，当然还有意向锁、表锁等。今天不讲别的，专门来看下innodb引擎下的锁是什么样子的。

现在有这样一条sql语句，你知道是什么锁吗？

update t set name='1' where id=10;

可能会说是锁住id=10的行锁吧，没错行锁是一定的，但锁的是id=10还是其他。。。这个就犯难了。

其实不然，要判断一条sql语句使用什么锁，不是凭猜测的，而是要有依据的。从下面几个方面入手，

1、数据库隔离级别；

2、数据库索引；

3、使用到的索引；

判断一条sql使用什么锁一定不能脱开上面的3条，否则说使用了什么锁都是毫无意义的，加什么样的锁取决于要实现什么目标，如果不是这个，那不加锁岂不是更好。先来看下事务及索引。

一、事务及索引

1.1、事务

对于innodb引擎来说是支持库事务的，事务有四大特性，分别是A（原子性）、C（一致性）、I（隔离性）、D（持久性）。

在隔离性中有四大隔离级别，分别是RU（读未提交）、RC（读已提交）、RR（可重复读）、Serirable（可串行化）。四种隔离级别会影响加锁的范围，同时加锁解决了相应隔离级别下出现的问题，比如幻读、不可重复读都可以通过加锁解决。

1.2、索引

innodb使用B+树作为索引结构，索引可分为聚集索引、非聚集索引。简单点聚集索引就是索引和数据是在一起的，而非聚集索引则是索引和数据是分开的，正好分别对应主键索引、辅助索引。

在innodb引擎中主键索引是聚集索引，在索引的叶子节点中存储的是整行数据；而在辅助索引节子点存储的主键值。

如果一条sql走的是辅助索引，那么要找到这条完整的数据，则必须再遍历主键索引B+树，读取主键索引的叶子节点获得到完整的整行数据。

innodb引擎的表最终都是通过一棵主键索引的B+树来存储数据的；辅助索引存储的仅是索引列。

搞清楚了数据库的隔离级别和索引后，再来看mysql中锁的使用情况。

有这样一张t_user表，其中id是主键，自增；u_code是唯一索引；u_name、u_address是一个辅助索引。

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `u_name` varchar(100) DEFAULT NULL,
  `u_code` varchar(100) DEFAULT NULL,
  `u_age` varchar(100) DEFAULT NULL,
  `u_address` varchar(100) DEFAULT NULL,
  `interest` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t_user_un` (`u_code`),
  KEY `index_name_address` (`u_name`,`u_address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

二、RU/RC隔离级别

看下在RU（读未提交）、RC（读已提交）两种隔离级别下的加锁情况。

2.1、select

2.1.1、主键索引

2.1.1.1、主键-等值

select * from t_user where id=1 for update;

对id=1的记录加X（排它）行锁；如该行不存在不加锁；

select * from t_user where id=1 in share mode;

对id=1的记录加S（共享）行锁；如该行不存在不加锁；

2.1.1.2、主键-范围

select * from t_user where id<=5 for update

数据中存在小于等于5的主键共有5条（1、2、3、4、5）数据，以此对这5条加X（排它）行锁，其余不加锁；

select * from t_user where id<=5 in share mode;

数据中存在小于等于5的主键共有5条（1、2、3、4、5）数据，以此对这5条加S（共享）行锁，其余不加锁；

2.1.2、唯一索引

2.1.2.1、等值

select * from t_user where u_code=001 for update;

对u_code=001的索引记录加X（排它）行锁，同时在对应的主键行上也要加X（排它）行锁，如该行不存在不加锁；

select * from t_user where u_code=001  in share mode;

对u_code=001的索引记录加S（共享）行锁，同时在对应的主键行上也要加S（共享）行锁。如该行不存在不加锁；

2.1.2.2、范围

select * from t_user where u_code<=005 for update

针对上面的语句，我们猜想会走唯一索引，但其实不是的，使用explain看下

可以看到走的是全表扫描，所以这里会给所有的主键行加X锁，同时判断是否满足条件，对不满足的会立即释放X锁。要想走唯一索引可以使用下面的语句，

explain select * from t_user 
force index(t_user_un)
where u_code <='009' for update

这时的加锁便是对满足条件的u_code索引记录加X（排它）行锁，同时对对应的主键记录加X（排它）行锁；其余不加锁；

is share mode 也是同样的道理，只不过加的是S锁，不再演示。

所有的更新都会表现在主键的B+树索引上，为了防止其他语句对数据进行修改，对主键行加锁便是最好的选择。

2.1.3、辅助索引

存在辅助索引(u_name,u_address)。

2.1.3.1、等值

select * from t_user where u_name='test3'  for update;

对u_name='test3'的索引记录加X（排它）行锁，同时在对应的主键行上也要加X（排它）行锁，如该行不存在不加锁；

select * from t_user where u_code=001  in share mode;

对u_code=001的索引记录加S（共享）行锁，同时在对应的主键行上也要加S（共享）行锁。如该行不存在不加锁；

2.1.3.2、范围

select * from t_user where u_code<=005 for update

对满足条件的u_code索引记录加X（排它）行锁，同时对对应的主键记录加X（排它）行锁；其余不加锁；

select * from t_user where u_code<=005 in share mode;

对满足条件的u_code索引记录加S（共享）行锁，同时对对应的主键记录加S（共享）行锁；其余不加锁；

2.2、update

更新sql的加锁情况和select的加锁情况类似，区别在于更新的时候是否有索引列。

更新索引不包含索引列

看下面这个sql

update t_user set u_address='guangzhou' where id=4;

在主键行加X（排它）锁。

在看下面这样一条，

update t_user set u_address='guangzhou' where u_code='001';

该sql会命中唯一索引，索引在唯一索引记录上会加X（排它）锁，同时在唯一索引对应的主键记录行也会加X（排它锁）。

更新包含索引列

看下面这个sql

update t_user set u_code='003' where id=4;

在主键行加X（排它）锁；另外由于u_code列是唯一索引，在u_code的索引上加X（排它）锁。

在看下面这样一条，

update t_user set u_name='test' where u_code='001';

该sql会命中唯一索引，索引在唯一索引记录上会加X（排它）锁，另外，u_name列命中辅助索引，在u_name的索引记录上加X（排它）锁，最好在唯一索引对应的主键记录行也会加X（排它）锁。

2.3、delete

delete语句的加锁情况和相应的select的语句是一样的，可根据情况具体分析；

RU/RC两种隔离级别下给sql加的都是行锁，不涉及范围的锁定。在RR下就不一样了，因为要解决幻读和不可重复读。

三、RR隔离级别

看下在RR（可重复读）隔离级别下的加锁情况。

3.1、select

3.1.1、主键索引

3.1.1.1、等值

select * from t_user where id=10 for update;

对id=1的记录加X（排它）行锁；

如该行不存在则会在这条不存在的记录间加gap（间隙）锁。看下面的数据

会在(7,12)间加gap锁，防止其他事务执行insert操作，防止幻读。

3.1.1.2、等值-share

select * from t_user where id=1 in share mode;

对id=1的记录加S（共享）行锁；如该行不存在则会在这条不存在的记录间加gap（间隙）锁，只不过这里是s gap。

3.1.1.3、范围-大于

数据是这样的，

select * from t_user where id>=15 for update

数据中存在大于等于15的数据，对15加X（排它）锁，对20，25，30，40加next-key，也就是(15,20]，(20,25]，(25,30]，(30,40]，(40,无穷)。

如果条件是‘>15’且15存在，则不会对15加X（排它）锁，其他的和上面一样。

如果15不存在，则加锁情况是(13,15]，(15,20]，(20,25]，(25,30]，(30,40]，(40,无穷)。

在范围（大于）条件下加了间隙锁（gap），目的是阻止insert插入语句，解决幻读问题。

3.1.1.4、范围-小于

select * from t_user where id<=5 for update;

数据中存在小于等于5的主键数据，会对(1,4]，(4,5]，(5,6]加next-key。

如果不存在id=5的数据，则会找到离5最近的，加gap锁。

3.1.2、唯一索引

3.1.2.1、等值

select * from t_user where u_code=001 for update;

对u_code=001的索引记录加X（排它）行锁，同时在对应的主键行上也要加X（排它）行锁。如该行不存在不加锁；

select * from t_user where u_code=001  in share mode;

对u_code=001的索引记录加S（共享）行锁，同时在对应的主键行上也要加S（共享）行锁。如该行不存在不加锁；

3.1.2.2、范围

select * from t_user where u_code<=005 for update

针对上面的语句，我们猜想会走唯一索引，但其实不是的，使用explain看下

可以看到走的是全表扫描，所以这里会给所有的主键行加next-key锁，也就是会锁住全部数据范围。

要想走唯一索引可以使用下面的语句，

explain select * from t_user 
force index(t_user_un)
where u_code <='009' for update

这时的加锁便是对满足条件的u_code索引记录加next-key锁，同时对对应的主键记录加X（排它）行锁；其余不加锁；

is share mode 也是同样的道理，只不过加的是S锁，不再演示。

3.1.3、辅助索引

下面的sql均会命中辅助索引(u_name,u_address)。

3.1.3.1、等值-存在

select * from t_user where u_name='3'  for update;

对u_name='test3'的索引记录加next-key锁，同时在对应的主键行上也要加X（排它）行锁，加排它锁的目的是方式update/delete；在下一个记录test1上加gap锁，加gap的目的是防止insert，综上该条语句会锁定(11,test1)范围，也就是在执行insert的时候u_name不能再(11,test1)间。

主要是u_name不是唯一索引，为了防止幻读则必须在其在的范围内(11,test1)加gap，另外对u_name=3所在的主键行加X锁，防止的是对已存在的行update/delete；换句话说防止了insert这不就解决了幻读，防止了update/delete这不就解决了不可重复读。

3.1.3.2、等值-不存在

select * from t_user where u_name='4'  for update;

u_name=4不存在，会在(3,test1)间加gap锁，也就是在insert的时候，不能插入在(3,test1)间的值，比如'31'、'a'等。防止执行insert操作，其他操作均可；

3.1.3.3、范围-大于

select * from t_user where u_name>='3' for update

对满足条件的3 test1 test2 加X（排它）锁，对3 test1 test2命中的主键加X锁；同时在(11,3)、(3,test1)、(test1,test2)、（test2,无穷）加gap防止insert。这里有小伙伴会疑惑，为什么在(11,3)间有gap锁，这是因为u_name不唯一，在(11,3)间有可能插入u_name=3的数据。

3.1.3.4、范围-小于

select * from t_user where u_name<='3' for update;

对满足条件的1 11 3加X（排它）锁，对1 11 3命中的主键加X锁；同时在(无穷,1)、(1,11)、(11,3)、(3,test1)加gap防止insert。这里有小伙伴会疑惑，为什么在(3,test1)间有gap锁，这是因为u_name不唯一，在(3,test1)间有可能插入u_name=3的数据。

3.2、update

更新操作分为更新字段中是否包含了索引字段两种情况。

3.2.1、不包含索引字段

update t_user set u_address where id=2;
update t_user set u_address where u_name>='007';

分析上面的两个更新sql的加锁情况，首先看where条件，和select ... for update的情况是一样的；其次u_address不是索引列所以不会额外加锁。

3.2.2、包含索引字段

update t_user set u_name='000' where id=9;
update t_user set u_code='test001' where u_name='000';

上面的两条更新sql，依然是先where条件，和select ... for update的情况是一样的；其次由于u_name、u_code都会命中索引，所以会在索引上加锁，u_name是辅助索引，会加gap锁；u_code是唯一索引，会对test001加X（排它）锁；

3.3、delete

3.3.1、主键

3.3.1.1、等值

delete from t_user where id=12;

主键的等值delete，会锁定主键行，id=12不管是否存在都会锁定12这个值，其他对该行的insert/update都会是X锁。

3.3.1.2、范围

delete from t_user where id<=6;

会锁定小于等于6的整个区间，不允许insert/update/delete

3.3.2、唯一索引

3.3.2.1、等值

delete from t_user where u_code ='007'

主键的等值delete，会锁定主键行，id=12不管是否存在都会锁定12这个值，其他对该行的insert/update都会是X锁。

3.3.2.2、范围

delete from t_user where u_code <='007'

会锁定唯一索引记录小于等于007的区间，同时锁定相应的主键行；

3.3.3、辅助索引

3.3.3.1、等值

delete from t_user where u_name='test'

这里会命中辅助索引，所以和select...for update的加锁是一样的。同时还会对相应的唯一索引、主键索引加X锁。

3.3.3.2、范围

delete from t_user where u_name<='test'

和select ...for update的加锁是一样的。

四、Serializable隔离级别

serializable隔离级别下和RR级别下是一致的，另外serializable级别下的所有select都是徐云加锁的，加的是S锁。

五、总结

本文重点分析了innodb引擎下各种sql的加锁情况，了解sql的加锁情况可以更好的帮助我们去理解sql的执行流程，理解mysql中的很多概念，看加锁情况从“隔离级别”和“索引”两方面去综合分析。

推荐阅读

花了半天时间，使用spring-boot实现动态数据源，切换自如

spring-boot集成mybatis真的很简单吗？

仅仅是调用第三方接口那么简单吗？

参考：http://mysql.taobao.org/monthly/2018/05/04/

内容来源于网络如有侵权请私信删除

文章来源: 博客园

原文链接: https://www.cnblogs.com/teach/p/17488428.html

标签： C++ CPP C++语言开发

你还没有登录，请先登录或注册！

还没有人评论，欢迎说说您的想法！

花了一周时间，总算把mysql的加锁搞清楚了，再也不怕间隙锁和next-key了

花了一周时间，总算把mysql的加锁搞清楚了，再也不怕间隙锁和next-key了

一、事务及索引

1.1、事务

1.2、索引

二、RU/RC隔离级别

2.1、select

2.1.1、主键索引

2.1.1.1、主键-等值

2.1.1.2、主键-范围

2.1.2、唯一索引

2.1.2.1、等值

2.1.2.2、范围

2.1.3、辅助索引

2.1.3.1、等值

2.1.3.2、范围

2.2、update

更新索引不包含索引列

更新包含索引列

2.3、delete

三、RR隔离级别

3.1、select

3.1.1、主键索引

3.1.1.1、等值

3.1.1.2、等值-share

3.1.1.3、范围-大于

3.1.1.4、范围-小于

3.1.2、唯一索引

3.1.2.1、等值

3.1.2.2、范围

3.1.3、辅助索引

3.2、update

3.2.1、不包含索引字段

3.2.2、包含索引字段

3.3、delete

3.3.1、主键

3.3.1.1、等值

3.3.1.2、范围

3.3.2、唯一索引

3.3.2.1、等值

3.3.2.2、范围

3.3.3、辅助索引

3.3.3.1、等值

3.3.3.2、范围

四、Serializable隔离级别

五、总结

相关课程

热门标签

推荐文章