HelloWorld
登录

事务的ACID，隔离级别，脏读，幻读和不可重复读

那年烟雨落申城

2023-07-09

968 0 0

事务的ACID

原子性（atomicity)

一个事务中执行的sql语句，要么全部成功，要么全部失败，不可能一部分成功。

一致性（consistency)

事务执行前和执行后数据一致，也就是说事务中的sql语句不能只执行一部分。这种请款一般发生在事务异常中断，服务器异常宕机，此时需要保证正在执行的事务在数据库重启后要进行提交或回滚操作。

隔离性（isolation）

多个事务执行过程中不互相干扰。

持久性（durability）

事务提交成功后永久保存在磁盘，不能出现事务提交成功后，数据没保存情况，异常宕机会根据日志恢复，该回滚回滚，该提交提交

事务的隔离级别（效率依次降低）

读未提交

一个事务能读到其它事务未提交的数据。会产生脏读

读已提交

一个事务中，能读到其它事务已经提交的数据，会产生幻读，不可重复读。

可重复读

一个事务中，相同的筛选条件每次读到的数据是一样的。

串行化

事务按提交先后顺序执行。

脏读，幻读和不可重复读

使用以下表复现一下脏读，幻读和不可重复读

1create table testtable (
2	id bigint primary key auto_increment comment '主键',
3	bus1 int comment '业务字段1',
4	bus2 varchar(20) comment '业务字段2'
5);

脏读

在读未提交的隔离级别下，事务A可以读取到事务B未提交的数据，假设事务B最后被回滚了，事务A读到的数据视为脏读，因为这个数据未被持久化。

1-- 事务A insert一条语句，不进行commit 
2-- 设置当前session事务不自动提交
3set SESSION autocommit = 0;
4
5START TRANSACTION;
6insert into testtable values(NULL,2,'我是事务A2');

事务B去读取bus1 = 2的数据

1-- 事务B
2-- 设置当前session的数据库隔离级别为读未提交
3set session transaction isolation level read uncommitted;
4select * FROM  testtable where bus1 = 2;

结果： 在这里插入图片描述 可以看到查询到了事务A还没有commit的数据。此时我们将事务A回滚，再次执行事务B，选中rollback;然后执行： 在这里插入图片描述 此时再去执行事务B的查询： 在这里插入图片描述 此时查询不到刚才事务A未提交的数据了。这就是脏读，事务B读到了数据，但是最后这个数据没有提交而是回滚了，我们在库里就找不到这条数据了。

不可重复读

在读已提交的隔离级别下，事务A都某一条数据进行更新（增加，修改，删除）操作，事务B在事务A提交前读取到的数据和事务A提交后读取到的数据不一致。 我们复现一下这种场景： 先将事务隔离级别设置为读已提交：

1set session transaction isolation level read committed;

然后执行一次查询：

1select * FROM  testtable where bus1 = 1;

此时结果是： 在这里插入图片描述 然后事务A启动并提交：

1-- 设置当前session事务不自动提交
2set SESSION autocommit = 0;
3
4START TRANSACTION;
5update testtable set bus2  = '我是事务A提交后' where bus1  = 1;
6
7commit;

在这里插入图片描述 然后事务B再次以相同条件读取： 在这里插入图片描述 发现已经读取到了事务A提交的数据，这种情况就是事务B首先读取了一次数据，然后事务A执行并提交，然后事务B再次读取了一次数据，发现此时读取的数据和上一次读取的数据不一致。这就是不可重复读。

幻读

在可重复读的事务隔离级别中，同一个事务中两次查询同一范围的时候，后一次查询出了前一次不存在的行。 和不可重复读不同的地方有两点

  1. 幻读只针对行的多少，不针对每个行内内容是否更新
  2. 幻读的隔离级别是可重复读，而不可重复读的隔离级别是读已提交 我们来复现一下这个场景： 为了去掉干扰，我把数据表清空，并插入一条数据：
1TRUNCATE testtable; 
2START TRANSACTION;
3insert into testtable values(NULL,1,'我是事务A');
4COMMIT;

然后编写一个范围查询，对比前后的数据：

1-- 事务B
2-- 设置当前session的事务隔离级别为可重复读
3set SESSION autocommit = 0;
4set session transaction isolation level repeatable read;
5START TRANSACTION;
6select testtable.* FROM  testtable where bus1 >= 1 and bus1  <= 3;
7-- 每遇到一条记录 就sleep 30秒  当前条件中会查出一条记录
8select SLEEP(30) ,testtable.* from testtable where bus1 = 1; 
9select testtable.* FROM  testtable where bus1 >= 1 and bus1  <= 3;
10commit;

