线上MySQL死锁，深入了解MySQL锁机制及pt-online

Hello_world_
93 阅读17分钟

前言

在我们学习MySQL的过程中，对MySQL的死锁都有一定了解。但是在实际生产环境中，遇到MySQL死锁场景对机会并不多。因此，对MySQL死锁原理的了解更多的是浮于表面。本篇文章将根据实际生产环境产生的MySQL死锁分析死锁产生的原因并讲解MySQL死锁的一些相关原理。

事故现象

简单描述一下事故现象。在一个寻常的凌晨，本该安心的睡觉。然而，凌晨3点多被一个最不想接到的电话吵醒（谁能懂，凌晨收到告警电话的那种崩溃）。此刻的心情

u=1543272680,826445026&fm=253&fmt=auto&app=138&f=JPEG.webp 收到的告警消息显示MySQL产生了死锁。

image.png

在告警之后的1分钟，死锁告警就已经恢复了。查询对应的MySQL日志内容，死锁期间的MySQL日志（敏感信息已脱敏）如下

TRANSACTION 1552471810, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

LOCK WAIT 7 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3

MySQL thread id 35833583, OS thread handle 140013220132608, query id 5285626704 10.6.44.188 user_aaa_2 update

REPLACE INTO `aaa_2`.`_bbb_3_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) VALUES (NEW.`pkid`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`u

2025-11-21T01:58:53.882679+08:00 35817125 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 350 page no 10041 n bits 72 index unidx_biz_id_type_source of table `aaa_2`.`_bbb_3_new` trx id 1552471810 lock_mode X waiting

2025-11-21T01:58:53.882694+08:00 35817125 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1552471812, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3

MySQL thread id 35817125, OS thread handle 140013467797248, query id 5285626707 10.21.19.225 user_fas_acc_clean_2 update

REPLACE INTO `aaa_2`.`_bbb_3_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) VALUES (NEW.`pkid`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`

2025-11-21T01:58:53.882716+08:00 35817125 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 350 page no 10041 n bits 72 index unidx_biz_id_type_source of table `aaa_2`.`_bbb_3_new` trx id 1552471812 lock_mode X locks rec but not gap

2025-11-21T01:58:53.882727+08:00 35817125 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 350 page no 10041 n bits 72 index unidx_biz_id_type_source of table `aaa_2`.`_bbb_3_new` trx id 1552471812 lock_mode X waiting

2025-11-21T01:58:53.882738+08:00 35817125 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

2025-11-21T02:10:52.680395+08:00 35842793 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

2025-11-21T02:10:52.680409+08:00 35842793 [Note] InnoDB:

*** (1) TRANSACTION:

TRANSACTION 1552590285, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

LOCK WAIT 7 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3

MySQL thread id 35773229, OS thread handle 140013722797824, query id 5286032613 10.21.41.74 user_aaa_3 update

REPLACE INTO `aaa_3`.`_bbb_0_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) VALUES (NEW.`pkid`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`u

2025-11-21T02:10:52.680437+08:00 35842793 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 370 page no 2576 n bits 72 index unidx_biz_id_type_source of table `aaa_3`.`_bbb_0_new` trx id 1552590285 lock_mode X waiting

2025-11-21T02:10:52.680452+08:00 35842793 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1552590267, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

371 lock struct(s), heap size 41168, 6903 row lock(s), undo log entries 6901

MySQL thread id 35842793, OS thread handle 140013673010944, query id 5286032521 10.20.19.57 mysqluser Sending data

