MySQL中的事务隔离级别和锁

前言

我们都知道数据库有四个隔离级别,同时肯定也听过MySQL的RR的隔离级别下解决了幻读的问题,那么到底是怎么实现的呢?MySQL中的RR隔离级别下面到底有没有解决幻读问题呢?

两段锁

学过操作系统都知道,如果要为了避免死锁,我们可以选择一次性把所有的资源都上锁,然后进行操作,操作完成之后就释放锁。但是这个方法在数据库里面并不适用,因为你不知道你要用到哪些资源(当然极端点你可以把所有的资源全部上锁,但是这样并发量就太惨了),所以数据库用的是两段锁:

  • 加锁阶段:在这个阶段里,进行加锁操作。如果要读取数据,就加S锁;如果要进行写操作,就加X锁。如果加锁不成功,则事务进入等待状态(具体表现就是一条语句不返回任何值,卡在那里),直到加锁成功才继续执行。
  • 解锁阶段:当事务释放了一个封锁以后,事务进入解锁阶段,在该阶段只能进行解锁操作不能再进行加锁操作。

具体如果落实到语句中,就是insert加insert的锁,update加update的锁,delete加delete的锁,然后当你commit的时候就释放这些锁。

注意:两段锁是会发生死锁机制的,这个比如事务A想要修改第一条数据和第二条数据,它先给第一条数据加了X锁,然后另外一个事务B给第二条加了X锁,这样死锁就形成了。

隔离级别

数据库中老生常谈的问题了,一共有四个隔离级别。MySQL的默认级别是Repeatable read,不会发生脏读和不可重复读。至于幻读嘛,先留个悬念。

之前面试官问过我,说最低的那个级别,也就是read uncommitted,是怎么实现的。

实验数据

接下来的所有数据都是基于Docker版本的mysql5.7来做的。然后我设计的表格也非常简单(直接抄的美团):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| class_name | varchar(100) | NO | | <null> | |
| teacher_id | int(11) | NO | MUL | <null> | |
+------------+--------------+------+-----+---------+----------------+

// 具体的数据
+----+------------+------------+
| id | class_name | teacher_id |
+----+------------+------------+
| 1 | 初三一班 | 1 |
| 3 | 初二一班 | 2 |
| 4 | 初二二班 | 2 |
+----+------------+------------+

Read Uncommitted

实际中没有任何数据库会用这种隔离级别,稍微做个实验就明白了。

首先打开启事务A和事务B,然后A往里面插入一条数据,接下来,事务B直接把所有的数据进行了一下更新,这样A能够看到B做的修改,也就是发生了我们说的脏读。这个一点问题也没有。

有问题是接下来的这个:那么MySQL是怎么实现这个隔离级别的呢?来先看看各类技术博客里面是怎么回答的吧:

  • 美团技术团队:Read Uncommitted这种级别,数据库一般都不会用,而且任何操作都不会加锁,这里就不讨论了。
  • 知乎大佬:读最新的数据,不管这条记录是不是已提交。不会遍历版本链,少了查找可见的版本的步骤。这样可能会导致脏读。对写仍需要锁定,策略和读已提交类似,避免脏写。
  • segmentfault:一级封锁协议对应READ-UNCOMMITTED 隔离级别,本质是在事务A中修改完数据M后,立刻对这个数据M加上共享锁(S锁)[当事务A继续修改数据M的时候,先释放掉S锁,再修改数据,再加上S锁],根据S锁的特性,事务B可以读到事务A修改后的数据(无论事务A是否提交,因为是共享锁,随时随地都能查到数据A修改后的结果),事务B不能去修改数据M,直到事务A提交,释放掉S锁。

稍微总结一下就是:有人说读写都不加任何锁;有人说读写都加S锁,提交后释放;还有人说读加S锁,写加X锁的。那到底谁是对的呢?

我自己做的实验:开启两个事务A和B。首先A先select,发现里面有一条数据。然后B插入了一条数据。接下来A又查了一下,发现此时有两条数据(脏读),这时,如果A也要插入一条和之前B插入的主键相同的数据,那么就会阻塞,过一会会提示(1205, 'Lock wait timeout exceeded; try restarting transaction');如果A插入的数据是原先数据库里就有,那么不会阻塞,数据库直接提示主键重复;如果A插入的数据是它之前自己插入的,那么不会阻塞,数据库直接提示主键重复。

