MySQL加锁范围分析

场景:

最近,遇到了一个关于mysql 加锁的问题,将当时的情形简化如下,有一个index_test表,表结构如下所示:

mysql> CREATE TABLE `index_test` (
  `priv_id` int(11) NOT NULL DEFAULT '0',
  `index_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`priv_id`),
  KEY `index_id` (`index_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

priv_id为主键,index_id上建有非唯一索引。

初始情况表中有如下记录:

mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
|       1 |        1 |
|       3 |        3 |
|       5 |        5 |
|       7 |        6 |
|       9 |        9 |
+---------+----------+

问题(1):

开启两个client,首先,两个client分别按时间顺序执行如下命令:

time

client1

client2

step1

begin;

step2

select * from index_test where index_id=5 for update;

step3

begin;

step4

insert into index_test values(4,4);

结果client2中(4,4)插不进去,事务被阻塞,为何我只锁住了index_id=5的记录,插入index_id=4的数据也插不进去了,难道是client1锁全表了么?

寻找答案:

带着这样的疑问,先查阅了mysql官方文档关于MySQL锁的章节,InnoDB本身支持3种锁:

  • Record Locks:锁住表中的某一条记录
  • Gap Locks:锁住某个范围
  • Next-key Locks: 相当于Record Locks和Gap Locks的组合

那么这3种锁在哪些场合下使用呢?

这里不得不提令一个概念:隔离级别

事务在并发执行的过程中会导致的几个问题如下:

  • 脏读(Drity Read):当一个事务允许读取另外一个事务修改但未提交的数据时,就可能发生脏读(dirty reads)。
  • 不可重复读(Non-repeatable read):当一行数据获取两遍得到不同的结果表示发生了“不可重复读(non-repeatable read)
  • 幻读(Phantom Read):当两个完全相同的查询语句执行得到不同的结果集。

为了解决上面几个问题,ANSI/ISO SQL定义的标准隔离级别如下:

  1. 可序列化(Serializable):最高的隔离级别。
  2. 可重复读(Repeatable reads):会产生幻读
  3. 授权读(Read committed):会出现“不可重复读”
  4. 未授权读(Read uncommitted):会产生脏读

不同的DBMS默认隔离级别也不同,一般的DBMS系统,默认都会使用读提交(Read-Comitted,RC)作为默认隔离级别,如Oracle、SQL Server等,而InnoDB默认隔离级别为可重复读(Repeatable reads),但是InnoDB的隔离级别与标准的的隔离级别有少许不同,其在RR隔离级别下通过next-key locks防止幻读的产生。

mysql官方文档上介绍next-key locks一节有这么一段话:

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows。

即默认情况下,InnoDB默认隔离级别为RR,并且不开启binlog,这种情况下会加next-key locks,看了自己机器上的InnoDB隔离级别以及innodb_locks_unsafe_for_binlog果真是默认配置,说明上述问题1中的client1加的就是next-key locks而不是record locks。

那么next-key locks的范围如何确定呢?

让我们继续看官方文档

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where ( or ) denote exclusion of the interval endpoint and or denote inclusion of the endpoint:(negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)

按照这种方法,我们上例中,对于index_id上的索引,可能的next-key locks为

  • (negative infinity, 1]
  • (1, 3]
  • (3, 5]
  • (5, 6]
  • (6, 9]
  • (9, positive infinity)

因为问题1中,client1 上的锁为index_id=5 for update,所以在此,加的是next-key锁,并且锁的范围是(3,5],因为client2插入的数据(priv_id=4,index_id=4)其在(3,5]范围之内,因此插不进去,问题解决。

更进一步,问题(2):

解决了上述index_id=5时,(4,4)记录插不进去的问题之后,为了验证官方文档上所说的锁范围,我进一步做了如下实验:

mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
|       1 |        1 |
|       3 |        3 |
|       5 |        5 |
|       7 |        6 |
|       9 |        9 |
+---------+----------+

time

client1

client2

step1

begin;

step2

select * from index_test where index_id=6 for update;

step3

begin;

step4

insert into index_test values(6,5);

结果出现client2插不进去,事务阻塞的情况。为何我只锁住了index_id=6的记录,插入index_id=5的数据也插不进去了,即使是client1加了next-key锁,根据InnoDB关于next-key locks这一节所述,这里next-key locks的范围也应该是(5,6]才对,即index_id=6的记录插不进去,但是index_id=5的记录应该可以差的进去,为何(priv_id=6,index_id=5)这条记录没有插进去呢?难道官方文档把next-key locks的锁范围说错了么?这不科学啊。

然后在网上搜索相关的资料,看看别人有没有遇到过这样的问题,在一篇关于MySQL加锁处理分析的blog中得到了启示,按照blog中组合七:id非唯一索引+RR的理论,gap锁的范围不仅跟被锁定的键有关,还跟主键有关,同时在MySQL官方文档中也得到了验证

Each secondary index record also contains all the primary key fields defined for the clustered index key that are not in the secondary index.

即每一条辅助索引记录同样还包含主键。

按照该blog中的理论:上述client1中gap锁的范围应该如下图所示:

即当执行:

mysql> select * from index_test where index_id=6 for update;,

这条语句时,所有从(priv_id=5,index_id=5)到(priv_id=9,index_id=9)的区间都会被锁住,这也就解释了为什么把(priv_id=6,index_id=5)这条记录却插不进去的原因,因为本身(6,5)就在上述的gap锁区间范围之内。

显然(priv_id=4,index_id=5)是不在上述锁区间的,那么(priv_id=4,index_id=5)理论上来分析是可以插入进去的

验证:执行如下sql语句

time

client1

client2

step1

begin;

step2

select * from index_test where index_id=6 for update;

step3

begin;

step4

insert into index_test values(4,5);

结果:client2中(priv_id=4,index_id=5)记录顺利插入成功。

但是:为何gap锁的范围会是上述描述的那样呢,gap锁的范围如何确定呢?其实确定gap锁范围,我们只要把握一点:就是让后续不能插入满足条件的新纪录,然后按照这个点,去考虑哪些地方需要加gap锁。仍然拿下面这个例子来说:

mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
|       1 |        1 |
|       3 |        3 |
|       5 |        5 |
|       7 |        6 |
|       9 |        9 |
+---------+----------+
mysql> begin;
mysql> select * from index_test where index_id=6 for update;,

加了gap锁之后就是不能够再让其他index_id=6的记录被插入,因为InnoDB索引结构都是B+树,索引记录都是按顺序排序的,想要再插入一条index_id=6的记录,其必定只能是在(priv_id>5,index_id=5)~(priv_id<9,index_id=9)记录之间插入,由于为了防止幻读,为了不让满足index_id=6的记录再插入进去,因此那段区间就被加了锁,而不是别的区间。

##最后:

开启binlog(需要重启mysql),或者设置mysql隔离级别为RC:

mysql> set session transaction isolation level read committed;

再执行问题(2)中的例子:

mysql> select * from index_test;
+---------+----------+
| priv_id | index_id |
+---------+----------+
|       1 |        1 |
|       3 |        3 |
|       5 |        5 |
|       7 |        6 |
|       9 |        9 |
+---------+----------+

time

client1

client2

step1

begin;

step2

select * from index_test where index_id=6 for update;

step3

begin;

step4

insert into index_test values(6,5);

这一次,client2中的(6,5)顺利插入,这是因为client1仅仅对(7,6)这一条记录加了record lock,而不会对旁边区间加gap锁,这种情况下是会发生幻读现象的

因此,在我们使用mysql加锁过程中,也首先需要搞清楚,我们的隔离级别是什么,是否开启了binlog等等,然后才能正确分析加锁的范围。

参考文献:

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Web 开发

在SAE上开发遇到的问题~

添加一个escape_data()的函数,该函数已经会自动识别各种PHP配置环境~

1070
来自专栏pangguoming

分布式系统唯一ID生成方案汇总

系统唯一ID是我们在设计一个系统的时候常常会遇见的问题,也常常为这个问题而纠结。生成ID的方法有很多,适应不同的场景、需求以及性能要求。所以有些比较复杂的系统会...

4726
来自专栏性能与架构

如何让linux定时任务crontab按秒执行?

linux定时任务crontab最小执行时间单位为分钟 如果想以秒为单位执行,应该如何设置呢? 思路 正常情况是在crontab中直接定义要执行的任务,现在...

3078
来自专栏皮皮之路

【MySQL】通过Binary Log简单实现数据回滚(一)

35711
来自专栏容器云生态

运维工作常用的shell命令

#seq 1 100    //1到100排序 #seq 1 3 100   //1到100排序,间隔为3 shell中的变量设置规则: 1.变量与变量的内容用...

1906
来自专栏大内老A

.NET Core的文件系统[4]:由EmbeddedFileProvider构建的内嵌(资源)文件系统

一个物理文件可以直接作为资源内嵌到编译生成的程序集中。借助于EmbeddedFileProvider,我们可以统一的编程方式来读取内嵌于某个程序集中的资源文件,...

1848
来自专栏程序员的SOD蜜

求连续操作(登录)数量(次数)最大的记录(用户)

昨晚上老同事聚会,一个同事说道一个面试问题没有一个人做出来,就是求连续日期登录次数最大的用户,同事说借助 rownumber即可求解,由于是喝酒聊天,也没有说详...

2687
来自专栏安恒网络空间安全讲武堂

CTF逆向--.NET与Python篇

题目(来源:Jarvis-OJ): Classical Crackme Classical CrackMe2 FindKey Login Classical C...

3498
来自专栏皮皮之路

【MySQL】通过Binary Log简单实现数据回滚(一)

1706
来自专栏大闲人柴毛毛

Mysql性能优化

 1. 优化SQL   1)通过show status了解各种sql的执行频率         show status like 'Com_%' ...

33911

扫码关注云+社区