gpt4 book ai didi

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

转载 作者:我是一只小鸟 更新时间:2023-06-18 06:31:28 26 4
gpt4 key购买 nike

接触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.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;
                        
                      

2、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下就不一样了,因为要解决幻读和不可重复读.

3、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的加锁是一样的.

4、Serializable隔离级别

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

5、总结

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

推荐阅读 。

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

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

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

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

  。

最后此篇关于花了一周时间,总算把mysql的加锁搞清楚了,再也不怕间隙锁和next-key了的文章就讲到这里了,如果你想了解更多关于花了一周时间,总算把mysql的加锁搞清楚了,再也不怕间隙锁和next-key了的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com