mysql如何处理备份时遇到的死锁问题_优化隔离级别设置-mysql教程-PHP中文网

应将 mysqldump 备份会话隔离级别设为 read committed，通过 --init-command="set session transaction isolation level read committed" 实现，需搭配 --single-transaction 且适用于 mysql ≥ 5.7 的 innodb 表。

mysql如何处理备份时遇到的死锁问题_优化隔离级别设置

mysqldump 备份时提示 `Lock wait timeout exceeded` 怎么办

这不是备份工具本身的问题，而是 mysqldump 默认启用 --single-transaction 时，依赖事务隔离级别保证一致性，但若长事务未结束，就会卡在 SELECT 上等锁，最终超时。

常见错误现象：mysqldump: Got error: 1205: Deadlock found when trying to get lock; try restarting transaction 或更频繁的 Lock wait timeout exceeded；尤其在业务写入密集、有未提交事务或慢查询堆积时。

先查有没有阻塞源：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), TRX_STARTED)) > 60;
临时缓解：加 --skip-lock-tables（但可能破坏一致性，仅限非关键库）
真正有效的做法是让备份事务不被旧事务拖住——调低隔离级别，从默认的 REPEATABLE READ 改为 READ COMMITTED

如何安全地把备份会话隔离级别设为 `READ COMMITTED`

mysqldump 不支持直接传 SET SESSION TRANSACTION ISOLATION LEVEL，得靠 --init-command 注入。

使用场景：你控制备份脚本，且 MySQL 版本 ≥ 5.7（READ COMMITTED 对一致性影响可控），表引擎是 InnoDB。

实操命令示例：

MySQL

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

下载

mysqldump --single-transaction \
  --init-command="SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED" \
  -u root -p mydb > backup.sql

必须搭配 --single-transaction，否则 READ COMMITTED 在备份中无意义
不能用 SET GLOBAL，会影响所有连接，风险太大
注意：如果备份期间有大事务执行 UPDATE/DELETE，READ COMMITTED 下可能漏读刚提交的行（极小概率），但比死锁导致备份失败更可控

`READ COMMITTED` 和 `REPEATABLE READ` 在备份中的实际差异

差别不在“能不能读到新数据”，而在“备份快照的起点怎么定”。REPEATABLE READ 要求整个事务开始前所有已提交变更都固化成一个快照；READ COMMITTED 是每个语句执行前重新拉一次快照。

性能与兼容性影响：

对备份速度几乎无影响，但显著降低锁等待概率
MySQL 5.6 不推荐，因 READ COMMITTED 下 SELECT ... FOR UPDATE 行为有差异，可能引发意料外的间隙锁
如果你用的是 Percona XtraBackup，它默认走物理拷贝，不走 SQL 层隔离，这个设置无效——别混用

为什么不用 `SET TRANSACTION READ ONLY` 配合 `READ COMMITTED`

看起来更安全？其实没必要，还容易踩坑。

mysqldump 本身只读，加 READ ONLY 不带来额外保护；反而在某些 MySQL 版本（如 5.7.20 前）会导致 --single-transaction 失效，退化成表级锁。

验证是否生效：备份过程中执行 SELECT @@tx_isolation, @@session.transaction_read_only;
如果看到 READ-COMMITTED 但 transaction_read_only=OFF，说明正常；若为 ON，检查有没有其他配置覆盖了 --init-command
最易忽略的一点：Docker 或 systemd 启动的 MySQL 可能预加载了 init_connect，会和你的 --init-command 冲突，导致隔离级别没生效