什么是大事务?

简单来说就是那些运行时间比较长,操作的数据比较多的事务

如何查询大事务?

以查询执行时间超过10秒的事务为例:

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

大事务一般会对数据库造成什么问题?

锁定数据过多,容易造成大量的死锁和锁超时

当系统中不同事务之间出现循环资源依赖,涉及的事务都在等待别的事务释放资源时,就会导致这几个事务都进入无限等待的状态,比如下面这个场景: 1 这时候,事务A在等待事务B释放id=2的行锁,而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放,就是进入了死锁状态

首先我们知道,有两种策略可以处理死锁:

  • 等待死锁超时。超时时间(innodb_lock_wait_timeout)默认是50s,这时间可以说真的是太长了,但是如果改小了吧,又可能会影响到本可以正常消除的死锁
  • 死锁检测。死锁检测的配置默认是开启的。死锁检测就是每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了死锁

但是死锁检测可能会存在一个问题: 假如所有事务都要更新同一行的时候。每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是 O(n)的操作。假设有1000个并发线程要同时更新同一行,那么死锁检测操作就是100万这个量级 的。虽然最终检测的结果是没有死锁,但是这期间要消耗大量的CPU资源。因此,你就会看到 CPU利用率很高,但是每秒却执行不了几个事务。

回滚记录占用大量存储空间,事务回滚时间长

在MySQL中,实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。 假设一个值从1被按顺序改成了2、3、4,在回滚日志里面就会有类似下面的记录。 1 当前值是4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的read-view。如图中看到的,在视图A、B、C里面,这一个记录的值分别是1、2、4,同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)。对于read-view A,要得到1,就必须将当前值依次执行图中所有的回滚操作得到。 同时你会发现,即使现在有另外一个事务正在将4改成5,这个事务跟read-view A、B、C对应的 事务是不会冲突的。 你一定会问,回滚日志总不能一直保留吧,什么时候删除呢?答案是,在不需要的时候才删除。 也就是说,系统会判断,当没有事务再需要用到这些回滚日志时,回滚日志会被删除。 什么时候才不需要了呢?就是当系统里没有比这个回滚日志更早的read-view的时候,换一种说法就是在这些事物提交之后。

执行时间长,容易造成主从延迟

因为主库上必须等事务执行完成才会写入binlog,再传给备库。所以,如果一个主库上的语句执行10分钟,那这个事务很可能就会导致从库延迟10分钟

解决方案

基于两阶段锁协议

两阶段锁协议是什么? 在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放

基于两阶段锁协议我们可以做这样的优化: 如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

假设你负责实现一个电影票在线交易业务,顾客A要在影院B购买电影票。我们简化一点,这个 业务需要涉及到以下操作:

  1. 从顾客A账户余额中扣除电影票价;
  2. 给影院B的账户余额增加这张电影票价; 3. 记录一条交易日志。 也就是说,要完成这个交易,我们需要update两条记录,并insert一条记录。当然,为了保证交易的原子性,我们要把这三个操作放在一个事务中。那么,你会怎样安排这三个语句在事务中的 顺序呢? 试想如果同时有另外一个顾客C要在影院B买票,那么这两个事务冲突的部分就是语句2了。因为 它们要更新同一个影院账户的余额,需要修改同一行数据。 根据两阶段锁协议,不论你怎样安排语句顺序,所有的操作需要的行锁都是在事务提交的时候才 释放的。所以,如果你把语句2安排在最后,比如按照3、1、2这样的顺序,那么影院账户余额 这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待,提升了并发度。

基于死锁检测

想要解决死锁检测的问题那么就只能控制O(n)的数量,当同一行并发数小的时候死锁检测的成本就会很低了

不过这个并发数还挺不好控制:

  1. 分布式系统中客户端数量是不确定的,所以不能在客户端做限制
  2. 在MySQL端做的话需要修改源码,这个就非大牛而不可为了
  3. 参考JDK1.7的ConcurrentHashMap的分段锁设计,将一行数据改成逻辑上的多行数据来减少锁冲突 其中第三个方案还是有点意思的,接着以影院的账户为例,可以将一个账号信息放在多条记录上,比如10个记录,影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候,随机选其中一条记录来加。这样每次冲突概率变成原来的1/10,可以减少锁等待个数,也就减少了死锁检测的CPU消耗。

这个方案看上去是无损的,但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少,比如退票逻辑,那么这时候就需要考虑当一部分行记录变成0的时候,代码要有特殊处理。

基于事务的隔离级别

我们知道MySQL的事务隔离级别默认是可重复读,在这个隔离级别下写数据的时候会有这些问题:

  1. 如果有索引(包括主键索引)的时候,以索引列为条件更新数据,会存在间隙锁、行锁、下一键锁的问题,从而锁住一些行
  2. 如果没有索引,更新数据时会锁住整张表

但是如果把隔离级别改为读提交就不存在这两个问题了,每次写数据只会锁一行

但同时,你要解决可能出现的数据和日志不一致问题,需要把binlog格式设置 为row

关于为什么要设置binlog可以参考这篇文章为什么要把MySQL的binlog格式修改为row

其它

  1. 一些只读的操作就没有必要开启事物了
  2. 通过SETMAX_EXECUTION_TIME命令, 来控制每个语句执行的最长时间,避免单个语句意外执行太长时间
  3. 监控 information_schema.Innodb_trx表,设置长事务阈值,超过就报警/或者kill
  4. 在业务功能测试阶段要求输出所有的general_log,分析日志行为提前发现问题
  5. 设置innodb_undo_tablespaces值,将undo log分离到独立的表空间。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。

目录