在之前的博客中,我写了一系列的文章,比较系统的学习了 MySQL 的事务、隔离级别、加锁流程以及死锁,我自认为对常见 SQL 语句的加锁原理已经掌握的足够了,但看到热心网友在评论中提出的一个问题,我还是彻底被问蒙了。
他的问题是这样的:
加了插入意向锁后,插入数据之前,此时执行了 select...lock in share mode 语句(没有取到待插入的值),然后插入了数据,下一次再执行 select...lock in share mode(不会跟插入意向锁冲突),发现多了一条数据,于是又产生了幻读。会出现这种情况吗?
这个问题初看上去很简单,在 RR 隔离级别下,假设要插入的记录不存在,如果先执行 select...lock in share mode 语句,很显然会在记录间隙之间加上 GAP 锁,而 insert 语句首先会对记录加插入意向锁,插入意向锁和 GAP 锁冲突,所以不存在幻读;如果先执行 insert 语句后执行 select...lock in share mode 语句,由于 insert 语句在插入记录之后,会对记录加 X 锁,它会阻止 select...lock in share mode 对记录加 S 锁,所以也不存在幻读。两种情况如下所示:
先执行 insERT 后执行 SELECT:
先执行 SELECT 后执行 insERT:
但是我们仔细想一想就会发现哪里有点不对劲,我们知道 insert 语句会先在插入间隙上加上插入意向锁,然后开始写数据,写完数据之后再对记录加上 X 记录锁。
那么问题就来了,如果在 insert 语句加插入意向锁之后,写数据之前,执行了 select...lock in share mode 语句,这个时候 GAP 锁和插入意向锁是不冲突的,查询出来的记录数为 0,然后 insert 语句写数据,加 X 记录锁,因为记录锁和 GAP 锁也是不冲突的,所以 insert 成功插入了一条数据,这个时候如果事务提交,select...lock in share mode 语句再次执行查询出来的记录数就是 1,岂不是就出现了幻读?
最新面试题整理好了,点击Java面试库小程序在线刷题。
整个流程如下所示(我们把 insert 语句的执行分成两个阶段,insERT 1 加插入意向锁,还没写数据,insERT 2 写数据,加记录锁):
一、insERT 加锁的困惑
在得出上面的结论时,我也感到很惊讶。按理是不可能出现这种情况的,只可能是我对这两个语句的加锁过程还没有想明白。
于是我又去复习了一遍 MySQL 官方文档,Locks Set by Different SQL Statements in InnoDB 这篇文档对各个语句的加锁有详细的描述,其中对 insert 的加锁过程是这样说的(这应该是网络上介绍 MySQL 加锁机制被引用最多的文档,估计也是被误解最多的文档):
insERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.
这里讲到了 insert 会对插入的这条记录加排他记录锁,在加记录锁之前还会加一种 GAP 锁,叫做插入意向锁,如果出现唯一键冲突,还会加一个共享记录锁。这和我之前的理解是完全一样的,那么究竟是怎么回事呢?难道 MySQL 的 RR 真的会出现幻读现象?
在 Google 上搜索了很久,并没有找到 MySQL 幻读的问题,百思不得其解之际,遂决定从 MySQL 的源码中一探究竟。另外,MySQL 系列面试题和答案全部整理好了,微信搜索Java技术栈,在后台发送:面试,可以在线阅读。 二、编译 MySQL 源码
编译 MySQL 的源码非常简单,但是中间也有几个坑,如果能绕过这几个坑,在本地调试 MySQL 是一件很容易的事(当然能调试源码是一回事,能看懂源码又是另一回事了)。
我的环境是 Windows 10 x64,系统上安装了 Visual Studio 2012,如果你的开发环境和我不一样,编译步骤可能也会不同。
在开始之前,首先要从官网下载 MySQL 源码:
这里我选择的是 5.6.40 版本,操作系统下拉列表里选 Source Code,OS Version 选择 Windows(Architecture Independent),然后就可以下载打包好的 zip 源码了。
将源码解压缩到 D:\mysql-5.6.40 目录,在编译之前,还需要再安装几个必要软件:
CMake:CMake 本身并不是编译工具,它是通过编写一种平台无关的 CMakeList.txt 文件来定制编译流程的,然后再根据目标用户的平台进一步生成所需的本地化 Makefile 和工程文件,如 Unix 的 Makefile 或 Windows 的 Visual Studio 工程;
执行 select ... lock in share mode 语句,判断记录上是否存在活跃的事务,如果存在,则为 insert 事务创建一个排他记录锁,并将自己加入到锁等待队列;
所以不存在网友所说的幻读问题。那么事情到此结束了么?并没有。
细心的你会发现,执行 insert 语句时,从判断是否有锁冲突,到写数据,这两个操作之间还是有时间差的,如果在这之间执行 select ... lock in share mode 语句,由于此时记录还不存在,所以也不存在活跃事务,不会触发隐式锁转换,这条语句会返回 0 条记录,并加上 GAP 锁;而 insert 语句继续写数据,不加任何锁,在 insert 事务提交之后,select ... lock in share mode 就能查到 1 条记录,这岂不是还有幻读问题吗?
为了彻底搞清楚这中间的细节,我们在 lock_rec_insert_check_and_lock 检查完锁冲突之后下个断点,然后在另一个事务中执行 select ... lock in share mode,如果它能成功返回 0 条记录,加上 GAP 锁,说明就存在幻读。不过事实上,这条 SQL 语句执行的时候卡住了,并不会返回 0 条记录。从 show engine innodb status 的 TRANSACTIONS 里我们看不到任何行锁冲突的信息,但是我们从 RW-LATCH INFO 中却可以看出一些端倪:
-------------
RW-LATCH INFO
-------------
RW-LOCK: 000002C97F62FC70
Locked: thread 10304 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 879 S-LOCK
RW-LOCK: 000002C976A3B998
Locked: thread 10304 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256 S-LOCK
Locked: thread 10304 file d:\mysql-5.6.40\storage\innobase\include\btr0pcur.ic line 518 S-LOCK
Locked: thread 2820 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256 S-LOCK
Locked: thread 2820 file D:\mysql-5.6.40\storage\innobase\row\row0ins.cc line 2339 S-LOCK
RW-LOCK: 000002C976A3B8A8 Waiters for the lock exist
Locked: thread 2820 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256 X-LOCK
Total number of rw-locks 16434
OS WAIT ARRAY INFO: reservation count 10
--Thread 10304 has waited at btr0cur.cc line 256 for 26.00 seconds the semaphore:
S-lock on RW-latch at 000002C976A3B8A8 created in file buf0buf.cc line 1069
a writer (thread id 2820) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.cc line 256
Last time write locked in file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256
OS WAIT ARRAY INFO: signal count 8
Mutex spin waits 44, rounds 336, OS waits 7
RW-shared spins 3, rounds 90, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 7.64 mutex, 30.00 RW-shared, 0.00 RW-excl
这里列出了 3 个 RW-LOCK:000002C97F62FC70、000002C976A3B998、000002C976A3B8A8。其中可以看到最后一个 RW-LOCK 有其他线程在等待其释放(Waiters for the lock exist)。下面列出了所有等待该锁的线程,Thread 10304 has waited at btr0cur.cc line 256 for 26.00 seconds the semaphore,这里的 Thread 10304 就是我们正在执行 select 语句的线程,它卡在了 btr0cur.cc 的 256 行,我们查看 Thread 10304 的堆栈: