03 | 事务隔离

事务就是要保证数据库一组操作，要么全部成功，要么全部失败。在mysql中，事务是引擎层支实现的，MyISAM不支持事务，InnoDB支持事务。这也是MyISAM被InnoDB取代的重要原因之一。

隔离性与隔离级别

• 读未提交。一个事务还没提交，他做的变更就能被别的事务看到。
• 读已提交。一个事务提交之后，才能被其他事务看到。
• 可重复读。一个事务执行过程中，总是跟这个事务启动时看到的数据是一致的。
• 串行化。当出现读写冲突的时候，后访问的事务必须等待前一个事务执行完成之后，才能继续执行。

在实现上，数据库会创建一个视图，访问的时候以视图的逻辑结果为准。

在“可重复读”级别下，这个视图是在事务启动的时候创建的，整个事务存在期间都用这个视图。

在“读提交”级别下，这个视图是在每个SQL语句开始执行的时候创建的。

在“读未提交”级别下，直接返回记录上的最新值。

在“串行化”级别下，直接用加锁的方式来避免并行访问。

Oracle的默认级别是“读提交”。

Mysql的默认级别是“可重复读”。

查看当前数据库默认的事务隔离级别：

show variables like 'transaction_isolation';

事务隔离的实现

每条update语句执行的同时，都会同时记录一条回滚日志。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从1按顺序改成了2/3/4，在回滚日志里会有类似下面的记录。

当前该值为4，不同时刻启动的事务会有不同的read-view。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。

当没有事务再需要用到这些回滚日志的时候，相应的回滚日志会被删除。

事务的启动方式

• 显式启动：begin或者start transaction。配套的提交语句是commit，回滚语句是rollback。
• set autocommit=0，将线程的自动提交关掉。意味着执行一个语句，事务就启动了，而且不会自动提交。这个事务会一直存在，直到主动执行commit或者rollback。

查询长事务

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

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

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

从应用开发端和数据库端两个方面来看问题。

• 应用开发端

  1. 确定auto_commit是否为1.
  2. 避免出现不必要的事务，如都是select语句，完全没必要使用事务。
  3. 设置合理的SET MAX_EXECUTION_TIME来控制每个语句的最长执行时间。

• 数据库端

  1. 监控information_schema.Innodb_trx表，设置长事务阈值，超过阈值就告警或kill。

     mysql -N -uroot -p'密码' -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join 
     information_schema.PROCESSLIST b 
     on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep' 
     inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
     inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | while read A B C D E F G H
     do 
     #echo $C 
     if [ "$C" -gt 5 ] 
     then 
     echo date "+%Y-%m-%d %H:%M:%S" 
     echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H" 
     fi 
     done >> /tmp/longtransaction.txt
     

  2. 使用pt-kill工具，kill长时间的异常事务。参考：www.cnblogs.com/bjx2020/p/9…

  3. 在业务开发测试阶段，输出所有的general_log，分析日志行为，提早发现。

     mysql>set global general_log_file='/tmp/general.log';    #设置路径
     mysql>set global general_log=on;    # 开启general log模式