草庐IT

浅谈事务隔离级别、MVCC及相关特性

李努力ly 2023-03-28 原文
文采不是太好，应该会有地方表达不清楚，烦请指正。
 
需要事先准备测试表：
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入数据：
insert into test values(1,'张一',20),(3,'王三',10),(4,'李四',34);
 
一、ACID特性
说起事务我们肯定会先想到事务的ACID四大特性：Atomicity、Consistency、Isolation、Durability
A（Atomicity）：原子性，对事务的状态要么全部提交，要么全部回滚，没有中间状态；
C（Consistency）：一致性，事务开始前和结束后，数据库的完整性没有被破坏；
I（Isolation）：隔离性，允许多个并发事务同时对数据库操作；
D（Durability）：持久性，事务提交后，对数据的修改就是永久的。
今天主要介绍的就是I（隔离性）。
 
二、MySQL隔离级别
在说隔离级别之前先简单介绍一下读取操作的几种方式：
①普通读取，也就是普通的select查询操作；
②锁定读取，也就是select ... lock in share mode; select ... for update; update等；
注意，该篇文章只针对于普通的读取，不涉及锁定读操作，因为锁定读会涉及到锁的相关信息，本文先不做过多的解释与验证。
mysql中隔离级别有如下几类：
READ UNCOMMITTED(RU)、READ COMMITTED(RC)、REPEATABLE READ(RR)、SERIALIZABLE
其实我们根据单词的意思就能知道这4种隔离级别的表面的含义了：RU读未提交、RC读已提交、RR可重复读、SERIALIZABLE串行化。每个隔离级别都有自己的特点，可根据实际业务需求情况来进行选择，5.7版本默认的是RR隔离级别。
为什么叫隔离级别呢？
根据个人的理解，主要是对不同事务之间的相互影响程度所采取的一种措施，根据级别的由低到高（RU-->RC-->RR-->SERIALIZABLE），所产生的影响程度不断提高（也就是并发度越来越低，效率越来越低），可以说每种隔离级别都有自己的特点，在介绍隔离级别的特点的时候，我们先来理解几个名词的概念：
①脏读：读到了其他未提交事务的数据，但是这个事务有可能提交也有可能回滚，如果回滚的话是不会存储到数据库中的，所以也可以说是读到了可能不存在的数据。
②不可重复读：同一个事务读取同一个数据，侧重的是修改，在事务开始的时候读到的数据和事务结束之前任意时刻读到的数据不一致。
③幻读：其实在幻读和不可重复读之间有一些不太好理解，不可重复读侧重的是两次读取操作的结果不同（该事务读取到了其他事务修改后并提交的数据）。而幻读更侧重的是插入，就是读取到了之前没有读到过的数据，比如A事务先读取某个范围内的数据，此时B事务在该范围内插入数据并提交，而A事务再次读取该范围内的数据的时候发现和第一次读取的不一致了，发现数据多了，读到了之前没有读到的数据。
在知道了上面几个概念之后，我们来分别测试一下在不同的隔离级别下，都有哪些特点。
 
1、RU隔离级别（读未提交）：
读未提交意思就是说，一个事务在没有提交的时候，它所做的修改就会被其他事务看到。该隔离级别下会产生脏读、幻读和不可重复读。通过开启不同的事务来进行测试：
首先修改隔离级别为RU：set global transaction isolation level read uncommitted;
①脏读：
Session A
Session B
begin;
begin;
select * from test;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   20 |
|  3 | 王三   |   10 |
|  4 | 李四   |   34 |
+----+--------+------+
 
 
 
insert into test values(2,'张二',22);
select * from test;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   20 |
|  2 | 张二   |   22 |
|  3 | 王三   |   10 |
|  4 | 李四   |   34 |
+----+--------+------+
 
 
rollback;
select * from test;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   20 |
|  3 | 王三   |   10 |
|  4 | 李四   |   34 |
+----+--------+------+
 
②不可重复读：
Session A
Session B
begin;
begin;
select * from test where id=1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   20 |
+----+--------+------+
 
 
 
