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

事务的ACID

原子性（atomicity)

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

一致性（consistency)

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

隔离性（isolation）

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

持久性（durability）

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

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

读未提交

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

读已提交

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

可重复读

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

串行化

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

脏读，幻读和不可重复读

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

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

脏读

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

-- 事务A insert一条语句，不进行commit 
-- 设置当前session事务不自动提交
set SESSION autocommit = 0;

START TRANSACTION;
insert into testtable values(NULL,2,'我是事务A2');

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

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

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

不可重复读

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

set session transaction isolation level read committed;

然后执行一次查询：

select * FROM  testtable where bus1 = 1;

此时结果是：事务的ACID，隔离级别，脏读，幻读和不可重复读然后事务A启动并提交：

-- 设置当前session事务不自动提交
set SESSION autocommit = 0;

START TRANSACTION;
update testtable set bus2  = '我是事务A提交后' where bus1  = 1;

commit;

事务的ACID，隔离级别，脏读，幻读和不可重复读然后事务B再次以相同条件读取：发现已经读取到了事务A提交的数据，这种情况就是事务B首先读取了一次数据，然后事务A执行并提交，然后事务B再次读取了一次数据，发现此时读取的数据和上一次读取的数据不一致。这就是不可重复读。

幻读

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

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

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

执行后会在`select SLEEP(30) ,testtable.* from testtable where bus1 = 1; `这里sleep 30秒，然后执行：
```sql
-- 设置当前session事务不自动提交
set SESSION autocommit = 0;
START TRANSACTION;
insert into testtable values(NULL,2,'我是事务A在事务B中插入的数据');
COMMIT;

等待30秒后的结果发现事务B的第一条select语句结果是：事务的ACID，隔离级别，脏读，幻读和不可重复读第二条select语句结果是：第三条select语句的结果是：抛开第二条select是为了sleep 30秒使用的，第一条和第三条一样，说好的幻读呢？？？？？？？？

Innodb的MVCC机制

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

主键冲突场景

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

清空表testtable，然后插入4条数据：

insert into testtable values(1,11,'第一条数据');
insert into testtable values (3,22,'第二条数据');
insert into testtable values (4,33,'第三条数据');
insert into testtable values (10,44,'第四条数据');

在事务A中查询全部数据，然后更新id=3的数据，然后再查询全部数据不提交事务，事务B新增一条数据，主键id=2，提交事务，看一下结果： ```sql

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

```sql
-- 事务B
-- 设置当前session的事务隔离级别为可重复读
set SESSION autocommit = 0;
set session transaction isolation level repeatable read;
START TRANSACTION;
insert into testtable values(2, '55','第五条数据');
commit;

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

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

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

-- 事务A
-- 设置当前session的事务隔离级别为可重复读
 set SESSION autocommit = 0;
 set session transaction isolation level repeatable read;
 START TRANSACTION;

update testtable set bus1 = '77' where id = 3;
 -- select * from testtable ;
--  insert into testtable values(2, '66','第六条数据');

-- commit;

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

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

清空数据并重新插入
```
TRUNCATE table testtable;
```

insert into testtable values(1,11,'第一条数据'); insert into testtable values (3,22,'第二条数据'); insert into testtable values (4,33,'第三条数据'); insert into testtable values (10,44,'第四条数据');

2. 对比快照读和当前读
+ 这是快照读，读到了当前表中的数据
```sql
-- 事务A
set autocommit = 0;
start transaction;

select * from testtable t ;


-- select * from testtable lock in share mode;

事务的ACID，隔离级别，脏读，幻读和不可重复读然后用另一个session更新

-- 事务B
set autocommit = 0;
start transaction;

update testtable set bus2 = '更新更新更新' where id = 3;

commit;

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

 select * from testtable lock in share mode;

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

-- 事务B
set autocommit = 0;
start transaction;

update testtable set bus2 = '我是lock in share mode更新测试' where id = 3;

commit;

这种情况是可以锁住筛选范围内的所有行，如果事务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的，可以通过以下命令查看

show variables like 'innodb_locks_unsafe_for_binlog';

事务的ACID，隔离级别，脏读，幻读和不可重复读 ~~~ 个人认为Mysql没有默认打开间隙锁的原因有以下几点：~~ ~~1. 间隙锁影响性能，如果锁锁住的是表中最大的那个id，那么最大的id到正无穷被锁住，影响其他事务正常插入数据，导致性能下降~~ 以上删除线部分有待验证，暂时删除，留待后续补充。