执行后会在select SLEEP(30) ,testtable.* from testtable where bus1 = 1; 这里sleep 30秒，然后执行：

1-- 设置当前session事务不自动提交
2set SESSION autocommit = 0;
3START TRANSACTION;
4insert into testtable values(NULL,2,'我是事务A在事务B中插入的数据');
5COMMIT;

等待30秒后的结果发现事务B的第一条select语句结果是： 在这里插入图片描述 第二条select语句结果是： 在这里插入图片描述 第三条select语句的结果是： 在这里插入图片描述 抛开第二条select是为了sleep 30秒使用的，第一条和第三条一样，说好的幻读呢？？？？？？？？

Innodb的MVCC机制

如果存储引擎是Innodb，可重复读的隔离级别下上述的幻读是不存在的，因为Innodb使用了MVCC多版本并发控制，在同一个事务中，第一条select语句会让Innodb对from后面的表做一个快照，对这个表的读取就在做快照的那一刻定格了，以后无论是怎么对这个表读取，都读不到做快照以后的数据，因此幻读在Innodb存储引擎下是不存在的。

主键冲突场景

网上有观点说，当事务A查询时某个主键不存在，事务B插入了这个主键，然后事务A也插入这个主键，产生冲突，这就是幻读，复现一下这个场景。

  1. 清空表testtable，然后插入4条数据：
1insert into testtable values(1,11,'第一条数据');
2insert into testtable values (3,22,'第二条数据');
3insert into testtable values (4,33,'第三条数据');
4insert into testtable values (10,44,'第四条数据');
  1. 在事务A中查询全部数据，然后更新id=3的数据，然后再查询全部数据不提交事务，事务B新增一条数据，主键id=2，提交事务，看一下结果：
1-- 事务A
2-- 设置当前session的事务隔离级别为可重复读
3set SESSION autocommit = 0;
4set session transaction isolation level repeatable read;
5START TRANSACTION;
6select * from testtable ;
7-- select * from testtable t2 ;
8-- insert into testtable values(2, '66','第六条数据');
9-- commit;
1-- 事务B
2-- 设置当前session的事务隔离级别为可重复读
3set SESSION autocommit = 0;
4set session transaction isolation level repeatable read;
5START TRANSACTION;
6insert into testtable values(2, '55','第五条数据');
7commit;

先执行事务A，然后再执行事务B，然后将事务A最后3行执行：

1-- 事务A
2-- 设置当前session的事务隔离级别为可重复读
3-- set SESSION autocommit = 0;
4-- set session transaction isolation level repeatable read;
5-- START TRANSACTION;
6-- select * from testtable ;
7select * from testtable t2 ;
8insert into testtable values(2, '66','第六条数据');
9commit;

在事务A的查询语句中，结果和之前的查询是一样的： 在这里插入图片描述 然而执行到插入的时候，就报主键冲突了。 在这里插入图片描述 这算不算幻读我不知道，但是可以解释，RR隔离级别下因为事务A的第一次select在事务B之前，此时会形成快照，因此事务B的操作事务A是不可见的（MVCC机制导致的），如果事务A是下面这种情况，便可以看到事务B已经提交的数据：

1-- 事务A
2-- 设置当前session的事务隔离级别为可重复读
3 set SESSION autocommit = 0;
4 set session transaction isolation level repeatable read;
5 START TRANSACTION;
6
7update testtable set bus1 = '77' where id = 3;
8 -- select * from testtable ;
9--  insert into testtable values(2, '66','第六条数据');
10
11-- commit;

操作步骤和上面一样，这段sql和之前事务A的区别就是第一次select换成了update，原因就是事务会在第一条sql正式启动，如果只有 START TRANSACTION;是不会开始的。执行完事务B后，再执行注释的那些代码时，就能看到事务B提交的数据了。 在这里插入图片描述 这时执行后面的insert很明显就会主键冲突了。 至于这算不算幻读，就仁者见仁智者见智了。 但这种主键冲突类似的问题可不可以被解决呢，答案是可以的。

select lock in share mode和select for update

