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

本地创建两个表：

// 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

我们现在事务1执行：

start transaction;

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

我们在事务2内执行：

update test set age = 30 where id = 2;

事务1执行:

update account set account = 800 where id = 1;

接着继续在事务2执行：

update account set account = 1500 where id = 1;

事务1执行：

update test set age = 50 where id = 2;

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

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

我们给事务2执行commit 操作

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

此时我们看到数据库数据已经更新成功，事务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;

等待50秒之后我们不提交事务,看到等待的那个事务1 出现了Lock wait timeout exceeded

错误:

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

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

如何解决死锁问题：

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

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

本作品采用《CC 协议》，转载必须注明作者和本文链接