Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

问题:生成环境统计数据出现了丢失,经过排查发现日志里面有死锁日志

错误原因:

  1. 死锁问题
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  2. 锁等待问题
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

以上两个都是在使用事务中可能会碰到的问题:下面我们模拟以上两种场景

复现死锁所使用的事务如下:

mysql 版本:8.0.32
Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

本地创建两个表:

// account 表
CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `account` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
// test 表
CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `age` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `age` (`age`)
) ENGINE=InnoDB CHARSET=utf8mb3;

1.死锁场景的复现

首先准备两个 session 窗口连接mysql,我们暂且称左边为事务1 右侧为事务2

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟
我们现在事务1执行:

start transaction;

事务2也执行上述操作如图:

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

我们在事务2内执行:

update test set age = 30 where id = 2;

事务1执行:

update account set account = 800 where id = 1;

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

接着继续在事务2执行:

update account set account = 1500 where id = 1;

事务1执行:

update test set age = 50 where id = 2;

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

这个时候我们看到事务1出现了错误:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

我们给事务2执行commit 操作

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

我们看到本来事务2等待中的也拿到了锁,执行了 sql,出现的效果就是事务1回滚,事务2执行成功,会造成数据不一致

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

此时我们看到数据库数据已经更新成功,事务2正常提交了

这个是我们在并发事务执行对同一行数据进行了更新操作,在事务上产生了排他锁【行锁】,我们是根据主键ID测试的,也可以用唯一索引,或者联合索引进行更新,如果where 内没有执行到索引,可能会升级为表锁,会严重影响性能

上述测试流程描述:

  1. 事务2 执行 test 表ID = 2 的更新
  2. 事务1执行 account 表 id = 1 更新
  3. 事务2执行 account 表 id = 1 更新,产生了锁等待,默认超时时间是 50s
  4. 事务1 执行 test 表ID= 2 的更新 发生死锁,释放了行锁,事务2正常执行(发生在等待超时时间内)
  5. 事务2 正常提交事务
    上面过程将会帮我们复现死锁产生流程

2.锁等待测试

我们再事务1 执行

update test set age = 50 where id =2;

事务2执行:

update test set age=60 where id =2;

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟
等待50秒之后我们不提交事务,看到等待的那个事务1 出现了Lock wait timeout exceeded

Mysql 并发事务生成出现的死锁1213 Deadlock 和 1205 Lock wait timeout 模拟

错误:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

锁等待的复现步骤比较简单:
就是拿到了同一行的数据进行了更新,触发了行锁,超过全局变量lock_waits_time 时间 就会出现上述错误

如何解决死锁问题:

目前查询资料给的方案大多是 拦截错误,在错误内重新开启事务执行,可怕的问题这种负载机器定时任务还会出现,合理的就是事务内减少大量的sql

还有一种方案是 修改事务 改为 READ COMMITTED这个本地测试并没有解决此问题,如果有更好的方案欢迎留言讨论

本作品采用《CC 协议》,转载必须注明作者和本文链接
每天一点小知识,到那都是大佬,哈哈
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!