MySQL学习笔记（三）事务

事务就是保证一组数据库操作，要么全部成功，要么全部失败。MySQl，事务支持是在引擎层实现的。

隔离性与隔离级别

数据库多个事务同时执行，可能出现脏读(dirty read)/不可重复读(non-repeatable read)/幻读(phantom read)的问题。为了解决这些问题，就有了“隔离级别”的概念。

读未提交（read uncommitted）、读提交(read committed)、可重复读(repeatable read)和串行化(serializable)。

读未提交：事务还没提交，可能被别的事务看到

读提交：事务提交后，才会被其他事务看到

可重复读：事务执行过程看到的数据，和这个事务启动时看到的是一样的。

串行化：对同一行记录，“写”会加“写锁”，“读”加“读锁”。后访问的事务必须等前一个事务执行完成，才能继续。

读未提交：V1 = 2   V2 = 2  V3 = 2

读提交：V1 = 1   V2 = 2  V3 = 2

可重复读：V1 = 1   V2 = 1  V3 = 2

串行化：事务B被锁住，直到A执行完后才继续执行。V1 = 1   V2 = 2  V3 = 2

“读未提交”直接返回记录上的最新值，而“串行化”直接用加锁的方式来避免并行访问。

Oracle数据库的默认隔离级别就是“读提交”。将MySQL的隔离级别设置为“读提交”。
 

mysql> show variables like 'transaction_isolation';

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

| Variable_name | Value |

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

| transaction_isolation | READ-COMMITTED |

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

事务隔离的实现

重点说明“可重复读”

MySQL每条记录更新的时候会同时记录一条回滚操作。记录上面的最新值，通过回滚，都可以得到前一个的状态。

假设一个值1被按顺序改为了2/3/4，回滚日志

当系统里没有比这个回滚日志更早的read-view的时候，删除这个回滚日志。

尽量不要使用长事务。长事务意味着系统里会存在很多很老的事务视图。这个事务提交前，数据库里它可能用到的回滚记录都必须保留，这就导致大量占用存储空间。

长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式

有下面几种：

1.显式启动事务语句，begin 或 start transcation。提交语句是commit。回滚语句是rollback。

2.set autocommit=0 会将线程的自动提交关掉。如果只执行一个select，事务就启动了。直到commit或rollback，或者断开连接。

有些客户端会默认连接成功后先执行一个set autocommit=0,导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

所以建议set autocommit=1，通过显示语句来启动事务。

commit work and chain提交事务并自动启动下一个事务，省去了再次执行begin语句的开销。

你可以在 information_schema 库的 innodb_trx这个表中查询长事务。

比如查找持续时间超过60秒的事务：

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
 

如何避免长事务对业务的影响？

从应用开发端和数据库端来看。

首先，从应用开发端来看：

1、确认是否使用了set autocommit=0。这个确认工作可以在测试环境中展开。把SQL的general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。

2、确认是否有不必要的只读事务。

3、通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

从数据库端来看：

1、监控 information_schema.lnnodb_trx表，设置长事务阈值，超过就报警/或者kill

2、Percona的pt-kill这个工具不错

3、在业务功能测试阶段输出所有的general_log，分析日志行为

4、MySQL5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。