innodb引擎下事务隔离级别与幻读

最新推荐文章于 2021-02-17 06:12:45 发布
最新推荐文章于 2021-02-17 06:12:45 发布
阅读量189 收藏
点赞数
分类专栏： mysql 文章标签： mysql 事务 隔离级别 可重复读 幻读
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yang1018679/article/details/106581183
版权

一、事务的acid特性

  • 原子性：一个事务必须被视为一个不可分割的最小工作单元，事务中所有操作要么全部操作成功，要么全部失败回滚；
  • 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态；
  • 隔离性：通常来说，一个事务所做的修改在其提交之前，对其他事务是不可见的；
  • 持久性：一旦事务提交，所做的修改就会永久保存早数据库中，即使此时系统崩溃，修改部分也不会丢失。

        在sql标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改在另一个事务中是可见的还是不可见的。较低级别的隔离通常可以执行更高级别的并发，系统的开销也更低。

二、隔离级别

  • read uncommitted（未提交读）

        在read uncommitted级别，事务中的修改，即使还没有提交，在其他事务中也是可见的，也称作“脏读”，实际中很少使用。先看看数据库当前隔离级别并更改为read uncommitted级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

        然后同时开启事务1和事务2。在事务1中先查看某条数据，然后在事务2中修改该条数据，暂不提交，这时在事务1中再次查看这条数据，发现数据变化了的，这时就发生“脏读”了。

#事务1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
                                               #事务2
                                               mysql> start transaction;
                                               Query OK, 0 rows affected (0.00 sec)
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 111111 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
                                               mysql> update qh_user set phone = '222222' where id = 1;
                                               Query OK, 1 row affected (0.02 sec)
                                               Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 222222 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
  • read committed（提交读/不可重复读）

        在read committed级别，一个事务开始后，只能看见其他已经提交事务所做的修改。所以在一个事务中某条数据前后两次查看可能是不同的，原因就在于这两次查看之间有其他的事务对该条数据作了修改并提交了。

        先还是将事务设置成read committed级别：

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

        此处也是同时开启两个事务，先在事务1中查看某条数据，在事务2中修改该条数据，然后事务1中再查看该条数据，这时再提交事务2，最后再第三次在事务1中查看数据，会发现第二次查看和第三次查看数据是不一样的，也就是不可重复读。

#事务1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
                                               #事务2
                                               mysql> start transaction;
                                               Query OK, 0 rows affected (0.00 sec)
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  | 
+----+------+--------+----------+
|  1 | jack | 111111 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
                                               mysql> update qh_user set phone = '333333' where id = 1;
                                               Query OK, 1 row affected (0.00 sec)
                                               Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 111111 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
                                               mysql> commit;
                                               Query OK, 0 rows affected (0.01 sec)
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 333333 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
  • repeatable read（可重复读）

        在repeatable read级别，在一个事务中读某条数据，不管在另一个事务中是修改（update）还是删除（delete），在原事务中都是不可见的，也就是说多次读取的结果都是一致的，部分解决了不可重复读的问题。还是先设置隔离级别：

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

        还是同时开启四个事务1、2、3、4，先在事务1中查看某条数据，在事务2中修改并提交，再查看事务1中数据，应该是没有变化，此时在事务3中删除这条记录，再查看事务1中数据，应该也是没变化，最后再在事务4中插入一条记录，此时理论上在事务1中应该会看到多出一条数据，也就出现了“幻读”，但是innodb存储引擎已通过mvcc解决了“幻读”问题，所以实际上还是没变化。

#事务1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
                                               
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 333333 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
                                               #事务2
                                               mysql> start transaction;
                                               Query OK, 0 rows affected (0.00 sec)
                                               mysql> update qh_user set phone = '1111' where id = 1;
                                               Query OK, 1 row affected (0.00 sec)
                                               Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 333333 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
                                               #事务3
                                               mysql> start transaction;
                                               Query OK, 0 rows affected (0.00 sec)

                                               mysql> delete from qh_user where id = 1;
                                               Query OK, 1 row affected (0.00 sec)

                                               mysql> commit;
                                               Query OK, 0 rows affected (0.01 sec)
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 333333 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
                                               #事务4
                                               mysql> start transaction;
                                               Query OK, 0 rows affected (0.00 sec)

                                               mysql> insert into qh_user(name, phone, address)
                                                   -> values ('rose', '55555', 'beijing');
                                               Query OK, 1 row affected (0.00 sec)

                                               mysql> commit;
                                               Query OK, 0 rows affected (0.00 sec)
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 333333 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
  • serializable（可串行化）

        其实在repeatable read级别，只是部分解决了“幻读”问题，虽然在事务1中没看到新增的记录，但实际上是可以修改这条记录的。

