MYSQL学习笔记--事务隔离

事务的属性包括ACID。
Atomicity：原子性。一个事务的全部操作要么全部成功，要么全部失败回滚。比如转帐，从A扣100，B增加100，这两个操作要么全部成功，如果有一个失败就要回滚，不然就会出现A扣了100，B却没有加上100的情况。
Consistency： 一致性。 即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。还是拿转账为例A 有 500 元，B 有 300 元，如果在一个事务里 A 成功转给 B50 元，那么不管并发多少，不管发生什么，只要事务执行成功了，那么最后 A 账户一定是 450 元，B 账户一定是 350 元。
Isolation：隔离性。所谓的隔离性就是说，事务与事务之间不会互相影响，一个事务的中间状态不会被其他事务感知。
Durability：持久性。事务完成了，那么事务对数据所做的变更就完全保存在了数据库中，即使发生停电，系统宕机也是如此。
隔离级别
SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
隔离得越严实，效率就会越低。
假设有下面一张表：

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

有如下两个事务：

在四种隔离级别下的V1，V2，V3分别是：
读未提交
V1 2
V2 2
V3 2
读提交
V1 1
V2 2
V3 2
可重复读
V1 1
V2 1
V3 2
串行化
V1 1
V2 1
V3 2
实现上，利用视图的逻辑结果为准备。
可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
“串行化”隔离级别下直接用加锁的方式来避免并行访问。
配置的方式：

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

事务隔离的实现
以可重复读为例。
每次更新操作都会记录一条回滚操作。
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

不同时刻启动的事务，看到的值是不一样的。数据库可以存在多个版本就是数据库的多版本并发控制（MVCC）。
回滚日志的删除，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。其实就是当系统没有比回滚日志更早的read-view.
基于上面可以知道长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
事务的启动方式
MySQL 的事务启动方式有以下几种：
1 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2 set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
意外的长连接，执行了set autocommit=0的命令，并且在长连接中。
推荐是set autocommit=1 ，显示执行启动事务语句。
担忧多一次交互的问题，可以使用ommit work and chain语法。在set autocommit=
在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

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，分析日志行为提前发现问题；如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。