select lock in share这个语句会把符合条件的数据作为共享模式，且为当前读（就是读取最新的数据库数据，而不是快照读），共享模式下不允许更新。

  1. 清空数据并重新插入
1TRUNCATE table testtable;
2
3insert into testtable values(1,11,'第一条数据');
4insert into testtable values (3,22,'第二条数据');
5insert into testtable values (4,33,'第三条数据');
6insert into testtable values (10,44,'第四条数据');
  1. 对比快照读和当前读
  • 这是快照读，读到了当前表中的数据
1-- 事务A
2set autocommit = 0;
3start transaction;
4
5select * from testtable t ;
6
7
8-- select * from testtable lock in share mode;

在这里插入图片描述 然后用另一个session更新

1-- 事务B
2set autocommit = 0;
3start transaction;
4
5update testtable set bus2 = '更新更新更新' where id = 3;
6
7commit;

此时再回到事务A中，执行注释的那条语句：

1 select * from testtable lock in share mode;

可以看到读到了最新的数据： 在这里插入图片描述 和上面没有lock in share mode的不一样，那么我们接下来再在当前事务中读取一次全表数据，不加lock in share mode是这样的： 在这里插入图片描述 这次是快照读，读到的和第一次读取的一样，这就是当前读和快照读的区别。 此时所有数据被lock in share mode锁住，我们更新一下试试，我修改事务B的sql后重新执行了一下，结果提示： 在这里插入图片描述 修改后的sql如下：

1-- 事务B
2set autocommit = 0;
3start transaction;
4
5update testtable set bus2 = '我是lock in share mode更新测试' where id = 3;
6
7commit;

这种情况是可以锁住筛选范围内的所有行，如果事务A较长，会特别影响更新性能，读取的话不受影响，还有一种是select for update，被称为排他锁，意思是 完全排他，连读取都不行，这种锁就类似悲观锁，比lock in share mode还狠。

那么有没有办法解决这个问题呢，有的，那就是Next-Key Lock

行锁，间隙锁，Next-Key Lock

行锁：锁直接加在某一行 间隙锁：锁加在不存在的空闲空间，可能是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引记录之后的空间。 Next-Key lock：就是行锁+间隙锁一起使用。 比如上面的例子中，如果对id=3加锁，那么 [1,3),(3,4] 会被添加间隙锁，锁本身不锁定当前行，当前行由行锁锁定。

默认情况下，Mysql是不打开Next-Key Lock的，可以通过以下命令查看

1show variables like 'innodb_locks_unsafe_for_binlog';

在这里插入图片描述 ~~ 个人认为Mysql没有默认打开间隙锁的原因有以下几点：~~ 1. 间隙锁影响性能，如果锁锁住的是表中最大的那个id，那么最大的id到正无穷被锁住，影响其他事务正常插入数据，导致性能下降 以上删除线部分有待验证，暂时删除，留待后续补充。

点赞
收藏

评论区

加载中...

相关推荐

golang实现MySQL数据库事物的提交与回滚

MySQL事务主要用于处理操作量大，复杂度高的数据。在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务用来管理insert,update,delete语句，事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicit

Spring事务（二）：Spring事务的特点

事务特性实现事务必须满足以下四大特性：Atomicity（原子性）：构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行。Consistency（一致性）：数据库在事务执行前后，完整性没有被破坏。（转账前后，钱的总数不变）Durability（持久性）：事务执行成功后必须全部写入磁盘。

mysql 事物四大特性和事物的四个隔离

1、事物四大特性（ACID）1.原子性（atomicity）：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。2.一致性（consistency）：数据库总数从一个一致性的状态转换到另一个一致性的状态。3.隔离性

MySql学习17

一.数据库事务的四大特性（ACID）如果一个数据库声称支持事务的操作，那么该数据库必须要具备以下四个特性：原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操

MySQL transaction

MySQLtransaction(数据库的事务)数据库事务（DatabaseTransaction），是指作为单个逻辑工作单元执行的一系列操作。要么完全执行，要么完全地不执行。ACID事务必须具备ACID四个特性原子性(Atomicity)原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。一致性（Consistency）

MySQL 事务的四大特性ACID介绍

事务的四大特性（ACID）1、原子性（Atomicity）事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生。2、一致性（Consistency）事务发生前和发生后，数据的完整性必须保持一致。3、隔离性（Isolation）当并发访问数据库时，一个正在执行的事务在执行完毕前，对应其他的会话是不可见的，多个并发事