如何定位并解决MySQL主从复制中的行锁争用延迟-mysql教程-PHP中文网

show processlist看不到行锁是因为行锁等待时sql线程状态显示为updating或locked，而非显式等待；seconds_behind_master飙升是因sql线程在执行update/delete时被innodb行锁阻塞，需通过performance_schema.data_lock_waits定位持锁事务。

如何定位并解决mysql主从复制中的行锁争用延迟

为什么 SHOW PROCESSLIST 看不到行锁，但 Seconds_Behind_Master 却飙升

因为行锁争用本身不阻塞 SQL 线程运行，而是让 SQL Thread 在执行某条 UPDATE 或 DELETE 时，卡在等待 InnoDB 行锁释放上。此时线程状态可能是 Updating 或 Locked，但不会显示为 Waiting for table metadata lock（那是 MDL 锁）。你看到的延迟是“事务已读入 relay log、正准备执行、却被锁拦住”，不是“没读到日志”。

用 SELECT * FROM performance_schema.data_lock_waits 定位真实行锁阻塞源

MySQL 8.0+ 提供了直接可观测的行锁等待视图。在从库上执行：

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_TRX_ID
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_TRX_ID;

关键点：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

如果 blocking_query 是业务侧长查询（比如未加索引的 SELECT ... FROM huge_table WHERE status=0），说明从库被读流量拖住；
如果 blocking_query 是另一条复制回放中的 DML（如 UPDATE order_info SET paid=1 WHERE id=123456），说明主库发来的事务之间存在锁冲突，常见于无主键或非唯一索引更新；
若 blocking_thread 是 0，代表锁来自已提交但未清理的旧事务（需检查 innodb_lock_wait_timeout 和 innodb_rollback_on_timeout 配置）。

避免行锁争用的三个硬性约束

行锁争用无法靠“加大并发数”解决，必须从数据访问模式入手：

所有写操作必须走 PRIMARY KEY 或 UNIQUE KEY —— 否则 MySQL 可能升级为间隙锁或临键锁，扩大锁范围；
禁止在从库执行未加 WHERE 条件或全表扫描的 UPDATE/DELETE，这类语句会持有表级意向锁，阻塞所有后续复制事务；
主库批量更新必须按主键分片，例如：UPDATE t SET status=1 WHERE id BETWEEN 10000 AND 19999 AND status=0，而不是 WHERE status=0 无范围限制。

从库只读 + explicit_defaults_for_timestamp 的隐藏陷阱

即使设置了 read_only=ON，某些场景下从库仍可能产生本地锁争用：

使用 mysqldump --single-transaction 备份时，START TRANSACTION WITH CONSISTENT SNAPSHOT 会持有 MVCC 快照，若持续时间长，可能阻塞复制事务对同一行的更新；
explicit_defaults_for_timestamp=OFF（MySQL 5.6 默认）会导致 INSERT 隐式更新 TIMESTAMP 列，触发额外的行查找与锁竞争；建议主从统一设为 ON 并显式声明默认值；
监控 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 这两个状态变量，当平均锁等待时间 > 50ms 且每秒锁等待次数 > 5，基本可判定存在严重行锁争用。