MySql事务隔离

提到事务，你肯定会想到ACID（Atomicity、Consistency、Isolation、Durability，即原⼦性、⼀致性、隔离性、持久性），今天我们就来说说其中，也就是“隔离性”。

隔离性与隔离级别

当数据库上有多个事务同时执⾏的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题

概念	表现出的问题情况
脏读（dirty read）	当数据库中⼀个事务A正在修改⼀个数据但是还未提交或者回滚，另⼀个事务B 来读取了修改后的内容并且使⽤了，之后事务A提交了，此时就引起了脏读。
不可重复读 （non-repeatable read）	在⼀个事务A中多次操作数据，在事务操作过程中(未最终提交)，事务B也才做了处理，并且该值发⽣了改变，这时候就会导致A在事务操作的时候，发现数据与第⼀次不⼀样了。
幻读（phantom read）	⼀个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插⼊了满⾜其查询条件的新数据

为了解决这些问题，就有了“隔离级别”的概念。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）	解释说明	通俗说明
读未提交（Read uncommitted）	可能	可能	可能	⼀个事务还没提交时，它做的变更就能被别的事务看到。	别⼈改数据的事务尚未提交，我在我的事务中也能读到。
读已提交（Read committed）	不可能	可能	可能	⼀个事务提交之后，它做的变更才会被其他事务看到。	别⼈改数据的事务已经提交，我在我的事务中才能读到。
可重复读（Repeatable read）	不可能	不可能	可能(InnoDb不可能)	⼀个事务执⾏过程中看到的数据，总是跟这个事务在启动时看到的数据是⼀致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可⻅的。	别⼈改数据的事务已经提交，我在我的事务中也不去读。
串行化（Serializable ）	不可能	不可能	不可能	对于同⼀⾏记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前⼀个事务执⾏完成，才能继续执⾏。	我的事务尚未提交，别⼈就别想改数据。

这4种隔离级别，并⾏性能依次降低，安全性依次提⾼。

下面用实际例子进行举例不同隔离级别的具体差异。

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

隔离级别	V1	V2	V3	说明
读未提交	2	2	2	V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了
读已提交	1	2	2	事务B的更新在提交后才能被A看到
可重复读	1	1	2	事务在执⾏期间看到的数据前后必须是⼀致的。
串⾏化	1	1	2	事务B执⾏“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执⾏。

启动参数 transaction-isolation 设置数据库隔离级别

事务隔离的实现

展开说明“可重复读”
在MySQL中，实际上每条记录在更新的时候都会同时记录⼀条回滚操作。记录上的最新值，通过回滚操作，都可以得到前⼀个状态的值。

假设⼀个值从1被按顺序改成了2、3、4，在回滚⽇志⾥⾯就会有类似下⾯的记录。

同⼀条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）只有在不需要的时候才会删除对应的事务视图。

所以尽量不要使用长事务，除了对回滚段的影响，⻓事务还占⽤锁资源，也可能拖垮整个库。

事务的启动方式

1. 显式启动事务语句， begin 或 start transaction。
2. set autocommit=0，这个命令会将这个线程的⾃动提交关掉。

建议总是使⽤set autocommit=1, 通过显式语句的⽅式来启动事务。
可以在information_schema库的innodb_trx这个表中查询⻓事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

如何避免长事务对业务的影响？

从应用开发端来看：

1. 确认是否使⽤了set autocommit=0。这个确认⼯作可以在测试环境中开展，把MySQL的general_log开起来，然后随便跑⼀个业务逻辑，通过general_log的⽇志来确认。⼀般框架如果会设置这个值，也就会提供参数来控制⾏为，你的⽬标就是把它改成1。
2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先⽤begin/commit框起来。我⻅过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务可以去掉。
3. 业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执⾏的最⻓时间，避免单个语句意外执⾏太⻓时间。

从数据库端来看：

1. 监控 information_schema.Innodb_trx表，设置⻓事务阈值，超过就报警/或者kill；
2. Percona的pt-kill这个⼯具不错，推荐使⽤；
3. 在业务功能测试阶段要求输出所有的general_log，分析⽇志⾏为提前发现问题；
4. 如果使⽤的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过⼤，这样设置后清理起来更方便。

innodb事务隔离级别及实现

1.innodb支持RC和RR隔离级别实现是用的一致性视图(consistent read view)
2.事务在启动时会拍一个快照,这个快照是基于整个库的.
基于整个库的意思就是说一个事务内,整个库的修改对于该事务都是不可见的(对于快照读的情况)
如果在事务内select t表,另外的事务执行了DDL t表,根据发生时间,要嘛锁住要嘛报错(参考第六章)

3.事务是如何实现的MVCC呢?
(1)每个事务都有一个事务ID,叫做transaction id(严格递增)
(2)事务在启动时,找到已提交的最大事务ID记为up_limit_id。
(3)事务在更新一条语句时,比如id=1改为了id=2.会把id=1和该行之前的row trx_id写到undo log里,
并且在数据页上把id的值改为2,并且把修改这条语句的transaction id记在该行行头
(4)再定一个规矩,一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transaction id做比对,
如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo log里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数据

4.什么是当前读,由于当前读都是先读后写,只能读当前的值,所以为当前读.会更新事务内的up_limit_id为该事务的transaction id

5.为什么rr能实现可重复读而rc不能,分两种情况
(1)快照读的情况下,rr不能更新事务内的up_limit_id,
而rc每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc不能可重复读
(2)当前读的情况下,rr是利用record lock+gap lock来实现的,而rc没有gap,所以rc不能可重复读