Mysql gap 锁引发的线上问题

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 将查询颗粒度将为行则可以规避类似的问题

1人评论了“Mysql gap 锁引发的线上问题”

  1. Pingback: order iphone duo online

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部