Mysql事务特性ACID、Mysql隔离级别、脏读、不可重复读、幻读

今天继续完善一下Mysql系列相关博客，记录一下学习成果，以便查阅，同时也希望能帮助到有需要的小伙伴，各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！

事务的定义

事务数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作; 事务是一组不可再分割的操作集合（工作逻辑单元）。

有事务的地方就有转账：

update user_account set balance = balance - 500 where id = 1;
update user_account set balance = balance + 500 where id = 2;

事务的开启和关闭

Mysql默认是自动提交事务的，可通过命令：show variables like 'autocommit';查看mysql的事务开启情况。

1或者ON表示启用，0或者OFF表示禁用。

mysql自动提交模式：

autocommit = ON：自动提交事务；

autocommit = OFF：禁止自动提交；

• 在 autocommit = ON的情况下，可通过begin;或者start transaction;命令手动开启一个事务。通过commit命令提交事务，或者通过rollback命令回滚事务。
• 在 autocommit = OFF的情况下，事务在用户本次对数据进行操作时自动开启，执行完 SQL 语句后，需要通过commit命令提交事务，或者通过rollback命令回滚事务。

Mysql模式是自动提交事务的，即autocommit = ON，除了使用begin或者start transaction开启事务外，还可以通过命令set session autocommit = off 或者 set autocommit = 0通过关闭自动提交来开启事务。

修改autocommit 对非事务型的表，比如MylSAM或者内存表，不会有 任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，也可以说是相当于一直 处于AUTOCOMMIT启用的模式。

事务特性ACID

原子性(Atomicity)：最小的工作单元，整个单元中的所有操作要么全部提交成功，要么全部失败回滚，不可能只执行其中一部分操作，这就是事务的原子性，如经典案例转账过程，转入和转出两个操作必须全部成功或者全部失败。

一致性(Consistency)：事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致。举例说明：张三向李四转100元，转账前和转账后的数据是正确的状态，这就叫一致性，如果出现张三转出100元，李四账号没有增加100元这就出现了数据错误，就没有达到一致性。

隔离性(Isolation)：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。简单说就是一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性(Durability)：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

事务的隔离级别及产生的问题

从网上找了一张现成的图，如下：

如上图所示，数据库的隔离级别有以下四种：

• 未提交读(Read Uncommitted)：可以读取到其他会话中未提交事务修改的数据。
• 提交读(Read Committed)：只能读取到已经提交的数据。
• 可重复读(Repeatable Read)：在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。
• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

我们可以通过：SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;设置隔离级别。

虽然InnoDB默认级别是Repeated Read，但是InnDB 中在该级别中它把幻读的问题也解决了，这个以后再记录，暂且演示脏读、不可重复读。

首先了解下什么是脏读、不可重复读和幻读：

• 脏读：读取未提交数据，一个事务(有的说一个线程)中读取到了另一个事务中未提交的数据。
• 不可重复读：前后多次读取，数据内容不一致，一个事务读取到了另外事务中提交的数据。
• 幻读(虚读)：前后多次读取，数据总量不一致，一个事务中读取到了另外事务中提交的insert数据。

准备下测试数据：

create table t_transaction_account(
    id int auto_increment primary key ,
    balance decimal(8,2),
    name varchar(36)
)engine = innodb default charset=utf8;
insert into t_transaction_account values(null,5000,'zhangsan'),(null,5000,'lisi');

从上图我们看到Read Uncommitted隔离级别会有脏读、不可重复读和幻读的问题，下面我们开两个窗口进行测试，

 

如上：在窗口1和2中设置隔离级别为未提交读，在窗口1中给id为1的账户转出500，id为2的账户转入500元，此时在窗口2中查询时已经读取到了窗口1中未提交的数据，产生了脏读。

脏读有什么影响呢，假设此时窗口2中将id为2的账户余额全部转出因某些原因延迟了1秒，在窗口2转出余额的同一时刻窗口1中回滚事务，那么窗口1中两个账户的余额恢复到开始状态都为5000元，一秒后窗口2中id为2的账户扣除余额成功提交了事务，账户1余额还是5000，那账户2的余额就变为了-500，这-500什么意思，银行白亏了500呗。

两个窗口中执行的操作命令放到一个表格中方便查看：

执行时间	窗口1(设置为read uncommitted)	窗口2(设置为read uncommitted)	事件
T1	start transaction	start transaction
T2	update t_transaction_account set balance = balance - 500 where id = 1; update t_transaction_account set balance = balance + 500 where id = 2;
T3		select * from t_transaction_account;	脏读
T4		update t_transaction_account set balance = balance - 5500 where id = 1;
T5		阻塞
T6	rollback;	阻塞
T7		commit;

 

 

 

 

 

 

 

 

 

再看下mysql中已提交读(read committed)隔离级别下不可重复读的问题：

执行时间	窗口1	窗口2(设置为read committed)	事件
T1	start transaction	start transaction
T2	update t_transaction_account set balance = balance - 500 where id = 1;
T3		select * from t_transaction_account where id = 1;(还为5000)	避免了脏读
T4	commit;
T5		select * from t_transaction_account where id = 1;(还剩4500)	不可重复读

 

 

 

 

 

 

 

 

 

在T3和T5时刻读取到的余额是一致的，因为read committed隔离级别避免了脏读的发生；对于某个处在在已提交读隔离级别下的事务来说，只要其他事务修改了某个数据的值，并且之后提交了，那么该事务就会读到该数据的最新值，如窗口2中在T3和T7时刻读取id为1的余额时读取到的余额是不一样的，这种情况就叫做发生了不可重复读。

最后下mysql中可重复读(repeatable read)隔离级别：

执行时间	窗口1	窗口2(设置为repeatable read)	事件
T1	start transaction	start transaction
T2	update t_transaction_account set balance = balance - 500 where id = 1;
T3		select * from t_transaction_account where id = 1;(还是5000)	避免了脏读
	commit；
T4		select * from t_transaction_account where id = 1;(还是5000)	避免了不可重复读
T5	insert into t_transaction_account values(null,0,'wangwu');
T6		select * from t_transaction_account where id < 10 ;(还是两条数据，没有wangwu这条数据)	没出现幻读的情况
T7	commit;
T8		select * from t_transaction_account where id < 10 ;(3条数据)

 

 

 

 

 

 

 

 

 

 

 

居然没有出现幻读的情况，是的，InnDB 引擎默认的事务隔离级别是可重复读（Repeatable Read），在该级别中它把幻读的问题也解决了，InnDB 中事务隔离级别通过锁、MVCC 实现，这个以后再说。