INSERT LOW_PRIORITY IGNORE INTO `aaa_3`.`_bbb_0_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) SELECT `pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`,

2025-11-21T02:10:52.680470+08:00 35842793 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 370 page no 2576 n bits 72 index unidx_biz_id_type_source of table `aaa_3`.`_bbb_0_new` trx id 1552590267 lock_mode X locks rec but not gap

2025-11-21T02:10:52.680481+08:00 35842793 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 370 page no 2577 n bits 208 index unidx_biz_id_type_source of table `aaa_3`.`_bbb_0_new` trx id 1552590267 lock_mode X locks gap before rec insert intention waiting

2025-11-21T02:10:52.680494+08:00 35842793 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

2025-11-21T02:10:52.764040+08:00 35842793 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

2025-11-21T02:10:52.764055+08:00 35842793 [Note] InnoDB:

*** (1) TRANSACTION:

TRANSACTION 1552590318, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

LOCK WAIT 7 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3

MySQL thread id 35773229, OS thread handle 140013722797824, query id 5286032731 10.21.41.74 user_aaa_3 update

REPLACE INTO `aaa_3`.`_bbb_0_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) VALUES (NEW.`pkid`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`u

2025-11-21T02:10:52.764089+08:00 35842793 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 370 page no 2606 n bits 192 index unidx_biz_id_type_source of table `aaa_3`.`_bbb_0_new` trx id 1552590318 lock_mode X waiting

2025-11-21T02:10:52.764113+08:00 35842793 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1552590267, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

617 lock struct(s), heap size 57552, 10898 row lock(s), undo log entries 10894

MySQL thread id 35842793, OS thread handle 140013673010944, query id 5286032521 10.20.19.57 mysqluser Sending data

INSERT LOW_PRIORITY IGNORE INTO `aaa_3`.`_bbb_0_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `status`, `field`, `status_bak`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) SELECT `pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`,

2025-11-21T02:10:52.764140+08:00 35842793 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 370 page no 2606 n bits 192 index unidx_biz_id_type_source of table `aaa_3`.`_bbb_0_new` trx id 1552590267 lock_mode X locks rec but not gap

2025-11-21T02:10:52.764156+08:00 35842793 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 370 page no 2606 n bits 192 index unidx_biz_id_type_source of table `aaa_3`.`_bbb_0_new` trx id 1552590267 lock_mode X locks gap before rec insert intention waiting

2025-11-21T02:10:52.764176+08:00 35842793 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

2025-11-21T03:27:46.866344+08:00 35849344 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

2025-11-21T03:27:46.866357+08:00 35849344 [Note] InnoDB:

*** (1) TRANSACTION:

TRANSACTION 1553136569, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

LOCK WAIT 7 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3

MySQL thread id 35762530, OS thread handle 140013618431744, query id 5287930191 10.21.3.199 user_aaa_7 update

REPLACE INTO `aaa_7`.`_bbb_3_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `status`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) VALUES (NEW.`pkid`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`field`, NEW.`u

2025-11-21T03:27:46.866393+08:00 35849344 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 484 page no 3513 n bits 72 index unidx_biz_id_type_source of table `aaa_7`.`_bbb_3_new` trx id 1553136569 lock_mode X waiting

2025-11-21T03:27:46.866409+08:00 35849344 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1553136505, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

704 lock struct(s), heap size 73936, 12641 row lock(s), undo log entries 12639

MySQL thread id 35849344, OS thread handle 140013464868608, query id 5287929962 10.20.19.57 mysqluser Sending data