update test set age=25 where id=1;
 
commit;
select * from test where id=1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   25 |
+----+--------+------+
 
③幻读：
Session A
Session B
begin;
begin;
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
+----+--------+------+
1 row in set (0.01 sec)
insert into test values(6,'王六',66);
 
commit;
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
|  6 | 王六   |   66 |
+----+--------+------+
 
2、RC隔离级别（读已提交）：
读已提交，意思就是一个事务提交之后，其他的事务才能看到它所做的变更。该隔离级别下不会产生脏读，但是会产生幻读和不可重复读。通过开启不同的事务来进行测试：
首先修改隔离级别为RC：set global transaction isolation level read committed;
①脏读：
Session A
Session B
begin;
begin;
select * from test;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   20 |
|  3 | 王三   |   10 |
|  4 | 李四   |   34 |
+----+--------+------+
 
 
 
insert into test values(2,'张二',22);
select * from test;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   20 |
|  3 | 王三   |   10 |
|  4 | 李四   |   34 |
+----+--------+------+
 
可以看到在session B中的事务未提交的时候，它所做的更改在session A中的事务始终是不可见的，所以不存在脏读。
②不可重复读：
Session A
Session B
begin;
begin;
select * from test where id=1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   20 |
+----+--------+------+
 
 
update test set age=25 where id=1;
 
commit;
select * from test where id=1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张一   |   25 |
+----+--------+------+
 
③幻读：
Session A
Session B
begin;
begin;
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
+----+--------+------+
1 row in set (0.01 sec)
insert into test values(6,'王六',66);
 
commit;
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
|  6 | 王六   |   66 |
+----+--------+------+
 
3、RR隔离级别（可重复读）：
可重复读，意思就是在事务开始和结束期间，看到的数据是一致的，该事务在期间修改的数据，在事务未结束之前对其他事务也是不可见的。该隔离级别下不会产生脏读和不可重复读，但是可能会发生幻读，通过开启不同的事务来进行测试，这里只测试幻读的情况：
首先修改隔离级别为RR：set global transaction isolation level repeatable read;
①不会产生幻读的情况：
Session A
Session B
begin;
begin;
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
+----+--------+------+
1 row in set (0.01 sec)
insert into test values(6,'王六',66);
 
commit;
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
+----+--------+------+
 
可以看到在RR隔离级别下，只是在普通读取过程中不会产生幻读的情况，因为Session A中的事务在其他事务提交前后都是读取到的相同的记录，并没有读取到新增的记录。
②会产生幻读的情况：
Session A
Session B
begin;
begin;
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
+----+--------+------+
1 row in set (0.01 sec)
insert into test values(6,'王六',66);
 
commit;
update test set age=68 where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
select * from test where id>3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   34 |
|  6 | 王六   |   68 |
+----+--------+------+
2 rows in set (0.00 sec)
 
该普通读取产生了幻读，是因为在Session A中的事务执行了一次update操作，该update操作属于是当前读，其他事务已经提交过的sql，在该事务中是可以被当前读所读到的。但是在RR隔离级别下是可以加锁来解决幻读的。
 
4、SERIALIZABLE隔离级别（串行化）：
该隔离级别下，是通过读加读写，写加写锁来阻止脏读、幻读、不重复读的发生，这里就不做相关实验了，感兴趣的可以修改隔离级别来进行测试。
 
三、MVCC
上面的实验都是基于普通的select语句进行的，由于其他的当前读会涉及到比较繁琐的加锁过程，所以本文没有阐述。
普通的select语句在RC和RR隔离级别下属于快照读，这就是由MVCC所控制了，MVCC只在RC和RR隔离级别下存在。
1、undo链：
MVCC又称为多版本并发控制，就是在每行记录中会添加行的创建版本号和过期版本号：
①trx_id：该id就是当前事务的id，对记录进行操作的时候都会把当前事务id赋值给trx_id；
②roll_ptr：是回滚指针，指向undo的，当某条记录被修改后，该记录的旧版本会写入undo，此时该记录的roll_id会指向对应的undo。
比如我们此时在test表中插入一条记录，假设此时版本号为10：
insert into test values(6,'王六',66); //其实insert undo信息只是起到的回滚的作用，当事务提交后就会删除了。
6
'王六'
66
10
roll_ptr(指向对应的undo)
比如我们在两个事务中update一条记录，假设该记录开始版本号为15：
Session A
Session B
begin;  假设该事务id为20
 