#事务1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  | 
+----+------+--------+----------+
|  1 | jack | 333333 | new york |
+----+------+--------+----------+
1 row in set (0.00 sec)
                                                #事务2
                                                mysql> start transaction;
                                                Query OK, 0 rows affected (0.00 sec)

                                                mysql> insert into qh_user(name, phone, address) 
                                                    -> values ('david', '8888', 'toronto');
                                                Query OK, 1 row affected (0.00 sec)

                                                mysql> select * from qh_user;
                                                +----+-------+--------+----------+
                                                | id | name  | phone  | address  |
                                                +----+-------+--------+----------+
                                                |  1 | jack  | 333333 | new york |
                                                |  2 | david | 8888   | toronto  |
                                                +----+-------+--------+----------+
                                                2 rows in set (0.00 sec)

                                                mysql> commit;
                                                Query OK, 0 rows affected (0.01 sec)
mysql> select * from qh_user;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | jack | 333333 | new york | 
+----+------+--------+----------+
1 row in set (0.00 sec)

mysql> update qh_user set address = 'california' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

        从上述实验结果可以看到，虽然在事务1中select没有id=2的记录，但是直接修改这条数据，数据库返回的依然是ok的，所以说mvcc并没有完全解决幻读问题。而在serializable级别每读取一行数据都会加锁，是最高的隔离级别，这样就可以彻底解决“幻读”问题。

#事务1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from qh_user;
+----+-------+--------+------------+
| id | name  | phone  | address    | 
+----+-------+--------+------------+
|  1 | jack  | 333333 | new york   |
|  2 | david | 8888   | california |
+----+-------+--------+------------+
2 rows in set (0.00 sec)
                                                #事务2
                                                mysql> start transaction;
                                                Query OK, 0 rows affected (0.00 sec)

                                                mysql> insert into qh_user(name, phone, address)
                                                    -> values ('floyd', '55555', 'beijing');
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
                                                Query OK, 1 row affected (25.69 sec)

        由上述实验结果可以看到，在设置serializable级别后，在事务2中插入一条新纪录后，并没有马上显示insert成功，而是阻塞在那里，直到事务1执行commit后，事务2才提示query ok。

        由此可见，“幻读”问题是解决了，但是这极可能会导致大量超时和锁竞争问题，实际中也很少应用。只有在必须确保数据一致性并且可以接受没有并发情况下才会考虑使用该级别。

 

 

