MySQL数据库事务
一、概述
事务(Transaction)是操作数据库中某个数据项的一个程序执行单元(unit)。
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
二、事务特性(ACID)
1、原子性(Atomicity)
事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。事务在执行过程中出错，会回滚到事务开始之前的状态，以此来保证事务的完整性。类似于原子在物理上的解释：指化学反应不可再分的基本微粒，原子在化学反应中不可分割 。
2、一致性(Consistency)
事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。个人理解类似于物理上的能量守恒。
3、隔离性(Isolation)
隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。比如A向一张银行卡转账，避免在同一时间过多的操作导致账户金额的缺损，所以在A转入结束之前是不允许其他针对此卡的操作的。
4、持久性(Durability)
事务的对数据的影响是永久性的。一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。事务一旦完成就是不可逆的，在数据库的操作上表现为事务一旦完成就是无法回滚的。
三、本地事务实现方案
大多数场景下，我们的应用都只需要操作单一的数据库，这种情况下的事务称之为本地事务(Local Transaction)。本地事务的ACID特性是数据库直接提供支持。
为了达成本地事务，MySQL做了很多的工作，比如回滚日志，重做日志，MVCC，读写锁等。
MySQL数据库的事务实现原理
以MySQL的InnoDB(InnoDB是MySQL的一个存储引擎)为例，介绍一下单一数据库的事务实现原理。
InnoDB是通过日志和锁来保证的事务的ACID特性，具体如下：
- 通过数据库锁的机制，保障事务的隔离性；
- 通过
Redo Log(重做日志)来，保障事务的持久性； - 通过
Undo Log(撤销日志)来，保障事务的原子性； - 通过
Undo Log(撤销日志)来，保障事务的一致性；
Undo Log如何保障事务的原子性呢？
具体的方式为：在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方称为Undo Log)，然后进行数据的修改。如果出现了错误或者用户执行了Rollback语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
Redo Log如何保障事务的持久性呢？
具体的方式为：Redo Log记录的是新数据的备份(和Undo Log相反)。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到崩溃之前的状态。
四、事务并发问题
在多个事务并发操作时，数据库中会出现下面几种问题：脏读，幻读，不可重复读等。
4.1 一类丢失更新
一类丢失更新也叫回滚丢失(Lost Update)：撤销一个事务的时候，把其它事务已提交的更新数据覆盖了。这是完全没有事务隔离级别造成的。如果事务1被提交，另一个事务被撤销，那么会连同事务1所做的更新也被撤销。
解决办法：SQL92没有定义这种现象，标准定义的所有隔离界别都不允许第一类丢失更新发生。
4.2 脏读
脏读(Dirty Read)又称无效数据读出：一个事务读取另外一个事务还没有提交的数据。
例如：事务A读取的数据，事务B对该数据进行修改还未提交数据之前，事务A再次读取数据会读到事务B已经修改后的数据，如果此时事务B进行Rollback或再次修改该数据然后提交，事务A读到的数据就是脏数据，这个情况被称为脏读。
解决办法：把数据库的事务隔离级别调整到read_committed。
4.3 不可重复读
同时操作，事务一分别读取事务二操作时和提交后的数据，读取的记录内容不一致叫不可重复读(Non-Repeatable Read)。
例如：事务A读取某些数据后，事务B读取并修改了该数据，事务A为了对读取值进行检验而再次读取该数据，便得到了不同的结果。
解决办法：把数据库的事务隔离级别调整到repeatable_read。
4.4 第二类丢失更新
又称覆盖丢失/两次更新问题(Second Lost Update)，它和不可重复读本质上是同一类并发问题，通常将它看成不可重复读的特例。当两个或多个事务查询相同的记录，然后各自基于查询的结果更新记录时会造成第二类丢失更新问题。每个事务不知道其它事务的存在，最后一个事务对记录所做的更改将覆盖其它事务之前对该记录所做的更改。
解决办法：可以通过悲观锁和乐观锁(推荐)解决。
4.5 幻读
和可重复读类似，但是事务二的数据操作仅仅是插入或删除，不是修改数据，读取的记录数量前后不一致。
例如：事务A进行范围查询时，事务B中新增了满足该范围条件的记录，当事务A再次按该条件进行范围查询，会查到在事务B中提交的新的满足条件的记录，该记录被称作幻行(Phantom Row)。
解决办法：把数据库的事务隔离级别调整到serializable_read。
幻读和不可重复度的区别：
- 幻读 ：在同一事务中，相同条件下，两次查询出来的记录条数不一样；
- 不可重复读 ：在同一事务中，相同条件下，两次查询出来的数据信息不一样；
注：脏读、不可重复读、幻读三种问题看似不太好理解，脏读侧重的是数据的正确性。不可重复读侧重于数据的修改，幻读侧重于数据的新增和删除。
五、事务隔离级别
为了解决数据库中事务并发所产生的问题，在标准SQL规范中，定义了四种事务隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。
低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。
MySQL事务隔离级别 ：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
通过修改MySQL系统参数来控制事务的隔离级别，在MySQL8中该参数为transaction_isolation ，在MySQL5中该参数为tx_isolation:
MySQL8:
-- 查看系统隔离级别：
SELECT @@global.transaction_isolation;
-- 查看当前会话隔离级别
SELECT @@transaction_isolation;
-- 设置当前会话事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 设置全局事务隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
了解了高并发下对事务的影响。事务的四种隔离级别就是对以上三种问题的解决方案。
事务的四个隔离级别：
- 读未提交(read uncommitted) ：所有事务都可以看到其他事务未提交的修改。一般很少使用；
- 提交读(read committed) ：
Oracle默认隔离级别，事务之间只能看到彼此已提交的变更修改； - 可重复读(repeatable read) ：
MySQL默认隔离级别，同一事务中的多次查询会看到相同的数据行；可以解决不可重复读，但可能出现幻读； - 可串行化(serializable) ：最高的隔离级别，事务串行的执行，前一个事务执行完，后面的事务会执行。读取每条数据都会加锁，会导致大量的超时和锁争用问题；
| 隔离级别 | 脏读 | 不可重复度 | 幻读 | 加锁读 |
|---|---|---|---|---|
读未提交(read-uncommitted) |
是 | 是 | 是 | 否 |
读提交(read-committed) |
否 | 是 | 是 | 否 |
可重复读(repeatable-read) |
否 | 否 | 是 | 否 |
可串行化(serializable) |
否 | 否 | 否 | 是 |
问：如何保证
repeatable read级别绝对不产生幻读？答 ：在
SQL中加入for update(排他锁)或lock in share mode(共享锁)语句实现。就是锁住了可能造成幻读的数据，阻止数据的写入操作。
5.1 SQL演示四种隔离级别
mysql版本：5.7
存储引擎：InnoDB
CREATE TABLE `tb_bank`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) COLLATE utf8_bin DEFAULT NULL,
`account` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (1, '小明', 1000);
5.1.1 演示--read-uncommitted的脏读
5.1.1.1 read-uncommitted导致的脏读
场景：session1要转出200元，session2转入100元。基数为1000。顺利完成正确的结果应该是900元。但是我们假设session2转入因为某种原因事务回滚。这时正确的结果应该是800元。
演示步骤：
①. 新建两个session(会话，在navicat中表现为两个查询窗口，在mysql命令行中也是两个窗口)，分别执行
select @@tx_isolation; --查询当前事务隔离级别
set session transaction isolation level read uncommitted; --将事务隔离级别设置为 读未提交
②. 两个session都开启事务
start transaction; --开启事务
③. session1和session2：证明两个操作执行前账户余额为1000
select * from tb_bank where id = 1; --查询结果为1000
④. session2：此时假设session2的更新先执行。
update tb_bank set account = account + 100 where id=1;
⑤. session1：在session2 commit之前session1开始执行。
select * from tb_bank where id = 1; --查询结果：1100
⑥. session2：因为某种原因，转入失败，事务回滚。
rollback; --事务回滚
commit; --提交事务
⑦. 这时session1开始转出，并且session1觉得⑤中查询结果1100就是正确的数据。
update tb_bank set account = 1100 - 200 where id = 1;
commit;
⑧. session1和session2查询结果
select * from tb_bank where id = 1; --查询结果：900
这时我们发现因为session1的脏读造成了最终数据不一致。正确的结果应该为800；
到此我们怎么避免脏读呢，将事务的隔离性增加一个级别到read-commit。
5.1.1.2 read-commit解决脏读
重置数据，使数据恢复到account = 1000
①. 新建两个session，分别设置
set session transaction isolation level read committed; --将隔离级别设置为 不可重复读
重复执行(1)中的②③④步
⑤. session1执行查询
select * from tb_bank where id = 1; --查询结果为1000，这说明 不可重复读 隔离级别有效的隔离了两个会话的事务。
这时我们发现，将事务的隔离升级为read-committed;后有效的隔离了两个事务，使得session1中的事务无法查询到session2中事务对数据的改动。有效的避免了脏读。
5.1.2 演示--read-committed的不可重复读
5.1.2.1 read-commit的不可重复读
重置数据，使数据恢复到account = 1000
所谓的不可重复读就是说，一个事务不能读取到另一个未提交的事务的数据，但是可以读取到提交后的数据。这个时候就造成了两次读取的结果不一致了。所以说是不可重复读。
read committed隔离级别下，每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次select也可以看到其它已commit事务所作的更改
场景：session1进行账户的查询，session2进行账户的转入100。
session1开启事务准备对账户进行查询然后更新，这时session2也对该账户开启了事务进行更新。正确的结果应该是在session1开启事务以后查询读到的结果应该是一样的。
①. 新建两个session，分别设置
set session transaction isolation level read committed;
②. session1和session2分别开启事务
start transaction;
③. session1第一次查询：
select * from tb_bank where id=1; --查询结果：1000
④. session2进行更新：(更新完需要提交commit，2020-06-27补充，read-committed本质就是读已提交，也就是可以读到其它事务已提交的更新)
update tb_bank set account = account + 100 where id = 1;
select * from tb_bank where id = 1; --查询结果：1100
⑤. session1第二次查询：
select * from tb_bank where id = 1; --查询结果：1100。和③中查询结果对比，session1两次查询结果不一致。
查看查询结果可知，session1在开启事务期间发生重复读结果不一致，所以可以看到read commit事务隔离级别是不可重复读的。显然这种结果不是我们想要的。
5.1.2.2 repeatable-read可重复读
重置数据，使数据恢复到account = 1000
①. 新建两个session，分别设置：
set session transaction isolation level repeatable read;
重复(1)中的②③④
⑤. session1第二次查询：
select * from tb_bank where id = 1; --查询结果为：1000
从结果可知，repeatable-read的隔离级别下，多次读取结果是不受其他事务影响的。是可重复读的。到这里产生了一个疑问，那session1在读到的结果中依然是session2更新前的结果，那session1中继续转入100能得到正确的1200的结果吗？
继续操作：
⑥. session1转入100：
update tb_bank set account = account + 100 where id = 1;
到这里感觉自己被骗了，锁，锁，锁。session1的更新语句被阻塞了。只有session2中的update语句commit之后，session1中才能继续执行。session的执行结果是1200，这时发现session1并不是用1000+100计算的，因为可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读(历史版本)。insert、update和delete会更新版本号，是当前读(当前版本)。
5.1.3 演示--repeatable-read的幻读
在业务逻辑中，通常我们先获取数据库中的数据，然后在业务中判断该条件是否符合自己的业务逻辑，如果是的话，那么就可以插入一部分数据。但是mysql的快照读可能在这个过程中会产生意想不到的结果。
场景模拟：
session1开启事务，先查询有没有小张的账户信息，没有的话就插入一条。这是session2也执行和session1同样的操作。
准备工作：插入两条数据
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (2, '小红', 800);
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (3, '小磊', 6000);
5.1.3.1 repeatable-read的幻读
①. 新建两个session都执行
set session transaction isolation level repeatable read;
start transaction;
select * from tb_bank; --查询结果：(这一步很重要，直接决定了快照生成的时间)
结果都是：
| id | name | account |
|---|---|---|
| 1 | 小明 | 1200 |
| 2 | 小红 | 800 |
| 3 | 小磊 | 6000 |
②. session2插入数据
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (4, '小张', 8000);
select * from tb_bank;
| id | name | account |
|---|---|---|
| 1 | 小明 | 1200 |
| 2 | 小红 | 800 |
| 3 | 小磊 | 6000 |
| 4 | 小张 | 8000 |
结果数据插入成功。此时session2提交事务
commit;
③. session1进行插入
插入之前我们先看一下当前session1是否有id=4的数据
select * from tb_bank;
| id | name | account |
|---|---|---|
| 1 | 小明 | 1200 |
| 2 | 小红 | 800 |
| 3 | 小磊 | 6000 |
结果session1中没有该条记录，这时按照我们通常的业务逻辑，此时应该是能成功插入id=4的数据。继续执行：
insert into `demo`.`tb_bank`(`id`, `name`, `account`) values (4, '小张', 8000);
结果如下：
1062 - Duplicate entry '4' for key 'PRIMARY'
结果插入失败，提示该条已经存在，但是我们查询里面并没有这一条数据啊。为什么会插入失败呢？
因为①中的select语句生成了快照，之后的读操作(未加读锁)都是进行的快照读，即在当前事务结束前，所有的读操作的结果都是第一次快照读产生的快照版本。疑问又来了，为什么②步骤中的select语句读到的不是快照版本呢？因为update语句会更新当前事务的快照版本。
5.1.3.2 repeatable-read利用当前读解决幻读
重复(1)中的①②
③. session1进行插入
插入之前我们先看一下当前session1是否有id=4的数据
select * from tb_bank;
结果session1中没有该条记录，这时按照我们通常的业务逻辑，此时应该是能成功插入id=4的数据。
select * from tb_bank lock in share mode; --采用当前读
结果：发现当前结果中已经有小张的账户信息了，按照业务逻辑，我们就不在继续执行插入操作了。
这时我们发现用当前读避免了repeatable-read隔离级别下的幻读现象。
5.1.4 serializable隔离级别
在此级别下我们就不再做serializable的避免幻读的sql演示了，毕竟是给整张表都加锁的。
六、当前读和快照读
6.1 快照读：即一致非锁定读。
InnoDB存储引擎下，查询语句默认执行快照读。RR隔离级别下一个事务中的第一次读操作会产生数据的快照。update，insert，delete操作会更新快照。
四种事务隔离级别下的快照读区别：
read-uncommitted和read-committed级别：每次读都会产生一个新的快照，每次读取的都是最新的，因此RC级别下select结果能看到其他事务对当前数据的修改，RU级别甚至能读取到其他未提交事务的数据。也因此这两个级别下数据是不可重复读的。repeatable-read级别：基于MVCC的并发控制，并发性能极高。第一次读会产生读数据快照，之后在当前事务中未发生快照更新的情况下，读操作都会和第一次读结果保持一致。快照产生于事务中，不同事务中的快照是完全隔离的。serializable级别：从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁(S锁)，写加写锁(X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降。(锁表，不建议使用)
6.2 当前读：即一致锁定读。
如何产生当前读
- select ... lock in share mode
- select ... for update
- update，insert，delete操作都是当前读。
读取之后，还需要保证当前记录不能被其他并发事务修改，需要对当前记录加锁。①中对读取记录加S锁(共享锁)，②③X锁(排它锁)。
6.3 update，insert，delete操作为什么都是当前读？
简单来说，不执行当前读，数据的完整性约束就有可能遭到破坏。尤其在高并发的环境下。
分析update语句的执行步骤：update table set ... where ...;
InnoDB引擎首先进行where的查询，查询到的结果集从第一条开始执行当前读，然后执行update操作，然后当前读第二条数据，执行update操作......所以每次执行update都伴随着当前读。delete也是一样，毕竟要先查到该数据才能删除。insert有点不同，insert操作执行前需要执行唯一键的检查。