update test set age=13 where id=3;
 
 
commit;
 
 
begin; 假设该事务id为40
 
update test set age=16 where id=3;
 
commit;
此时应该是这样的：
这样的话就形成了一个undo版本链儿。
注意上面的箭头并不是代表指向的某个字段记录，而是在undo中的整条记录。
2、read view：
我们通过上面的一些测试，知道了RC和RR隔离级别下都是其它事务提交之后才能读到，那没有提交的时候，其它事务是如何进行读取历史的版本的呢？这就产生了read view（读视图）。
read view中几个比较重要的部分：当前活跃的事务id列表、活跃事务id列表中最小活跃的事务id（记为min_id）、当前的事务id（记为creat_id）、下一个事务id号（max_id）。
这里要知道事务id是递增的，只有在事务真正修改记录的时候才会被分配。
那么在访问数据的时候就会有如下判断：
①被访问的记录的trx_id是否和creat_id一致，如果一致的话，说明是在当前事务中进行读取的，也就是访问的自己本身；
②如果被访问的记录的trx_id小于min_id，表明该记录修改已经在当前事务生成read view之前提交了，也是可以被访问到的；
③如果被访问的记录的trx_id大于max_id，代表该事务是在当前事务生成read view之后开启的，是不能访问的；
④如果被访问的记录的trx_id在min_id 和 max_id之间，那么就要判断一下该事务id是不是在活跃事务id列表中，如果存在的话，说明生成当前事务的read view的时候该事务还是活跃的，是不可见的。如果不存在的话，说明在生成当前事务的read view的时候该事务已经提交了是可见的。
3、RC、RR中的read view：
在RC和RR中生成read view是有些不同的，主要是生成的时间点不同。
1）RC隔离级别下是每次读取数据的时候都会生成一个read view
Session A
Session B
begin;  假设该事务id为20，未提交
begin; //假设该事务id为40，执行了一些操作但是未提交
update test set age=13 where id=3;
 
。。。。
update test set age=16 where id=3;
。。。。
此时对应undo链如下：
当Session C事务在RC隔离级别下，去读取id=3的这条记录，那么它读取出来的是trx_id为15的这条版本记录。
原因：
①在Session C事务进行读取的时候，首先生成read view，该read view中包含一个当前活跃的事务id列表也就是[20,40]，min_id是20，max_id是41，creat_id是0；
②Session C的事务只是读取，并没有修改，所以creat_id为0，最新的事务版本id是20，存在于当前活跃的链表中所以看不到该条记录；
③继续沿着undo链表往下走，trx_id还为20，还在活跃的链表中，所以也读不到；
④继续沿着undo链表往下走，trx_id为15，小于min_id 20所以可见。
继续测试，将SessionA中的事务进行提交，在Session B中修改该记录：
Session A
Session B
begin;  假设该事务id为20，未提交
begin; //假设该事务id为40，执行了一些操作但是未提交
update test set age=13 where id=3;
 
。。。。
update test set age=16 where id=3;
。。。。
commit;
update test set age=19 where id=3;
对应的undo链就是：
继续在Session C的事务中去读取id=3的这条记录，那么它读取出来的是trx_id为20的这条记录版本。
因为RC每次读取都会生成新的read view，此时的read view中当前活跃事务id列表也就是[40]，min_id是40，max_id是41，creat_id是0。该记录的最新事务版本id是40，在活跃列表中，所以是可以不可以读到的。继续往下走事务id是20不在活跃列表中，是可以读到的。
2）RR隔离级别下是在事务开始的时候都一个read view，在事务结束之前都会去读取该read view
还是以上面的例子为例：
Session A
Session B
begin;  假设该事务id为20，未提交
begin; //假设该事务id为40，执行了一些操作但是未提交
update test set age=13 where id=3;
 
