mysql 大事物判断方法 mysql大事务影响

转载

数据大侠客 2023-12-08 16:09:06

文章标签 mysql 大事物判断方法数据库回滚死锁检测死锁 文章分类 MySQL 数据库

什么是大事务？

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

如何查询大事务？

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

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

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

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

当系统中不同事务之间出现循环资源依赖，涉及的事务都在等待别的事务释放资源时，就会导致这几个事务都进入无限等待的状态，比如下面这个场景：

mysql 大事物判断方法 mysql大事务影响_mysql 大事物判断方法

这时候，事务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，在回滚日志里面就会有类似下面的记录。

mysql 大事物判断方法 mysql大事务影响_死锁检测_02

当前值是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购买电影票。我们简化一点，这个业务需要涉及到以下操作:

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

基于死锁检测

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

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

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

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