MySQL事务

Q：事务的概念是什么？
事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。
MyISAM引擎不支持事务，InnoDB引擎支持事务。

Q：事务的四大特性(ACID)
A：atomicity 原子性：事务是由一个或一组相互关联的SQL语句组成，这些语句被认为是一个不可分割的单元，要么都成功要么都失败。
C：consistency 一致性：对于数据库的修改是一致的，即多个用户查的数据是一样的。一致性主要由mysql的日志机制处理，他记录数据的变化，为事务恢复提供跟踪记录。
I：isolation 隔离性：每个事务都有自己的空间，和其他发生在系统中的事务隔离开来，而且事务的结果只有在他完全被执行时才能看到。
D：durability 持久性：当提交了这个事务之后，对数据的修改更新就是永久的。

Q：隔离性的四个级别

1、读未提交（read uncommitted）
一个事务还没提交时，它做的变更就能被别的事务看到。会出现"脏读"
2、读提交（read committed）
一个事务提交之后，它做的变更才会被其他事务看到。会出现"不可重复读"
3、可重复读（repeatable read）
一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。未提交变更对其他事务是不可见的。会出现"幻读"
4、串行化（serializable）
对同一行记录，"写"会加"写锁"，"读"会加"读锁"。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才继续执行。
这四种隔离级别，并行性能依次降低，安全性依次提高。

Q：可重复读的使用场景，对账的时候应该很有用的。
假设管理一个个人银行账户表，一个表存了每个月月底的余额，一个表存了账单明细。这时要做数据校对，也就是判断上个月的余额和当前余额的差额，
是否与本月的账单明细一致。在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。

Q：事务隔离的实现
数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
读未提交：直接返回记录上的最新值，没有视图概念。
读提交：视图在每个SQL语句开始执行的时候创建。
可重复读：视图在事务启动时创建，整个事务存在期间都用这个视图。
串行化：直接用加锁方式来避免并行访问。

Q：并发版本控制(MVCC)的概念是什么，怎么实现的？

MySQL中每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制(MVCC)。
对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。
即使现在有另外一个事务正在将4改成5，这个事务跟read-view A、B、C对应的事务是不会冲突的。

Q：回滚日志什么时候删除？
当没有事务再需要用到这些回滚日志时，回滚日志会被删除。也就是当系统里没有比这个回滚日志更早的read-view时，删除回滚日志。

Q：事务的启动方式
1、显示启动事务语句，begin或start transaction。配套的提交语句是commit，回滚语句是rollback。
2、set autocommit=0，这个命令会将这个线程的自动提交关闭。意味着如果只执行一个select语句，这个事务就启动了，并且不会自动提交，
这个事务持续存在直到你主动执行commit或rollback语句，或者断开连接。
建议使用set autocommit=1，通过显示语句的方式来启动事务。在autocommit为1的情况下，用begin显示启动的事务，如果执行commit则提交事务。
如果执行commit work and chain，则提交事务并自动启动下一个事务。

Q：如何查询各个表中的长事务？
// 查找持续时间超过60s的事务
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

Q：使用长事务的弊端？为什么使用长事务可能拖垮整个库？
1、长事务意味着系统里面会存在很老的事务视图，在这个事务提交前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
2、长事务还占用锁资源，可能会拖垮整个库。

Q：如何避免长事务对业务的影响？
这个问题，我们可以从应用开发端和数据库端来看
应用开发端：
1、确认是否使用了set autocommit=0，目标是使用set autocommit=1
2、确认是否有不必要的只读事务。把好几个select语句放到了事务中，这种只读事务可以去掉。
3、根据业务本身的预估，通过 set max_execution_time命令来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
数据库端：
1、监控information_schema.innodb_trx表，设置长事务阈值，超过就报警或者kill
2、Percona 的 pt-kill 这个工具不错，推荐使用
3、在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题
4、（MySQL5.6或者之后的版本）把innodb_undo_tablespaces设置成2(或者更大的值)。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoD表的语句，事务才真正启动。一致性视图是在执行第一个快照读语句时创建的。
start transaction with consistent snapshot命令可以马上启动一个事务，在执行该命令时创建一致性视图。

MySQL里有两个"视图"的概念：
一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view...，而它的查询方法与表一样。
另一个是InnoDB在实现MVCC时用到的一致性读视图(consistent read view)，用于支持RC(读提交)和RR(可重复读)隔离级别的实现。

Q：事务("快照")在MVCC里是怎么工作的？
1、每个事务都有一个唯一的事务ID，叫作transaction id（严格递增）。
2、数据表中的一行记录，可能有多个版本(row)，每个版本有自己的row trx_id。
每行数据都有多个版本，每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。
同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
3、InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在"活跃"的所有事务ID。"活跃"指的就是启动了但还没有提交。

InnoDB利用了"所有数据都有多个版本"的这个特性，实现了"秒级创建快照"的能力。
一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
1、版本未提交，不可见
2、版本已提交，但是是在视图创建后提交的，不可见
3、版本已提交，而且是在视图创建前提交的，可见

更新数据都是先读后写的，而这个读，只能读当前的值，称为"当前读"(current read)。
除了update语句外，select语句如果加锁，也是当前读。

// 加读锁（S锁，共享锁）
mysql> select k from t where id=1 lock in share mode;
// 加写锁（X锁，排他锁）
mysql> select k from t where id=1 for update;

// 查看隔离级别
mysql> show variables like "tx_isolation";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)