。。。。
update test set age=16 where id=3;
。。。。
此时对应undo链如下：
当Session C事务在RR隔离级别下，去读取id=3的这条记录，那么它读取出来的是trx_id为15的这条版本记录。
原因（截止到现在RR隔离级别下和RC是一样的原理）：
①在Session C事务进行读取的时候，首先生成read view并且之后都会使用该read view，该read view中包含一个当前活跃的事务id列表也就是[20,40]，min_id是20，max_id是41，creat_id是0；
②Session C的事务只是读取，并没有修改，所以creat_id为0，最新的事务版本id是20，存在于当前活跃的链表中所以看不到该条记录；
③继续沿着undo链表往下走，trx_id还为20，还在活跃的链表中，所以也读不到；
④继续沿着undo链表往下走，trx_id为15，小于min_id 20所以可见。
继续测试，将SessionA中的事务进行提交，在Session B中修改该记录：
Session A
Session B
begin;  假设该事务id为20，未提交
begin; //假设该事务id为40，执行了一些操作但是未提交
update test set age=13 where id=3;
 
。。。。
update test set age=16 where id=3;
。。。。
commit;
update test set age=19 where id=3;
对应的undo链就是：
继续在Session C的事务中去读取id=3的这条记录，那么它读取出来的还是trx_id为15的这条记录版本。
因为RR还在使用的是事务开始时候的read view，此时的read view中当前活跃事务id列表也还是[20,40]，min_id是20，max_id是41，creat_id是0。该记录的最新事务版本id是40，在活跃列表中，所以是可以不可以读到的。继续往下走事务id是20还在活跃列表中，是不可以读到的，直到读到事务id是15的时候才可以读到。
级别MVCCspanquotfont-familyMySQL

