MySQL -- SQL事务

1，事务的ACID

1. 原子性（Atomicity）：是指事务是一个不可分割的单元，事务中的操作要么全发生，要么都不发生；
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态；
3. 隔离性（Isolation）：是指一个事务的执行不能被其他事务干扰，即一个事务的内部操作及使用的数据对并发的其他操作的事务是隔离的，并发执行的各个事务之间不能相互干扰；
4. 持久性（Durability）：是指一个事务一旦被提交，那么他对数据库中数据的改变是永久性的，即使数据库故障都不应该对其有任何影响；

1.1 事务的创建

1. 隐式事务：事务没有明显的开启和结束的标记，如：insert、update、delete语句的执行；
2. 显式事务：事务具有明显的开启和结束的标记，但必须关闭事务的自动提交，手动去触发事务开启、关闭，如：用set autocommit=0关闭事务的自动提交（关闭只对当前事务有效），用commit提交事务，用rollback回滚事务；

1.2 常见数据库读错误

1. 脏读：对于两个事务T1，T2，T1读取了T2更新但没有提交的数据，之后若T2回滚了，T1读取的内容就是临时且无效的数据（更新时）；
2. 不可重复读：对于两个事务T1，T2，T1读取了一字段，然后T2修改了该字段，之后T1再去读取该字段发现值不一样了；
3. 幻读：对于两个事务T1，T2，T1从表中读取一些行，然后T2在表中插入了新的行，之后T1再去读取的时候发现行多了（插入时）；

2，事务的隔离级别

隔离级别	表述
read uncommited（读未提交数据）	允许事务读取其他事务没有提交的变更，可能会出现脏读、不可重复读、幻读
read commited（读已提交数据）	只允许事务读取其他事务已经提交的变更，可以避免脏读，但不能避免不可重复读、幻读
repeatable read（可重复读）	确保事务可以多次从一个字段中读取相同的值，在本次事务持续期间，禁止其他事务对这个字段进行更改，可以避免脏读、不可重复读，但可能出现幻读
serializable（串行化）	确保事务可以多次从一个字段中读取相同的值，在本次事务持续期间，禁止其他事务对该整张表的插入、删除、修改，可以避免所有并发问题，但性能低

注：

1. mysql默认隔离级别为repeatable read（可重复读）；
2. oracle默认隔离级别为read commited（读已提交）
3. delete支持事务回滚，truncate不支持事务回滚
4. repeatable read相当于行级锁，serializable相当于表级锁；

回滚点

set autocommit = 0;
start transaction ;
delete from user where id = 10;
savepoint a;-- 设置名称为a的保存点
delete from user where id = 11;
rollback to a;-- 回滚到保存点a，此时10呗删除，11得以回滚

3，事务实现

3.1 隔离实现

1. 在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
2. 假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。
   
3. 当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的 多版本并发控制（MVCC）。
4. 对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。
5. 现在有另外一个事务正在将4改成5，这个事务跟read-view A、B、C对应的事务是不会冲突的。

3.2 可重复读实现

可重复读的核心就是一致性读（consistent read）；事务更新数据的时候，只能用当前读。
如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

4，长事务

1. 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间；
2. 建议set autocommit=1，自动结束事务，尽量避免长事务；
3. 长事务还占用锁资源，也可能拖垮整个库；
4. 在information_schema库的innodb_trx这个表中查询长事务；

-- 查找持续时间超过60s的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

参考链接：
1，MySQL基础
2，文章节选自 极客时间《MySQL45讲》 如有不当，联系侵删