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 条评论
登录 后参与评论

相关文章

来自专栏蓝天

高性能高可用的分布式唯一ID服务——mooon-uniq-id

源码位置:https://github.com/eyjian/mooon/tree/master/application/uniq_id。

662
来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

2337
来自专栏木头编程 - moTzxx

后台 配置页面功能设计

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u011415782/article/de...

3544
来自专栏程序猿

SQL注入分析服务器类型

分析数据库服务器类型 一般来说,ACCESS与SQL-SERVER是最常用的数据库服务器,尽管它们都支持T-SQL标准,但还有不同之处,而且不同的数据库有不同的...

3046
来自专栏Spark学习技巧

phoenix二级索引

二级索引 二级索引是从主键访问数据的正交方式。Hbase中有一个按照字典排序的主键Rowkey作为单一的索引。不按照Rowkey去读取记录都要遍历整张表,然后按...

6019
来自专栏散尽浮华

Mysql慢查询操作梳理

Mysql慢查询解释 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_quer...

2276
来自专栏解Bug之路

MySql之自动同步表结构

在开发过程中,由于频繁的修改数据库的字段,导致rd和qa环境的数据库表经常不一致。 而由于这些修改数据库的操作可能由多个rd操作,很难一次性收集全。人手工去和...

541
来自专栏沃趣科技

RR与RC隔离级别下MySQL不同的加锁解锁方式

1)innodb_locks_unsafe_for_binlog是全局参数,影响所有session;但隔离级别可以是全局也可以是会话级别。

4135
来自专栏源哥的专栏

如何对表操作进行监控

我们在跟踪定位一些系统问题的时候,经常需要知道哪些地方对一个表进行了操作,查询代码很麻烦,其实有一个简单的方法,就是跟踪表, 如下语句,就可以给表t_table...

1032
来自专栏IT技术精选文摘

Mysql锁机制分析

6184

扫码关注云+社区