有关浅谈事务隔离级别、MVCC及相关特性的更多相关文章

  1. ruby - 使用 RubyZip 生成 ZIP 文件时设置压缩级别 - 2

    我有一个Ruby程序，它使用rubyzip压缩XML文件的目录树。gem。我的问题是文件开始变得很重，我想提高压缩级别，因为压缩时间不是问题。我在rubyzipdocumentation中找不到一种为创建的ZIP文件指定压缩级别的方法。有人知道如何更改此设置吗？是否有另一个允许指定压缩级别的Ruby库？ 最佳答案 这是我通过查看ruby​​zip内部创建的代码。level=Zlib::BEST_COMPRESSIONZip::ZipOutputStream.open(zip_file)do|zip|Dir.glob("**/*")d

  2. ruby - 我可以使用 aws-sdk-ruby 在 AWS S3 上使用事务性文件删除/上传吗？ - 2

    我发现ActiveRecord::Base.transaction在复杂方法中非常有效。我想知道是否可以在如下事务中从AWSS3上传/删除文件:S3Object.transactiondo#writeintofiles#raiseanexceptionend引发异常后，每个操作都应在S3上回滚。S3Object这可能吗？？ 最佳答案 虽然S3API具有批量删除功能，但它不支持事务，因为每个删除操作都可以独立于其他操作成功/失败。该API不提供任何批量上传功能(通过PUT或POST)，因此每个上传操作都是通过一个独立的API调用完成的

  3. ruby-on-rails - 相关表上的范围为 "WHERE ... LIKE" - 2

    我正在尝试从Postgresql表(table1)中获取数据，该表由另一个相关表(property)的字段(table2)过滤。在纯SQL中，我会这样编写查询:SELECT*FROMtable1JOINtable2USING(table2_id)WHEREtable2.propertyLIKE'query%'这工作正常:scope:my_scope,->(query){includes(:table2).where("table2.property":query)}但我真正需要的是使用LIKE运算符进行过滤，而不是严格相等。然而，这是行不通的:scope:my_scope,->(que

  4. ruby - 分布式事务和队列，ruby，erlang，scala - 2

    我有一个涉及多台机器、消息队列和事务的问题。因此，例如用户点击网页，点击将消息发送到另一台机器，该机器将付款添加到用户的帐户。每秒可能有数千次点击。事务的所有方面都应该是容错的。我以前从未遇到过这样的事情，但一些阅读表明这是一个众所周知的问题。所以我的问题。我假设安全的方法是使用两阶段提交，但协议(protocol)是阻塞的，所以我不会获得所需的性能，我是否正确？我通常写Ruby，但似乎Redis之类的数据库和Rescue、RabbitMQ等消息队列系统对我的帮助不大——即使我实现某种两阶段提交，如果Redis崩溃，数据也会丢失，因为它本质上只是内存。所有这些让我开始关注erlang和

  5. ruby-on-rails - 在具有 ActiveRecord 条件的相关模型中按字段排序 - 2

    我正在尝试按Rails相关模型中的字段进行排序。我研究的所有解决方案都没有解决如果相关模型被另一个参数过滤？元素模型classItem相关模型:classPriority我正在使用where子句检索项目:@items=Item.where('company_id=?andapproved=?',@company.id,true).all我需要按相关表格中的“位置”列进行排序。问题在于，在优先级模型中，一个项目可能会被多家公司列出。因此，这些职位取决于他们拥有的company_id。当我显示项目时，它是针对一个公司的，按公司内的职位排序。完成此任务的正确方法是什么？感谢您的帮助。PS-我

  6. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  7. ruby-on-rails - faraday如何设置日志级别 - 2

    我最近将我的http客户端切换到faraday，一切都按预期工作。我有以下代码来创建连接:@connection=Faraday.new(:url=>base_url)do|faraday|faraday.useCustim::Middlewarefaraday.request:url_encoded#form-encodePOSTparamsfaraday.request:jsonfaraday.response:json,:content_type=>/\bjson$/faraday.response:loggerfaraday.adapterFaraday.default_ada

  8. ruby-on-rails - 在 rails 中提交后回滚事务 - 2

    保存成功后可以回滚吗？让我有一个带有属性名称、电子邮件等的用户模型。例如u=User.newu.name="test_name"u.email="test@email.com"u.save现在记录将成功保存在数据库中，之后我想回滚我的事务(不是销毁或删除)。有什么想法吗？ 最佳答案 您可以通过交易来做到这一点，请参阅http://markdaggett.com/blog/2011/12/01/transactions-in-rails/例子:User.transactiondoUser.create(:username=>'Nemu

  9. ruby - 使用指向 ruby​​ 可执行文件的符号链接(symbolic link)时查找相关库 - 2

    假设您有一个可执行文件foo.rb，其库bar.rb的布局如下:/bin/foo.rb/lib/bar.rb在foo.rb的header中放置以下要求以在bar.rb中引入功能:requireFile.dirname(__FILE__)+"../lib/bar.rb"只要对foo.rb的所有调用都是直接的，这就可以正常工作。如果你把$HOME/project和符号链接(symboliclink)foo.rb放入$HOME/usr/bin,然后__FILE__解析为$HOME/usr/bin/foo.rb,因此无法找到bar.rb关于foo.rb的目录名.我意识到像ruby​​gems这

  10. HarmonyOS原子化服务开发相关术语 - 2

    术语中文解释Ability原子化服务帮助用户完成任务的原子化服务，和用户的意图进行关联。Fulfillment服务履行通过图标，卡片，语音等形式呈现用户意图。开发者通过接口的方式，处理用户意图，返回内容。Intent意图用于表达用户想要达成的目标或完成的任务。HUAWEIAssistant智能助手“无微不智”的个人助手，通过不断的学习用户的使用习惯，不断的为用户提供贴心的精准的便捷的个性化服务。AISearch全局搜索用户可快速搜索关键词，与之匹配的原子化服务则会出现在搜索结果中。SmartService智慧服务用户订阅原子化服务，在到达特定触发条件（时间、地点、事件）后，卡片推送至用户智能助

随机推荐