请选择 进入手机版 | 继续访问电脑版
点击联系客服
客服QQ:509006671 客服微信:mengfeiseo
查看: 8|回复: 0

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

[复制链接]

1

主题

1

帖子

-7

积分

限制会员

积分
-7
发表于 4 天前 | 显示全部楼层 |阅读模式
事务的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”

);

脏读
p>在读未提交的隔离级别下,事务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;

结果:


可以看到查询到了事务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;

此时结果是:


然后事务A启动并提交:

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


然后事务B再次以相同条件读取:


发现已经读取到了事务A提交的数据,这种情况就是事务B首先读取了一次数据,然后事务A执行并提交,然后事务B再次读取了一次数据,发现此时读取的数据和上一次读取的数据不一致。这就是不可重复读。

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

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

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

    -- 事务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秒,然后执行:

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

    等待30秒后的结果发现事务B的第一条select语句结果是:


    第二条select语句结果是:


    第三条select语句的结果是:


    抛开第二条select是为了sleep 30秒使用的,第一条和第三条一样,说好的幻读呢????????

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

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

    [ol]
  • 清空表testtable,然后插入4条数据:[/ol]
    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,提交事务,看一下结果:[/ol]
    -- 事务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;

    -- 事务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的查询语句中,结果和之前的查询是一样的:


    然而执行到插入的时候,就报主键冲突了。


    这算不算幻读我不知道,但是可以解释,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提交的数据了。


    这时执行后面的insert很明显就会主键冲突了。
    至于这算不算幻读,就仁者见仁智者见智了。
    但这种主键冲突类似的问题可不可以被解决呢,答案是可以的。

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

    [ol]
  • 清空数据并重新插入[/ol]
    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,'第四条数据');
  • 对比快照读和当前读[/ol]
  • 这是快照读,读到了当前表中的数据
    -- 事务A
    set autocommit = 0;
    start transaction;
    select * from testtable t ;
    -- select * from testtable lock in share mode;


    然后用另一个session更新

    -- 事务B
    set autocommit = 0;
    start transaction;
    update testtable set bus2 = '更新更新更新' where id = 3;
    commit;

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

    select * from testtable lock in share mode;

    可以看到读到了最新的数据:


    和上面没有lock in share mode的不一样,那么我们接下来再在当前事务中读取一次全表数据,不加lock in share mode是这样的:


    这次是快照读,读到的和第一次读取的一样,这就是当前读和快照读的区别。
    此时所有数据被lock in share mode锁住,我们更新一下试试,我修改事务B的sql后重新执行了一下,结果提示:


    修改后的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';


    个人认为Mysql没有默认打开间隙锁的原因有以下几点:

    [ol]
  • 间隙锁影响性能,如果锁锁住的是表中最大的那个id,那么最大的id到正无穷被锁住,影响其他事务正常插入数据,导致性能下降
  • 先查询逐渐是否存在,然后再插入的这种场景出现在数据库中的概率不是很大,一个原因是设计自增主键的数据库一般不会在间隙中插入数据,都是让数据库自行自增,另一个原因是如果真的出现这种问题,一般都通过分布式锁去解决,很少在数据库层面解决,毕竟这是个非常影响数据库吞吐量的操作。[/ol]
  • 回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    QQ|无图版|手机版|小黑屋|汕头@IT精英团

    Powered by Discuz! X3.4 © 2021 Comsenz Inc.

    GMT+8, 2021-6-14 17:34 , Processed in 0.388633 second(s), 42 queries .

    快速回复 返回顶部 返回列表