lucas1018
关注
专栏目录
laravel 中事务的使用
weixin_43681591的博客
01-13 1万+
方法一: //它不需要引入直接就可以用 public function Transaction(){  　　DB::beginTransaction(); //开启事务 　　sql1=DB::table(′demo′)−>where(′id′,′6′)−>delete();sql1=DB::table(′demo′)−>where(′id′,′6′)−>delete...
InnoDB在RR隔离级别下的幻读问题的分析
最新发布
Legolas_PRC的博客
02-07 399
对innDB在RR隔离级别下幻读问题的简单分析
laravel设置mysql事务级别_laravel框架中的MySQL事务处理
weixin_30536391的博客
02-03 275
在操作数据库中，实现一个功能时有时候会多次操作数据库，在多次操作数据库时，就会有可能出现其中一条sql语句执行成功，而另一条sql语句执行失败。这样的话就会出现很严重的问题，所以可以用MySQL的实物处理来解决这个问题。laravel中的事物：假如说：有一个用户表(user)里面有两个字段，username，passwordpassword字段设置为char(32),固定长度。现在想要一次性插入两...
laravel 事务_mysql的事务隔离和幻读和死锁问题
weixin_39538693的博客
12-03 428
1.系统要通过严格的ACID测试,ACID表示原子性/一致性/隔离性/持久性原子性:一个事务必须被视为一个不可分割的最小工作单元一致性:数据库总是从一个一致性的状态转换到另外一个一致性的状态隔离性:通常来说一个事务所做的修改在最终提交以前对其他事务是不可见的持久性:一旦事务提交,则其所做的修改就会永久保存到数据库中2.sql标准中定义了四种隔离,较低级别的隔离可以执行更高的并发,开销也更低READ...
laravel设置mysql事务级别_如何在laravel 5中使用DB事务？
weixin_35273106的博客
02-06 167
首先，我们要了解为什么在laravel项目中使用事务。数据库(db)事务(简称：事务)是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。db事务是一个非常重要的概念，而且非常有用，因为它可以安全地管理项目数据。laravel 5提供了事务提交、回滚等功能。那么如何在你的laravel 项目中使用DB事务？下面我们给大家举一个简单的例子。我有三张表分别为“items”,“it...
Laravel之路——事务
weixin_34104341的博客
12-26 230
准备： 　　表必须是InnoDB引擎 DB::beginTransaction(); try{ $name = 'abc'; $result1 = Test::create(['name'=>$name]); if (!$result1) { /** * Exception类接收的参数 * ...
innodb如何巧妙的实现事务隔离级别详解
12-16
【MySQL InnoDB 事务隔离级别详解】 在数据库管理系统中，事务是确保数据一致性的重要机制，而InnoDB存储引擎则是MySQL中支持事务的主要引擎。事务的四大特性，即原子性（Atomicity）、一致性（Consistency）、隔离...
MySQL的Innodb中的事务隔离级别和锁的关系
12-14
InnoDB默认的隔离级别是可重复读（Repeatable read），在此级别下，InnoDB使用Next-Key Locks来消除幻读，这是一种行锁与间隙锁的组合，不仅锁定查询涉及的具体行，还会锁定索引范围内的空隙，以防止其他事务插入...
MySQL中Innodb的事务隔离级别和锁的关系的讲解教程
09-10
本文档将介绍MySQL中InnoDB引擎事务隔离级别与锁之间的关系，并结合美团技术团队的经验分享，为大家提供一份详细的教程。 事务隔离级别是数据库事务处理的一个重要概念，它定义了事务与事务之间的隔离程度，主要...
MySQL数据库事务隔离级别详解
12-16
在多用户同时访问数据库的情况下，事务隔离级别能够确保数据的一致性和完整性，避免并发操作带来的各种问题，如脏读、不可重复读和幻读。 1. 脏读（Dirty Read）： 脏读指的是在一个事务中，事务A读取了事务B还未...
关于MySQL的四种事务隔离级别！
Prince_96的博客
11-16 399
本文实验的测试环境：Windows 10+cmd+MySQL5.6.36+InnoDB
laravel中的事务处理
奴役人生
01-23 9581
你可以用 DB 门面的 transaction 方法在一个事务里执行一系列操作。如果事务闭包里抛出异常，那么事务就会自动回滚。如果闭包执行成功，那么事务就会自动提交。使用 transaction 方法就无需手动回滚或者提交事务了： DB::transaction(function () { DB::table('users')->update(['votes' => 1]);
事务的四大特性（ACID）
weixin_30687587的博客
11-08 125
事务的四大特性（ACID） 1.原子性（atomicity）：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。 2.一致性（consistency）：数据库总数从一个一致性的状态转换到另一个一致性的状态。 3.隔离性（isolation）：一个事务所做的修改在最终提交以前，...
MySQL设置事物隔离级别
m0_37477061的博客
11-14 929
高性能MySQL学习总结及实验验证： 数据库隔离级别有四种，应用《高性能mysql》一书中的说明：   然后说说修改事务隔离级别的方法： 1.全局修改，修改mysql.ini配置文件，在最后加上 1 #可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. 2 [mysqld] 3 tra...
laravel日常使用总结
小瓶盖zzz的博客
03-14 932
laravel日常使用总结一、redis 运行服务时报错：Creating Server TCP listening socket *:6379: bind: No such file or directory解决方法，依次输入以下命令1. redis-cli.exe2. Shutdown3. Exit4. redis-server.exe 二、路由routes.php的设置Routes.php是...
laravel设置mysql事务级别_laravel数据库事务回滚
weixin_39560657的博客
02-17 134
背景：近期使用Laravel-5.4进行项目开发时涉及到关联模型的数据操作，想到用事务回滚。中间涉及到数据引用及异常的捕获。数据库事务回滚有两种方式：自动回滚、手动回滚。举例如下：自动回滚function(Request$request){DB::transaction(function()use($request){try{$admin=Admin::create([‘name‘...
laravel框架中的MySQL事务处理
Iloveyougirls的博客
05-08 8947
在操作数据库中，实现一个功能时有时候会多次操作数据库，在多次操作数据库时，就会有可能出现其中一条sql语句执行成功，而另一条sql语句执行失败。这样的话就会出现很严重的问题，所以可以用MySQL的实物处理来解决这个问题。 laravel中的事物： 假如说：有一个用户表（user） 里面有两个字段，username，password password字段设置为char(32),固定长度。
项目开发中MySQL应该选择什么事务隔离级别
谷子的博客
03-13 2628
读了一篇讲述MySQL事务隔离级别的帖子，加上自己的一点理解写了这篇文章（文中部分RC举例为引用） 先借用一个例子，下面这个场景你可能遇到过。 A:“MySQL有几个事务隔离级别？” B:“读未提交，读已提交，可重复读，串行化四个！默认是可重复读” A:“为什么mysql选可重复读作为默认的隔离级别？” B:"…" A:“你们项目中选了哪个隔离级别？为什么？” B:“默认的可重复读，原因…” 那么...
MySQL InnoDB事务隔离级别与锁机制深度解析
"这篇教程详细解析了MySQL中Innodb引擎的事务隔离级别与锁的关系，基于美团技术团队的实际经验分享。文章旨在帮助读者理解在事务处理中数据库如何确保一致性和隔离性，以及如何处理并发访问，以避免死锁。" 在MySQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值