【七】MySQL之事务ACID、隔离级别、脏读、幻读、不可重复读

一、ACID

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

1.原子性atomicity：在同一个事务内部的一组操作必须全部执行成功（或者全部失败）
2.一致性consistency：事务执行前后，数据完整性没有被破坏
3.隔离性isolation：一个事务操作的结果在何时以何种方式对其他并发的事务操作可见
4.持久性durability：事务结束后，对数据的修改是永久的

二、InnoDB下的事务的隔离级别

查询mysql的事务隔离级别

SELECT @@tx_isolation

设置mysql的隔离级别

set session transaction isolation level REPEATABLE_READ

设置mysql的锁等待超时，单位是秒

set innodb_lock_wait_timeout = 12000;

查看mysql的锁等待超时，单位是秒

show variables like 'innodb_lock_wait_timeout';

 

事务隔离级别	名称	描述	解决了什么	未解决什么
READ_UNCOMMITTED	读未提交	是事务最低的隔离级别，它充许别外一个事务可以看到这个事务未提交的数据。 （1）事务读取数据不加锁。 （2）事务在更新数据时，先对其加行级共享锁S，直到事务结束才释放。	无	脏读、不可重复读、幻读
READ_COMMITTED	读已提交	保证一个事务修改的数据提交后才能被另外一个事务读取。 （1）事务在读取数据时，先对其加行级共享锁S，一旦读完该行，立即释放该行级共享锁S。 （2）事务在更新数据时，先对其加行级排他锁X，直到事务结束才释放。	脏读	不可重复读、幻读
REPEATABLE_READ	可重复读	保障可重复读的两种情况： 1.一致性非锁定读的情况：靠MVCC保障可重复读 select执行的是快照读，读的是数据库记录的快照版本，是不加锁的。读的自己的快照，另外一个事务改了后，自己这边select出来的还是原来的，保证了可重复读。 2.锁定读的情况：靠锁保障可重复读 update、delete、SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE都是要加锁的 （1）事务在读取数据时，先对其加行级共享锁S，直到事务结束才释放。 （2）事务在更新数据时，先对其加行级排他锁X，直到事务结束才释放。 从该级别才开始加入间隙锁	脏读、不可重复读	幻读
SERIALIZABLE	串行	事务被处理为顺序执行 （1）事务在读取数据时，先对其加表级共享锁 ，直到事务结束才释放。 （2）事务在更新数据时，先对其加表级排他锁，直到事务结束才释放。	脏读、不可重复读、幻读	无

我在windows上安装的MySQL5.7，它InnoDB 默认事务隔离级别为可重复读（重复读取，RR）

三、脏读、不可重复读、幻读解释

1.脏读

读取到未提交事务修改的数据。

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

例子:

Mary的原工资为1000,财务人员将Mary的工资改为了8000，但未提交事务
与此同时，Mary正在读取自己的工资.Mary发现自己的工资变为了8000，欢天喜地！ （脏读）
而财务发现操作有误，而回滚了事务,Mary的工资又变为了1000.

2.不可重复读

读取到已提交事务修改的数据。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

例子：

在事务1中，Mary 读取了自己的工资为1000,操作并没有完成 .
在事务2中，这时财务人员修改了Mary的工资为2000,并提交了事务.
在事务1中，Mary 再次读取自己的工资时，工资变为了2000.

3.幻读

读取到新插入的数据。

第一个事务对一个表中的全部数据行进行了修改。同时，第二个事务向表中插入一行新数据。那么操作第一个事务的用户发现表中还有未修改的数据行。

例子：

目前工资为1000的员工有10人。

事务1,读取所有工资为1000的员工。
这时事务2向employee表插入了一条员工记录，工资也为1000
事务1再次读取所有工资为1000的员工 共读取到了11条记录

四、事务的三级*协议

1.一级*协议：事务T中如果对数据R有写操作，必须在这个事务中对R的第一次读操作前对它加X锁，直到事务结束才释放事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。

2.二级*协议：一级*协议加上事务T在读取数据R之前必须先对其加S锁，读完后方可释放S锁。

3.三级*协议：一级*协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。

满足高级锁则一定满足低级锁。但有个非常致命的地方，一级锁协议就要在第一次读加X锁，直到事务结束。几乎就要在整个事务加写锁了，效率非常低。

三级*协议只是一个理论上的东西，实际数据库常用另一套方法来解决事务并发问题。

五、MVCC

1.简介

MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证。

换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。

2.作用：

1.增强并发性。这样的一来查询就不用等待另一个事务释放锁。

2.在RR可重复读的隔离级别中，保障了单纯的select(不会加锁)的“可重复读”特性

这项技术在数据库领域并不是普遍使用的，其它的数据库产品，以及mysql其它的存储引擎并不支持它。

3.快照读和当前读

快照读：读取的是快照版本，也就是历史版本。如：普通的SELECT

当前读：读取的是最新版本，如：UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE是当前读。

4.锁定读

　　在一个事务中，标准的SELECT语句是不会加锁，但是有两种情况例外。

　　SELECT ... LOCK IN SHARE MODE

　　给记录加设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交

　　SELECT ... FOR UPDATE

　　给记录加排它锁，这种情况下跟UPDATE的加锁情况是一样的

5.一致性非锁定读

事务中的单纯、标准的select读是不加锁的。

而这个单纯的select不加锁的读就是一致性非锁定读

一致性非锁定读就是MVCC来保证的 

consistent read（一致性读），InnoDB用多版本来提供查询数据库在某个时间点的快照。

1.如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照；

2.如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。

Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。

一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。 

6.实现机制

InnoDB会给数据库中的每一行增加三个隐藏字段，它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

每开启一个新事务，事务的版本号就会递增。

于是默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：

SELECT

读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。

INSERT

将当前事务的版本号保存至行的创建版本号

UPDATE

新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号

DELETE

将当前事务的版本号保存至行的删除版本号