本文重要引见InnoDB中的行锁相干观点,重点引见行锁的锁定局限:
什么样的SQL语句会加锁?
加什么样的锁?
加锁语句会锁定哪些行?
背景学问
上面我们简朴的引见了InnoDB的行级锁,为了明白背面的考证部份,须要补充一下背景学问。假如对响应学问异常相识,能够直接跳转到考证部份内容。
1. InnoDB锁的范例
InnoDB引擎运用了七种范例的锁,他们分别是:
同享排他锁(Shared and Exclusive Locks)
意向锁(Intention Locks)
纪录锁(Record Locks)
间隙锁(Gap Locks)
Next-Key Locks
插进去企图锁(Insert Intention Locks)
自增锁(AUTO-INC Locks)
本文重要触及Shared and Exclusive Locks,Record Locks,Gap Locks,Next-Key Locks这几种锁,其他范例锁假如人人感兴趣能够本身深切相识,在此不在详述。
1.1 Shared and Exclusive Locks
同享锁(S锁)和排他锁(X锁)的观点在很多编程言语中都涌现过。先来形貌一下这两种锁在MySQL中的影响效果:
假如一个事件对某一行数据加了S锁,另一个事件还能够对响应的行加S锁,然则不能对响应的行加X锁。
假如一个事件对某一行数据加了X锁,另一个事件既不能对响应的行加S锁也不能加X锁。
用一张典范的矩阵表格继承申明同享锁和排他锁的互斥关联:
-- | S | X |
---|---|---|
S | 0 | 1 |
X | 1 | 1 |
图中S示意同享锁X示意独有锁,0示意锁兼容1示意锁争执,兼容不被壅塞,争执被壅塞。由表可知一旦一个事件加了排他锁,其他个事件加任何锁都须要守候。多个同享锁不会互相壅塞。
1.2 Record Locks、Gap Locks、Next-Key Locks
这三种范例的锁都形貌了锁定的局限,故放在一同申明。
以下定义摘自MySQL官方文档
纪录锁(Record Locks):纪录锁锁定索引中一条纪录。
间隙锁(Gap Locks):间隙锁要么锁住索引纪录中心的值,要么锁住第一个索引纪录前面的值或许末了一个索引纪录背面的值。
Next-Key Locks:Next-Key锁是索引纪录上的纪录锁和在索引纪录之前的间隙锁的组合。
定义中都提到了索引纪录(index record)。为何?行锁和索引有什么关联呢?实在,InnoDB是经由过程搜刮或许扫描表中索引来完成加锁操纵,InnoDB会为他碰到的每个索引数据加上同享锁或排他锁。所以我们能够称行级锁(row-level locks)为索引纪录锁(index-record locks),由于行级锁是增加到行对应的索引上的。
三种范例锁的锁定局限差别,且逐步扩展。我们来举一个例子来扼要申明种种锁的锁定局限,假定表t中索引列有3、5、8、9四个数字值,依据官方文档的肯定三种锁的锁定局限以下:
纪录锁的锁定局限是零丁的索引纪录,就是3、5、8、9这四行数据。
间隙锁的锁定为行中心隙,用鸠合示意为(-∞,3)、(3,5)、(5,8)、(8,9)、(9,+∞)。
Next-Key锁是有索引纪录锁加上索引纪录锁之前的间隙锁组合而成,用鸠合的体式格局示意为(-∞,3]、(3,5]、(5,8]、(8,9]、(9,+∞)。
末了关于间隙锁还须要补充三点:
间隙锁阻挠其他事件对间隙数据的并发插进去,如许可有有用的处理幻读题目(Phantom Problem)。正由于如此,并非一切事件断绝级别都运用间隙锁,MySQL InnoDB引擎只要在Repeatable Read(默许)断绝级别才运用间隙锁。
间隙锁的作用只是用来阻挠其他事件在间隙中插进去数据,他不会阻挠其他事件具有一样的的间隙锁。这就意味着,除了insert语句,许可其他SQL语句能够对一样的行加间隙锁而不会被壅塞。
关于唯一索引的加锁行动,间隙锁就会失效,此时只要纪录锁起作用。
2. 加锁语句
前面我们已引见了InnoDB的是在SQL语句的实行过程当中经由过程扫描索引纪录的体式格局来完成加锁行动的。那哪些些语句会加锁?加什么样的锁?接下来我们一一形貌:
select ... from语句:InnoDB引擎采纳多版本并发掌握(MVCC)的体式格局完成了非壅塞读,所以关于一般的select读语句,InnoDB并不会加锁【注1】。
select ... from lock in share mode语句:这条语句和一般select语句的区分就是背面加了lock in share mode,经由过程字面意义我们能够猜到这是一条加锁的读语句,而且锁范例为同享锁(读锁)。InnoDB会对搜刮的一切索引纪录加next-key锁,然则假如扫描的唯一索引的唯一行,next-key降级为索引纪录锁。
select ... from for update语句:和上面的语句一样,这条语句加的是排他锁(写锁)。InnoDB会对搜刮的一切索引纪录加next-key锁,然则假如扫描唯一索引的唯一行,next-key降级为索引纪录锁。
update ... where ...语句:。InnoDB会对搜刮的一切索引纪录加next-key锁,然则假如扫描唯一索引的唯一行,next-key降级为索引纪录锁。【注2】
delete ... where ...语句:。InnoDB会对搜刮的一切索引纪录加next-key锁,然则假如扫描唯一索引的唯一行,next-key降级为索引纪录锁。
insert语句:InnoDB只会在将要插进去的那一行上设置一个排他的索引纪录锁。
末了补充两点:
假如一个查询运用了辅佐索引而且在索引纪录加上了排他锁,InnoDB会在相对应的聚合索引纪录上加锁。
假如你的SQL语句没法运用索引,如许MySQL必需扫描悉数表以处理该语句,致使的效果就是表的每一行都会被锁定,而且阻挠其他用户对该表的一切插进去。
SQL语句考证
闲言少叙,接下来我们进入本文重点SQL语句考证部份。
1.测试环境
数据库:MySQL 5.6.35
事件断绝级别:Repeatable read
数据库接见终端:mysql client
2.考证场景
2.1 场景一
建表:
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插进去数据:
INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a'); INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c'); INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e'); INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g'); INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');
起首我们实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name='e' for update; | -- |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`) VALUES (10, #{name}); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
a | 不壅塞 |
b | 不壅塞 |
d | 壅塞 |
e | 壅塞 |
f | 壅塞 |
h | 不壅塞 |
i | 不壅塞 |
视察效果,我们发明SQL语句SELECT * FROM user where name='e' for update
一共锁住索引name中三行纪录,(c,e]区间应当是next-key锁而(e,h)区间是索引纪录e背面的间隙。
接下来我们肯定next-key锁中哪部份是索引纪录锁哪部份是间隙锁。
实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name='e' for update; | -- |
3 | -- | SELECT * FROM user where name=#{name} for update; |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
d | 不壅塞 |
e | 壅塞 |
f | 不壅塞 |
由于间隙锁只会阻挠insert语句,所以一样的索引数据,insert
语句壅塞而select for update
语句不壅塞的就是间隙锁,假如两条语句都壅塞就是索引纪录锁。
视察实行效果可知,d和f为间隙锁,e为索引纪录锁。
结论:经由过程两条SQL,我们肯定了关于辅佐索引name在查询前提为 where name='e'
时的加锁局限为(c,e],(e,g),个中:
对SQL语句扫描的索引纪录e加索引纪录锁[e]。
锁定了e前面的间隙,c到e之间的数据(c,e)加了间隙锁
前两个构成了next-key锁(c,e]。
值得注重的是还锁定了e背面的间隙(e,g)。
说的这里仔细的读者能够已发明我们的测试数据中没有间隙的边境数据c和g。接下来我们就对间隙边境值举行测试。
实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name='e' for update; | -- |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`) VALUES (#{id}, #{name}); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中id,name的值,视察效果:
id的值 | name=c | 实行效果 | id的值 | name=g | 实行效果 |
---|---|---|---|---|---|
-- | -- | -- | -3 | g | 组塞 |
-- | -- | -- | -2 | g | 壅塞 |
-1 | c | 不壅塞 | -1 | g | 壅塞 |
1 | c | 不壅塞 | 1 | g | 不壅塞 |
2 | c | 不壅塞 | 2 | g | 壅塞 |
3 | c | 不壅塞 | 3 | g | 不壅塞 |
4 | c | 壅塞 | 4 | g | 壅塞 |
5 | c | 壅塞 | 5 | g | 壅塞 |
6 | c | 壅塞 | 6 | g | 壅塞 |
7 | c | 不壅塞 | 7 | g | 不壅塞 |
8 | c | 壅塞 | 8 | g | 不壅塞 |
9 | c | 不壅塞 | 9 | g | 不壅塞 |
10 | c | 壅塞 | 10 | g | 不壅塞 |
11 | c | 壅塞 | - | - | - |
12 | c | 壅塞 | - | - | - |
经由过程视察以上实行效果,我们发明,name即是c和e时insert
语句的效果跟着id值得差别一会儿锁定,一会儿不锁定。那一定是id列加了锁才会形成如许的效果。
假如先不看id=5
这一行数据的效果,我们发明一个规律:
当
name=c
时,name=c
对应的id=3
的id聚合索引数据纪录以后的间隙(3,5),(5,7),(7,9),(9,∞)都被加上了锁。当
name=e
时,name=e
对应的id=7
的id聚合索引数据纪录之前的间隙(5,7),(3,5),(1,3),(-∞,1)都被加上了锁。我们可用
select * from user where id = x for update;
语句揣摸出以上间隙上加的锁都为间隙锁。
接下来我们解释一下id=5
的锁定状况
实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name='e' for update; | -- |
3 | -- | SELECT * FROM user where id=#{id} for update; |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中id的值,视察效果:
id的值 | 实行效果 |
---|---|
3 | 不壅塞 |
4 | 不壅塞 |
5 | 壅塞 |
6 | 不壅塞 |
7 | 不壅塞 |
经由过程视察实行效果可知,id=5
的聚合索引纪录上增加了索引纪录锁。依据MySQL官方文档形貌,InnoDB引擎在对辅佐索引加锁的时刻,也会对辅佐索引所在行所对应的聚合索引(主键)加锁。而主键是唯一索引,在对唯一索引加锁时,间隙锁失效,只运用索引纪录锁。所以SELECT * FROM user where name='e' for update;
不仅对辅佐索引name=e
列加上了next-key锁,还对对应的聚合索引id=5
列加上了索引纪录锁。
终究结论:
关于SELECT * FROM user where name='e' for update;
一共有三种锁定行动:
对SQL语句扫描过的辅佐索引纪录行加上next-key锁(注重也锁住纪录行以后的间隙)。
对辅佐索引对应的聚合索引加上索引纪录锁。
当辅佐索引为间隙锁“最小”和“最大”值时,对聚合索引响应的行加间隙锁。“最小”锁定对应聚合索引以后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。
上面我们将对辅佐索引加锁的状况引见完了,接下来我们测试一下对聚合索引和唯一索引加锁。
2.2 场景二
建表:
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注重与场景一表user差别的是name列为唯一索引。
插进去数据:
INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a'); INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c'); INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e'); INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g'); INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');
起首我们实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name='e' for update; | |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`) VALUES (10, #{name}); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
a | 不壅塞 |
b | 不壅塞 |
c | 不壅塞 |
d | 不壅塞 |
e | 壅塞 |
f | 不壅塞 |
g | 不壅塞 |
h | 不壅塞 |
i | 不壅塞 |
由测试效果可知,只要name='e'
这行数据被锁定。
经由过程SQL语句我们考证了,关于唯一索引列加锁,间隙锁失效,
2.3 场景三
场景一和场景二都是在查询前提即是的状况下做出的局限揣摸,如今我们尝试一下其他查询前提,看看结论是不是一致。
借用场景一的表和数据。
建表:
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插进去数据:
INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a'); INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c'); INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e'); INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g'); INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');
实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name}); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
a | 壅塞 |
b | 壅塞 |
c | 壅塞 |
d | 壅塞 |
e | 壅塞 |
f | 壅塞 |
g | 壅塞 |
h | 壅塞 |
i | 壅塞 |
这个效果是不是是和你设想的不太一样,这个效果表明where name>'e'
这个查询前提并非锁住'e'
列以后的数据,而锁住了一切name
列中一切数据和间隙。这是为何呢?
我们实行以下的SQL语句实行计划:
explain select * from user where name>'e' for update;
实行效果:
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+ | 1 | SIMPLE | user | index | index_name | index_name | 26 | NULL | 5 | Using where; Using index | +----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
假如你的效果与上面差别先实行一下OPTIMIZE TABLE user;
再实行以上语句。
经由过程视察SQL语句的实行计划我们发明,语句运用了name
列索引,且rows
参数即是5,user表中一共也只要5行数据。SQL语句的实行过程当中一共扫描了name
索引纪录5行数据且对这5行数据都加上了next-key锁,相符我们上面的实行效果。
接下来我们再制作一组数据。
建表:
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插进去数据:
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');
这张表和前表的区分是多了一列非索引列age
。
我们再实行一下一样的SQL语句实行计划:
explain select * from user where name>'e' for update;
实行效果:
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+ | 1 | SIMPLE | user | range | index_name | index_name | 26 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
是不是是和第一次实行效果差别了,rows
参数即是2,申明扫描了两行纪录,连系SQL语句select * from user where name>'e' for update;
实行后返回效果我们揣摸这两行纪录应当为g和i。
由于select * from user where name>'e' for update;
语句扫描了两行索引纪录分别是g和i,所以我们将g和i的锁定局限叠就能够获得where name>'e'
的锁定局限:
索引纪录g在
name
列锁定局限为(e,g],(g,i)。索引纪录i的在name
列锁定局限为(g,i],(i,+∞)。二者叠加后锁定局限为(e,g],(g,i],(i,+∞)。个中g,i为索引纪录锁。g和i对应
id
列中的7和9加索引纪录锁。当
name
列的值为锁定局限上边境e时,还会在e所对应的id
列值为5以后的一切值之间加上间隙锁,局限为(5,7),(7,9),(9,+∞)。下边境为+∞无需斟酌。
接下来我们一一测试:
起首测试考证了next-key锁局限,实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18'); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
a | 不壅塞 |
b | 不壅塞 |
c | 不壅塞 |
d | 不壅塞 |
f | 壅塞 |
g | 壅塞 |
h | 壅塞 |
i | 壅塞 |
j | 壅塞 |
k | 壅塞 |
下面考证next-key锁中哪部份是间隙锁,哪部份是索引纪录锁,实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | SELECT * FROM user where name=#{name} for update; |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
e | 不壅塞 |
f | 不壅塞 |
g | 壅塞 |
h | 不壅塞 |
i | 壅塞 |
j | 不壅塞 |
接下来考证对id
列加索引纪录锁,实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | SELECT * FROM user where id=#{id} for update; |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中id的值,视察效果:
id的值 | 实行效果 |
---|---|
5 | 不壅塞 |
6 | 不壅塞 |
7 | 壅塞 |
8 | 不壅塞 |
9 | 壅塞 |
10 | 不壅塞 |
末了我们考证name
列的值为边境数据e时,id
列间隙锁的局限,实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18'); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中id的值,视察效果:
id的值 | 实行效果 |
---|---|
-1 | 不壅塞 |
1 | 不壅塞 |
2 | 不壅塞 |
3 | 不壅塞 |
4 | 不壅塞 |
5 | 不壅塞 |
6 | 壅塞 |
7 | 壅塞 |
8 | 壅塞 |
9 | 壅塞 |
10 | 壅塞 |
11 | 壅塞 |
12 | 壅塞 |
注重7和9是索引纪录锁纪录锁。
视察上面的一切SQL语句实行效果,能够考证select * from user where name>'e' for update
的锁定局限为此语句扫描name
列索引纪录g和i的锁定局限的叠加组合。
2.4 场景四
我们经由过程场景三考证了一般索引的局限查询语句加锁局限,如今我们来考证一下唯一索引的局限查询状况下的加锁局限。有了场景三的铺垫我们直接跳过扫描悉数索引的状况,建立能够扫描局限纪录的表构造并插进去响应数据测试。
建表:
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(8) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插进去数据:
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19'); INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');
和场景三表唯一差别是name
列为唯一索引。
SQL语句select * from user where name>'e'
扫描name
列两条索引纪录g和i。假如须要只对g和i这两条纪录加上纪录锁没法防止幻读的发作,索引锁定局限应当照样两条数据next-key锁锁的组合:(e,g],(g,i],(i,+∞)。个中g,i为索引纪录锁。
我们经由过程SQL考证我们的结论,实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18'); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
a | 不壅塞 |
b | 不壅塞 |
c | 不壅塞 |
d | 不壅塞 |
f | 壅塞 |
g | 壅塞 |
h | 壅塞 |
i | 壅塞 |
j | 壅塞 |
k | 壅塞 |
下面考证next-key锁中哪部份是间隙锁,哪部份是索引纪录锁,实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | SELECT * FROM user where name=#{name} for update; |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中name的值,视察效果:
name的值 | 实行效果 |
---|---|
e | 不壅塞 |
f | 不壅塞 |
g | 壅塞 |
h | 不壅塞 |
i | 壅塞 |
j | 不壅塞 |
经由过程上面两条SQL语句的考证效果,我们证明了我们的g和i的锁定局限趋向为二者next-key叠加组合。
接下来我们考证一下对辅佐索引加锁后对聚合索引的锁转移,实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | SELECT * FROM user where id=#{id} for update; |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中id的值,视察效果:
id的值 | 实行效果 |
---|---|
5 | 不壅塞 |
6 | 不壅塞 |
7 | 壅塞 |
8 | 不壅塞 |
9 | 壅塞 |
10 | 不壅塞 |
由效果可知对辅佐索引name
中的g和i列对应的聚合索引id
列中的7和9加上了索引纪录锁。
到目前为止一切试验效果和场景三完整一样,这也很好明白,毕竟场景四和场景三只是辅佐索引name
的索引范例差别,一个是唯一索引,一个是一般索引。
末了考证意向,next-key锁边境数据e,看看结论时刻和场景三雷同。
实行SQL语句的模板:
步骤 | client 1 | client 2 |
---|---|---|
1 | begin; | -- |
2 | SELECT * FROM user where name>'e' for update; | -- |
3 | -- | begin; |
4 | -- | INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18'); |
5 | rollback; | -- |
6 | -- | rollback; |
替代步骤5中id的值,视察效果:
id的值 | 实行效果 |
---|---|
-1 | 不壅塞 |
1 | 不壅塞 |
2 | 不壅塞 |
3 | 不壅塞 |
4 | 不壅塞 |
5 | 不壅塞 |
6 | 不壅塞 |
7 | 壅塞 |
8 | 不壅塞 |
9 | 壅塞 |
10 | 不壅塞 |
11 | 不壅塞 |
12 | 不壅塞 |
注重7和9是索引纪录锁纪录锁。
经由过程效果可知,当name
列为索引纪录上边境e时,并没有对id有加锁行动,这点与场景三差别。
关于唯一索引的局限查询和一般索引的局限查询相似,唯一差别的是当辅佐索引即是高低局限的边境值是不会对主键加上间隙锁。
唯一索引局限查询加锁局限:
关于扫描的辅佐索引纪录的锁定局限就是多个索引纪录next-key局限的叠加组合。
关于聚合索引(主键)的锁定局限,会对多个辅佐索引对应的聚合索引列加索引纪录锁。
结论
InnoDB引擎会对他扫描过的索引纪录加上响应的锁,经由过程“场景一”我们已明白了扫描一条一般索引纪录的锁定局限,经由过程“场景三”我们能够揣摸恣意多个扫描一般索引索引纪录的锁定局限。经由过程“场景二”我们肯定了扫描一条唯一索引纪录(或主键)的锁定局限。经由过程“场景四”我们能够揣摸恣意多个扫描索唯一引纪录(或主键)的锁定局限。在现实的运用能够天真运用,揣摸两条SQL语句是不是互相锁定。这里还须要注重的是关于索引的查询前提,不能想当然的明白,他每每不是我们明白的模样,须要连系实行计划揣摸索引终究扫描的纪录数,不然会对加锁局限明白发生误差。
备注
注1:在事件断绝级别为SERIALIZABLE时,一般的select语句也会对语句实行过程当中扫描过的索引加上next-key锁。假如语句扫描的是唯一索引,那就将next-key锁降级为索引纪录锁了。
注2:当更新语句修正聚合索引(主键)纪录时,会对受影响的辅佐索引实行隐性的加锁操纵。当插进去新的辅佐索引纪录之前实行反复搜检扫描时和当插进去新的辅佐索引纪录时,更新操纵还对受影响的辅佐索引纪录增加同享锁。
相干引荐:
mysql实行sql文件报错Error: Unknown storage engine‘InnoDB怎样处理
MySQL启动时InnoDB引擎被禁用了怎么办
MySQL存储引擎MyISAM和InnoDB之间的比较
以上就是MySQL数据库InnoDB引擎行级锁锁定局限详解的细致内容,更多请关注ki4网别的相干文章!