[MySQL][Gap Lock][Next-Key Lock]浅析

1. 基本信息

[lslxdx@localhost ~]# mysql --version
/*
mysql  Ver 15.1 Distrib 10.1.21-MariaDB, for osx10.12 (x86_64) using readline 5.1
*/

MariaDB [test]> SELECT version();
/*
10.0.14-MariaDB-log
*/

MariaDB [test]> SHOW CREATE TABLE t1\G;
/*
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8
*/

MariaDB [test]> SELECT @@tx_isolation;
/*
REPEATABLE-READ
*/

MariaDB [test]> select * from t1;
/*
+----+------+
| id | name |
+----+------+
| 10 | 10   |
| 20 | 20   |
| 30 | 30   |
| 40 | 40   |
+----+------+
*/

2. 记录存在 且 主键精准匹配

Record Lock: 变量应该使用Next-Key Lock的, 但由于是”主键精准匹配”, 所以Gap Lock就没必要了 – “Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.”

session 1session 2
begin;/**/
select id from t1 where id = 20 for update;/**/
/**/begin;
/**/select id from t1 where id = 20 for update; /* block */
rollback;/**/
/**/rollback;

3. 记录不存在 且 主键精准匹配

Next-Key Lock: 锁gap(10, 20) + 已发现的Record. 由于没有发现Records, 所以实际上没有Record Lock, 只有Gap Lock. 另外, Gap Lock总是不冲突.

session 1session 2
begin;/**/
select id from t1 where id = 18 for update;/**/
/**/begin;
/**/select id from t1 where id = 18 for update;
/**/select id from t1 where id = 20 for update;
rollback;/**/
/**/rollback;

4. 主键范围匹配(<和>)

Next-Key Lock: 锁Gap(10, 30), 锁Record 20/30, 合起来就是锁区间(10, 30], 锁上(界)不锁下(界).

session 1session 2
begin;/**/
select id from t1 where id < 30 and id > 10 for update;/**/
/**/begin;
/**/select id from t1 where id = 1 for update;
/**/select id from t1 where id = 10 for update;
/**/select id from t1 where id = 15 for update;
/**/select id from t1 where id = 18 for update;
/**/select id from t1 where id = 20 for update; /* block */
/**/select id from t1 where id = 21 for update;
/**/select id from t1 where id = 30 for update;/* block */
/**/select id from t1 where id = 31 for update;
/**/select id from t1 where id = 40 for update;
rollback;/**/
/**/rollback;

结论

  1. 在RR(REPEATABLE-READ)隔离级别下, SELECT ... FOR UPDATE使用Next-Key Lock, 即Record Lock + Gap Lock.
  2. Next-Key Lock在不同的场景中会退化:
场景退化成的锁类型
使用unique index精确匹配(=), 且记录存在Record Lock
使用unique index精确匹配(=), 且记录不存在Gap Lock
使用unique index范围匹配(<和>)Record Lock + Gap Lock 且 锁上界不锁下界

注意

  1. 4. 主键范围匹配(<和>)中, 如果变为where id < 30 and id >= 10, 那么, 锁定的区间是[10, 30], 因为10被search到了!
  2. Innodb加锁规则
操作锁类型
SELECT … FROM快照读, 不加锁. 当然了, SERIALIZABLE隔离级别除外.
SELECT … FROM … LOCK IN SHARE MODE共享的Next-Key Lock
SELECT … FROM … FOR UPDATE排他的Next-Key Lock
UPDATE … WHERE …排他的Next-Key Lock
DELETE FROM … WHERE …排他的Next-Key Lock
INSERTRecord Lock

补充

  1. 为什么Next-Key Lock要锁上界不锁下界
    原因是默认主键的有序自增的特性,结合后面的例子说明
-- 有primary key
create table t(id int,name varchar(10),key idx_id(id),primary key(name))engine =innodb;
insert into t values(1,'a'),(3,'c'),(5,'e'),(8,'g'),(11,'j');  

-- session 1
begin;
delete from t where id=8;

-- session 2
begin;
insert into t(id,name) values(6,'f');
insert into t(id,name) values(5,'e1');
insert into t(id,name) values(8,'gg');
insert into t(id,name) values(10,'p');
insert into t(id,name) values(11,'iz'); -- fail
insert into t(id,name) values(5,'cz'); -- success
insert into t(id,name) values(11,'ja'); -- success
------------------------------------
------------------------------------
-- 无primary key
create table t_nop(id int,key idx_id(id))engine =innodb;
insert into t_nop values(1),(3),(5),(8),(11);  

-- session 1
begin;
delete from t_nop where id=8;

-- session 2
begin;
insert into t_nop(id) values(6);
insert into t_nop(id) values(5);
insert into t_nop(id) values(8);
insert into t_nop(id) values(10);
insert into t_nop(id) values(5); -- fail!!!
insert into t_nop(id) values(11); -- success!!!
  1. 如果在索引上(unique/non-unique index, primary key)搜索一个不存在的range(如select * from t1 where id > 23 and id < 24 for update), 都会产生Next-Key Lock, 这是因为Index First Key遍历B-树, 确定起始点, 而Index Last Key会从起始点开始遍历每一条索引记录:
# http://hedengcheng.com/?p=771#comment-5590

你这个问题真的很好,也指出了我文中的一点小问题。

按照原理来说,id>5 and id<7这个查询条件,在表中找不到满足条件的项,因此会对第一个不满足条件的项(id = 9)上加GAP锁,防止后续其他事务插入满足条件的记录。

而GAP锁与GAP锁是不冲突的,那么为什么两个同时执行id>5 and id<7查询的事务会冲突呢?

原因在于,MySQL Server并没有将id<7这个查询条件下降到InnoDB引擎层,因此InnoDB看到的查询,是id>5,正向扫描。读出的记录id=9,先加上next key锁(Lock X + GAP lock),然后返回给MySQL Server进行判断。
MySQL Server此时才会判断返回的记录是否满足id<7的查询条件。此处不满足,查询结束。

因此,id=9记录上,真正持有的锁是next key锁,而next key锁之间是相互冲突的,这也说明了为什么两个id>5 and id<7查询的事务会冲突的原因。
  1. 除range搜索之外, 还有一种加Next-Key Lock的情形: 找到满足条件的记录,但是记录无效(标识为删除的记录)
Unique查询,三种情况,对应三种加锁策略,总结如下:
a. 找到满足条件的记录,并且记录有效,则对记录加X锁,No Gap锁(lock_mode X locks rec but not gap);
b. 找到满足条件的记录,但是记录无效(标识为删除的记录),则对记录加next key锁(同时锁住记录本身,以及记录之前的Gap:lock_mode X);
c. 未找到满足条件的记录,则对第一个不满足条件的记录加Gap锁,保证没有满足条件的记录插入(locks gap before rec);

参考

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值