Mysql gap 锁
mysql存储引擎:innodb mysql事务隔离级别: READ-COMMITTED
昨天线上有个数据表字段回写一直为 NULL 就追踪日志,最后发现一个 mysql 报错
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
解析报错消息体
-
SQLSTATE[40001]:事务序列化失败,通常由死锁触发。
-
1213:MySQL InnoDB 错误码,表示死锁。
-
Deadlock found when trying to get lock:事务 A 和 B 互相等待对方持有的锁,InnoDB 检测到后,选择回滚其中一个事务(当前这个),让另一个继续。
-
try restarting transaction:提示必须重新开始事务重试;当前事务已被回滚,不能继续。
观察问题
-
看起来是 mysql 获取锁失败了,sql 被拒绝直接回滚了,
-
看到这个问题第一反应是数据操作时有事务操作异常,没有提交,导致 事务冲突 //检查了这个表所有请求位置,发现开启任何事务,看起来不应该是事务导致的问题
-
再追踪检查日志发现这个问题大批量出现,20w 数据中有 10 万异常,50% 比率,
-
然后我检查了list表相关字段表结构
CREATE TABLE `article` ( `batch_task_id` int NOT NULL COMMENT '批量任务 id', `owner_id` bigint NOT NULL COMMENT '归属账号 id', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `request_params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '请求参数', `result_status` tinyint DEFAULT '0' COMMENT '处理状态(0 未处理 1 成功 2 失败)', `update_time` datetime DEFAULT NULL COMMENT '更新时间', KEY `idx_batch_task_id` (`batch_task_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='文章任务列表'; -
并且检查所有引用该表的逻辑 发现初始化表数据时是通过 insertAll 实现批量新增 每次 200 条左右,batch_task_id 是有重复的int 值(8774305) 是batch task 表的主键 在任务消费 task 中 where batch_task_id=123进行了批量 update,
-
再次观察报错消息为获取锁失败,并且释放锁后还会自动恢复该锁,
分析
- 观察表结构和引用位置,我推论该问题是批量插入时batch_task_id 每批会有较大间隙和重复, 并且在 update 时通过非主键索引进行 查找时候mysqlinsert 操作还没执行完, 在 insert 时通过idx_batch_task_id 索引为该表新增了一个 gap 锁, RR 隔离级别下 batch_task_id 的 range 查询产生了 gap lock,导致 update 时获取锁失败 但是在获取锁时候被 mysql 检测到死锁,将获取锁请求拒绝了,当 insertall 全部执行完后,插入的批量锁也释放了,导致事务冲突自动解除了
尝试解决的方案
- article表新增一个主键 id, 在批量 update 操作时不使用batch_task_id为 update 的 where 条件,改为通过主键检索,将修改条目的颗粒度改为主键 update 指定row
问题分析
- 更新条件没走索引,导致锁大量行或间隙,解决方案即在所有批量操作时候将 update 批量查询的颗粒度降低,最好降低为主键 id 将查询颗粒度将为行则可以规避类似的问题
Pingback: order iphone duo online