INSERT LOW_PRIORITY IGNORE INTO `aaa_7`.`_bbb_3_new` (`pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `status`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`) SELECT `pkid`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`, `field`,

2025-11-21T03:27:46.866428+08:00 35849344 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 484 page no 3513 n bits 72 index unidx_biz_id_type_source of table `aaa_7`.`_bbb_3_new` trx id 1553136505 lock_mode X locks rec but not gap

2025-11-21T03:27:46.866438+08:00 35849344 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 484 page no 3997 n bits 248 index unidx_biz_id_type_source of table `aaa_7`.`_bbb_3_new` trx id 1553136505 lock_mode X locks gap before rec insert intention waiting

2025-11-21T03:27:46.866452+08:00 35849344 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

事故排查

虽然死锁告警恢复了，但是死锁产生原因还是需要排查，避免后续再次发生死锁问题从而影响生产数据。回顾近一周该服务的变更，总体存在2个关键变更

  • 业务代码发布
  • 表碎片整理

业务代码排查

在查看gitlab代码仓库后，发现数据库死锁前发布的业务代码并不存在数据库操作。因此，可以初步判断新增的业务代码并不是导致数据库死锁的原因。

在查看mysql报错日志后，发现发生死锁的sql语句有：REPLACE INTO、INSERT……SELECT。这2个语句在业务代码中并不存在，因此可以判断死锁原因并不是业务代码导致的。

表碎片整理排查

背景介绍，发生死锁数据库对应的服务日均调用量超3千万。对应数据库采取分库分表架构。即便如此，数据库日均数据增量已超17G。在可预见的未来该服务日均调用次数会日益增长，数据库日均数据增量也日益增长。对于历史数据会进行数据归档操作。

由于该数据库会对历史数据进行数据归档，那么就会造成对应.idb文件的磁盘空间有效利用率越来越低，进而影响数据的查询、修改、删除、插入操作。所以，需要对该数据表进行表碎片整理。

在排除业务代码原因后，与DBA沟通后得知表碎片整理工单会导致数据库死锁的产生，且目前无法解决。进一步沟通得知，该操作不会对业务流程有影响（此刻心情如释重负）。

v2-3142b0714bca543a232884a259a6b8b6_720w.png

死锁分析

虽然排查出了死锁产生的原因并且不是我造成的（对我的绩效没有影响，对我的绩效没有影响，对我的绩效没有影响。重要的事情说3遍），但是为什么会发生死锁还不得而知。出于好奇心的驱使，我对这个死锁产生的根因充满了好奇。

u=72690610,453925253&fm=253&fmt=auto&app=138&f=JPEG.webp

分析对应的MySQL日志，日志中发生死锁场景如下：

死锁场景1

发生时间： 2025-11-21 01:58:53

涉及事务： 1552471810（事务1）、1552471812（事务2）

发生背景： 2个事务的REPLACE对同一个二级索引范围（同一个索引key或相邻key）进行操作

死锁发生时间线：

死锁1时间线.png

死锁循环：

  • 事务1等待事务2释放记录锁
  • 事务2等待事务1放弃gap范围

死锁场景2

发生时间： 2025-11-21 02:10:52

涉及事务： 1552590285（事务1）、1552590267（事务2）

发生背景： 事务1执行REPLACE为小事务、事务2执行INSERT……SELECT为大事务

死锁发生时间线：

死锁2时间顺序.png

死锁循环：

  • 事务1等待事务2是否记录锁
  • 事务2等待事务1放弃gap范围

死锁场景3

死锁场景3与死锁场景2场景一致，仅发生死锁数据不同。此处不重复解释。

问题与解答

对于这次死锁场景分析，存在以下几个问题。

  • 问题1: 碎片整理为什么会出现REPLACE、INSERT……SELECT语句？

解答： 与DBA沟通后得知，rds采用pt-online-ddl方式进行碎片整理，由于该方案是通过新建一张临时表将原表数据copy到临时表达到碎片整理的效果。该方案天然存在死锁风险。

  • 问题2: 尝试获取锁并未实际获取锁，为什么会被判定为死锁？

解答： InnoDB将锁请求本身也纳入了死锁检测。因此，即便是尝试获取锁并未实际获取锁同样也会被判定为死锁。

  • 问题3: InnoDB执行INSERT……SELECT操作数据是，获取锁执行完INSERT操作后为什么不立即释放锁？

解答： InnoDB引擎遵守严格两阶段锁（Strict 2PL），这就意味着一旦获取了X锁并执行了插入操作，在事务结束前，该X锁将一直持有。

若允许事务中途释放锁，会导致什么后果？

  • 破坏事务隔离性：导致脏写、幻读等问题。如事务1在中途释放锁后，事务2对该数据进行操作，然而事务1在后续逻辑依赖该数据，这就导致了事务隔离性被破坏。
  • 索引失效：对于非唯一索引而言，会导致同一个事务在对事务期间B+树结构发生改变；对于唯一索引而言，会导致双插入成功问题（插入同义数据，不同事务均成功）
  • 破坏MVCC机制
  • 问题4: 什么是pt-online-ddl

pt-online-ddl是在线修改MySQL表结构的工具。主要用于大表结构变更（添加字段、修改数据类型、创建索引等）。

其应用场景主要有：

  1. 生产环境大表结构变更
  2. 生产环境大表索引变更
  3. 大表磁盘碎片整理

工作原理：

  1. 创建新表
  2. 执行DDL变更
  3. 为原表创建insert、update、delete触发器
  4. 分批拷贝历史数据
  5. 校验原表与新表数据一致性
  6. 新表更名
  7. 删除原表

在分批拷贝历史数据过程中，原表数据可能存在变更（INSERT、UPDATE、DELETE）会触发对应的触发器。在此期间，copy事务与业务事务存在并发，存在死锁风险。

  • 问题5: 锁请求为什么会被纳入死锁检测

在上述死锁场景中，发生死锁的事务中存在一方并未实际获得记录锁而是尝试获取锁，却被判定为了死锁。

目的： 及时发现“因等待而形成的循环依赖”并提前干预避免造成实际死锁，避免隐性死锁长期阻塞系统，并确保事务调度的正确性和活性。

InnoDB将锁请求也视为资源的一部分，是为了在逻辑死锁阶段就主动干预，确保系统永远不会因为并发而停摆。

InnoDB的锁及加锁原理

在MySQL中存在不同等级、不同功能的锁。对于不同的锁，锁定的资源并不相同。下面将对InnoDB各种锁进行介绍。

MySQL在不同事务隔离级别中某些锁，锁定的资源并不相同。

在MySQL中不同类别的锁（行级锁、表级锁、页面锁）锁定的资源大致分为以下四类：

  • 行数据（数据本身）
  • 索引记录（索引项）
  • 数据间间隙
  • 表级资源

在读已提交隔离级别下，锁定的资源主要集中在索引记录层面。锁定间隙相关的锁使用较少

意向锁

在意向锁中，可分为意向排他锁（IX）、意向共享锁（IS）。

锁定资源：整张表（逻辑层面）

作用：

  • 用于表级锁与行级锁之间的协调
  • 并不阻碍普通的读写
 SELECT * FROM user FOR SHARE   --IS锁
 SELECT * FROM user FOR UPDATE  -- IX锁
记录锁

在记录锁中，也分为共享、排他。

锁定资源：索引中的一条具体记录

此处索引包含聚簇索引和二级索引

UPDATE user SET age = 10 WHERE id = 10;
间隙锁

间隙锁，锁定的是索引记录之间的间隙。或锁定第一个或最后一个索引记录之前或之后的间隙

在读已提交的隔离级别下，间隙锁除唯一键检查等情况外其余场景被禁用

作用：允许更高并发的插入，避免不必要的阻塞

SELECT * FROM user WHERE id BETWEEN 10 AND 20 FOR UPDATE
临键锁

临键锁可以理解为记录锁与间隙锁的组合

在读已提交的隔离级别下，临键锁被禁用

锁定资源：索引记录及索引记录之前的间隙

SELECT * FROM user WHERE id BETWEEN 10 AND 20 FOR UPDATE
插入意向锁

插入意向锁可以理解为一种间隙锁。该锁用来表示插入意图 即多个事务插入同一索引间隙 如果他们没有插入 在空隙内的位置相同。

锁定资源：某个间隙

INSERT INTO user (id, name) values (20, 'aaa'),(30, 'bbb');
自增锁

自增锁是一种特殊的表级锁。

锁定资源：表级自增计数器

上述各种类型的锁其锁定的资源分类。

锁类型锁定资源
意向锁（IS、IX）表
记录锁索引记录
间隙锁索引间隙
临键锁索引及索引间隙
插入意向锁索引间隙
自增锁表级计数器

以上内容依据于MySQL官方文档InnoDB锁

avatar
文章
阅读
粉丝