阅读 75

ACID之I:事务隔离

MyISAM 不支持事务, innoDB支持事务

多个事务可能存在问题

在多个事务共同操作时容易出现这样几个问题:

  • 脏读(dirty read)
  • 不可重复读(nonrepeatable)
  • 幻读(phantom read)

来具体看一下这三个问题会有什么影响

脏读:A事务读取到并使用了B事务还未提交的数据,这时如果B事务被撤回,则A操作的数据则是不准确的。比如以下银行存取款例子:

A事务 B事务
开启事务
开启事务
查询余额为1000
取出500,余额变为500
查询余额为500(脏数据)
撤销事务,此时余额为1000
存入100,此时余额为600
提交事务

不可重复读: A事务读取了B事务已经提交的数据。同一个事务内查询到不同的结果,比如A查询到银行账户有200元,然后准备取钱,这时B用同一个账号将钱转走,A取钱时会提示余额不足,A在同一个事务内发现余额发生了变化,第一次查到的结果不可重复
幻读:解决了不可重复读之后,保证同一个事务里,查询的结果都是事务开始时的状态(一致性)。但是,如果另一个事务同时插入了新的数据,则本事务再次更新时,就会发现这些新插入的数据,好像之前读到的不完整的数据是鬼影一样幻觉

隔离级别

为了解决脏读、不可重复读、幻读,有了"隔离级别这个概念"
SQL隔离级别包括:

  • 读未提交(read uncommitted):别人修改数据的事务还没有提交,我在我的事务内也能读到
  • 读可提交(read committed):别人修改数据的事务提交后,我在我的事务内才能读到
  • 可重复读(repeatable read):别人修改数据的事务提交后我也读不到,我只能读到我事务开始时保持的数据状态,我的事务内数据不受到其他事务的影响
  • 串行化(serializable):我的事务还没提交时,比我晚开始的事务只能是等待状态,同时只能有一个事务在进行

比如我的账户有500元,按照时间顺序执行以下两个事务

A事务 B事务
启动事务A查询余额为500
启动事务B查询到余额500
存入100元
再次查询余额Q1
提交事务B
查询余额Q2
提交事务A
再次查询余额Q3

根据不同的隔离级别,Q1、Q2、Q3会有不同的值。下面来详细分析:

  • 读未提交(read uncommitted):能够读到其他事务未提交的数据,也就是B事务存入100虽然没有提交,但是事务A第二次查询余额已经发生变化,Q1为600,后面没有涉及到金额的变化,所以Q2/Q3的值也是600
  • 读可提交(read committed):能够读到其他事务已经提交的数据,查询Q1时,因为B事务还没有提交,所以并没有影响到A事务,所以Q1的值还是500,当查询Q2时,B事务已经提交,A事务可以读到,所以Q2、Q3都是600
  • 可重复读(repeatable read):一个事务内的数据不受其他事务的影响,因此在A事务提交前,所以查到的余额不变,同事务开始时的值是一样的,因此Q1、Q2都为500。A事务提交后,可以发现B事务造成的数据影响,所以Q3的值为600
  • 串行化(serializable):事务A开始后,事务B再发起,则会被锁住,当事务A提交后,事务B才能进入,所以事务A提交前Q1、Q2的值是不变的都是500,提交事务A后,事务B才开始解锁执行,B提交后,Q3查询到的值为600

由以上可以看出这4种隔离级别,从上至下性能依次降低,安全性依次提高。

事务的启动方式

事务有两种启动方式:

  1. 显式启动事务语句,begin或者start transaction,提交commit,回滚rollback
  2. set autocommit=0,该命令会把这个线程的自动提交关掉。这样只要执行一个select语句,事务就启动,并不会自动提交,直到主动执行commit或rollback或断开连接。

本文为极客时间《MySQL实战45讲》的学习笔记,其中含有部分原文,如有侵权行为请联系我立刻删除
第二节:MySQL系列之一条更新SQL的生命历程