从上面那个实验,直接就把美团的说法给毙掉了,因为如果任何操作都不加锁,那为什么insert会阻塞呢?

那看来,应该是读的时候不加锁,写的时候加排它锁喽?那也不对啊,排它锁是需要在commit的时候释放的,而一旦事务B插入了数据,那么事务B是会对这条记录上锁的,那么别的事务是读不到的,怎么可能还会出现脏读呢?

最后我只能找到sqlserver的资料:

Tansactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. // 读其实不会加s锁
READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. // 写操作会上x锁,但是不会阻塞别的事务来读取
When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.
This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. // 这个隔离级别和不上锁且都执行select是等价的
This is the least restrictive of the isolation levels.

我个人的理解:在这个Read Uncommitted级别下面,select语句,是不会上共享锁的。但是你insert和update和delete都是会加x锁的。而一旦数据上了X锁,就不能再次上s锁和x锁(解释了为什么不能够同时insert同一个主键的行),但是由于select语句根本就不给你上锁,自然也不会冲突,所以自然能够读取。

Read Committed

首先我们设置好隔离级别,然后开始事务A和事务B。

  • 例子1:事务A给数据库插入了一条数据,并且暂时没有提交。此时事务B如果用select查看,会看不到A刚刚插入的数据,也就是脏读确确实实被解决掉了。而一旦事务A提交,那么事务B就能够在其中看到A插入的数据了。
  • 例子2:事务A给数据库插入了一条数据,并且暂时没有提交。之后事务B也给数据库插入了一条和A之前插入的主键相同(不一定非要主键,这里主要是为了说明)的数据,此时事务B会被阻塞住。从这里可以证明,写的时候,确实会加排他锁。

那问题又来了呀,如果事务在写的时候会加排它锁,那么另外一个事务确实读取不到数据,但是会发生阻塞,然而我们在实际中却没有看到阻塞。这又是怎么回事呢?那只能说明在这个隔离级别下面,你的读操作,它并没有进行任何的上锁,那这岂不是又跟上面的read uncommitted一样了么?那为什么不会读到脏的数据呢?

这里需要进行说明,mysql是根据你的索引来给对应的数据进行上锁的。如果你的where条件里面的那列没有索引怎么办?MySQL会粗暴的把所有的行都加上排它锁,这样你的并发就下去了。(当然虽然说是这么说,但是实际中是有优化的)

MVCC会使用一个版本链的东西,根据你当前的事务id,来判断当前事务应该可以读到哪些数据。

接下来为了下面的内容铺垫,在这里先演示一下幻读的情况:

首先开启事务A,先用select选择出指定的数据,然后你对数据进行修改。在事务B中你再插入一条相关的数据,发现并不会阻塞,然后提交。由于提交了,所以事务A是可以读取到相应的内容的。

Repeatable Read

这个级别其实和Read Committed是一样的,读用的是MVCC,而写用的也是排它锁。只不过是在MVCC上面有点差距而已。

这里着重需要讲述的就是幻读的解决,和上面的RC一样的过程,只不过此时如果你再插入一条相关的数据,你会发现你已经插入不了了。这是因为你的更新操作,它不仅锁住了更新的那一行,也顺带把间隙给锁住了。

Serializable

这个级别就简单多了,读数据要给数据加S锁,写数据要给数据加X锁。只有S锁和S锁是不互斥的,其它都会互斥。

总结

下面的表格总结了一波MySQL中各个隔离级别中的读和写,都是通过什么实现的:

隔离级别 读取 写入
Read Uncommitted 不加锁。 加排它锁
Read Committed 不加锁,使用MVCC 加排它锁
Repeatable Read 不加锁,使用MVCC 加排它锁(防止其他事务修改和删除),除此之外还加了间隙锁(防止其他事务插入)
Serializable 加共享锁 加排它锁

注意,这里Repeatable Read使用了间隙锁就是为了解决幻读。