mysql 事务隔离级别详述

帅气的老牛

已于 2022-11-15 13:45:26 修改

阅读量278

点赞数

文章标签：间隙锁不可重复读事务隔离级别幻读

于 2022-09-14 20:31:07 首次发布

本文链接：https://blog.csdn.net/ppvqq/article/details/105396279

版权

从数据库层面出发，事务的隔离级别分为四种：读未提交（read uncommitted）,读已提交（read committed），可重复读（repeatable read），序列化（serializable）；mysql INNODB引擎对这四种事务隔离级别都提供了支持。各级别事务存在的事务并发问题如下:

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

为了好阐述，咱们先创建一张表：

CREATE TABLE `test` (
`id` int(11) NOT NULL,
`id_backup` int(11) NOT NULL,
`id_name` varchar(128) NOT NULL COMMENT ' ID名称',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_id_backup` (`id_backup`)
)

有两条数据：

id=1, id_backup=1,id_name='1';

id=5, id_backup=5,id_name='5';

根据每种隔离级别的英文名字，我们就知道都解决了哪些问题：

read-uncommitted：读取到其它事务未提交的内容。没准儿其它事务回滚了，所以读取到的数据有可能是错误的，即：脏读。

read-committed：读取的是其它事务提交的内容。都提交了，那肯定就不是错误数据(脏数据)了。但是存在不可重复读的问题：

事务A	事务B
begin;
select * from test where id=1; #id_backup=1
	begin; update test set id_backup=2 where id=1; commit;
select * from test where id=1; #id_backup=2
commit;

事务A中读取一条数据两次，但是结果不一样，即为：不可重复读。

repeatable-read：顾名思义，可重复读了，解决了不可重复读的问题：

事务A	事务B
begin;
select * from test where id=1; #id_backup=1
	begin; update test set id_backup=2 where id=1; commit;
select * from test where id=1; #id_backup=1
commit;

可以看到，即使事务B更改了值，且事提交了，但是事务A前后两次的读取值还是一样的。

但是还是没有解决幻读的问题（针对幻读，网上有太多的各种解释，并且百分之九十都是错误的，mysql官网也没有给出具体的例子。我先按照我觉得比较对的一种进行解释）：

事务A	事务B
begin;
select * from test where id_backup=2; #结果为空
	begin; insert into test value(2,2,'2'); commit;
select * from test where id_backup=2; #结果还是为空 insert into test value(2,2,'2');#这个时候报错了，相同值已经存在了

明明查不出相同内容的记录，但是插入的时候，却告知已经重复了（是不是感觉的莫名其妙，虚幻了），即为：幻读。虽然存在幻读的问题，但是在repeatable-read模式下通过间隙锁机制是可以解决幻读问题的。

serializable：这个好理解了，无论读写，都排好队啊！一个一个的执行，上述的并发问题就都解决了。

接下来，咱们看看innodb引擎都是怎么解决这些问题的。

首先引出一个概念：consistent read，以下是官网的解释：

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions.

innodb 利用MVCC实现一致性读。在某个时间点去读取数据时，会在这个时间点生成一份儿数据的快照，快照内容只包含这个时间点前提交的内容，不包含这个时间点以后的修改内容，无论是已提交的还是未提交的。

但是read-committed跟repeatable-read的处理逻辑是不一样的。

read-committed的处理逻辑是：

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot。无论在不在同一个事务当中，每次读取的时候，都会生成一个新的快照。

repeatable-read的处理逻辑是：

all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

在同一个事务当中，只在第一次读取的时候生成快照，后续无论再读取几次，用到的都是这个快照。

这就是为什么read-committed有不可重复读的问题，而repeatable-read解决了这个问题的原因。因为是基于快照的方式进行的读，不会产生阻塞，所以也叫Consistent Nonlocking Reads。

下面我们着重介绍一下幻读。幻读之前，我们先介绍一下mysql的锁机制，这里我们只关注三种哈：

Record Locks：

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

首先，msyql的锁都是加在索引行上的。如果一张表没有创建索引，innodb也会创建一个隐藏的集簇索引。重点哈：锁都是加在索引上的。那如果where条件里没有索引列，会产生什么现象呢？后续介绍。

Gap Locks：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

间隙锁，锁的是空间，被锁住后，其他事务是不能在这个空间再插入数据的。还有一个重点就是间隙锁在某些隔离级别是不起作用的，在某些隔离级别是起作用的。具体的就是read-committed是不起作用的，repeatable是起作用的。

Next-Key Locks：

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

next-key lock就是行锁加上这行之前的间隙锁的组成。

铺垫完成以后，我们再回头看，到底什么是幻读，为什么在read-committed下不能解决幻读，而在repeatable下可以解决幻读。

首选针对什么是幻读，幻读如何产生的，网上有大量的讲述文章，但是大部分都是错误的。我们先看一下mysql官网给出的定义：

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

在同一个事务里边儿，第一次读取的时候，跟第二次读取的时候，结果不一样。这不就成了不可重复读了吗！但是在repeatable级别下，读是基于MVCC的，是不允许存在这种情况的。实际也证明不存在这种情况：

事务A	事务B
begin;
select * from test where id_backup<6; #返回两条结果
	begin; insert into test value(2,2,'2'); commit;
select * from test where id_backup<6; #结果还是两条

所以说所谓的幻读，不应该是这个意思，官方的文档表述有问题，而且官方也没给出产生幻读的具体实例。网上做解析的文章很多，列举的例子根本不能复现。我们接着上例：

事务A	事务B
begin;
select * from test where id_backup<6; #返回两条结果
	begin; insert into test value(2,2,'2'); commit;
select * from test where id_backup<6; #结果还是两条 insert into test value(2,2,'2'); #结果报错了，已经存在啦

在事务B插入一条，我们在A事务执行相同的插入，结果报错了。从操作者的角度来讲：我去！查询没有，插入的时候却被告知已经存在了！迷茫不？幻觉不？幻读就出来了！

怎么解决？加next-key锁。增、删、改操作都是隐式加锁， select操作用for update显式加锁。加上next-key锁，不让别的事务在此区间插入新的数据，不就可以解决幻读了吗。

事务A	事务B
begin;	begin;
select * from test where id_backup<6 for update; #返回两条结果
	insert into test value(2,2,'2');#等待获取锁，此条不成功或 insert into test value(3,3,'3');#等待获取锁，此条可以插入成功
select * from test where id_backup<6 for update; #结果还是两条
insert into test value(2,2,'2'); #成功了
commit；	#已存在冲突报错或 #插入成功
	commit；

问题又来了，repeatable-read加for update可以解决幻读，但是在read-committed级别下也是可以用for update的，为啥就不能解决问题呢。问题就是上述所说的，间隙锁在read-committed级别是不起作用的！加for update就是在做扫描的时候，遇到匹配的，就全部加行锁！

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

事务A	事务B
begin;
select * from test where id_backup<5 for update; #符合条件的只有id=1，针对此行加行锁。
	begin; insert into test value(2,2,'2'); commit;#执行成功
commit；