MySQL 死锁
- 2025-05-05 10:07:00
- 丁国栋
- 原创 24
如何监控mysql是否发生死锁?
MySQL 死锁监控方法详解
- 查看死锁日志
方法一:查看最近死锁信息
SHOW ENGINE InnoDB STATUS\G
在输出结果中查找"LATEST DETECTED DEADLOCK"部分,包含:
-
死锁发生时间
-
涉及的事务和SQL语句
-
等待的锁资源
-
被回滚的事务
方法二:启用详细死锁日志 在my.cnf/my.ini中添加:
[mysqld]
InnoDB_print_all_deadlocks = 1
这样所有死锁信息都会写入错误日志,而不仅是最近一次。
- 监控系统变量
-- 查看死锁相关统计
SHOW STATUS LIKE 'InnoDB_row_lock%';
SHOW STATUS LIKE 'InnoDB_deadlocks%';
-- 重要指标解释
InnoDB_row_lock_current_waits: 当前等待的锁数量
InnoDB_row_lock_time: 锁等待总时长(ms)
InnoDB_row_lock_time_avg: 平均锁等待时间(ms)
InnoDB_row_lock_time_max: 最大锁等待时间(ms)
InnoDB_row_lock_waits: 锁等待总次数
InnoDB_deadlocks: 死锁发生次数
- 使用性能模式(Performance Schema)
-- 启用锁监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock/metadata/sql/mdl';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_transactions%';
-- 查询锁等待信息
SELECT * FROM performance_schema.events_waits_current
WHERE EVENT_NAME LIKE 'wait/lock%';
-- 查询死锁事务
SELECT * FROM performance_schema.events_transactions_current
WHERE STATE = 'DEADLOCK';
- 使用information_schema
-- 查看当前锁等待
SELECT * FROM information_schema.InnoDB_LOCKS;
SELECT * FROM information_schema.InnoDB_LOCK_WAITS;
SELECT * FROM information_schema.InnoDB_TRX;
-- 综合查询示例
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 information_schema.InnoDB_LOCK_WAITS w
INNER JOIN information_schema.InnoDB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.InnoDB_TRX r ON r.trx_id = w.requesting_trx_id;
- 配置自动监控
方法一:设置报警阈值
-- 设置锁等待超时时间(秒)
SET GLOBAL InnoDB_lock_wait_timeout = 50;
方法二:使用监控工具
-
Prometheus + MySQL Exporter
-
Percona Monitoring and Management (PMM)
-
Zabbix MySQL模板
-
Datadog/New Relic等APM工具
- 定期检查脚本示例
#!/bin/bash
# 检查死锁并发送报警
deadlocks=$(mysql -e "SHOW STATUS LIKE 'InnoDB_deadlocks'" | awk 'NR==2 {print $2}')
if [ "$deadlocks" -gt 0 ]; then
# 获取详细死锁信息
deadlock_info=$(mysql -e "SHOW ENGINE InnoDB STATUS\G")
# 发送报警(示例使用邮件)
echo "$deadlock_info" | mail -s "MySQL Deadlock Alert on $(hostname)" admin@example.com
# 可选:记录到日志文件
echo "$(date) - Detected $deadlocks deadlock(s)" >> /var/log/mysql_deadlocks.log
echo "$deadlock_info" >> /var/log/mysql_deadlocks.log
fi
-
分析死锁的实用建议
-
收集完整信息:
-
死锁发生时间点
-
涉及的SQL语句
-
表结构和索引情况
-
系统负载情况
-
-
重现步骤:
-- 1. 设置隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 2. 开启事务 START TRANSACTION; -- 3. 执行会引发死锁的SQL UPDATE eps_user SET balance = balance - 100 WHERE id = 1; -- 在另一个会话中执行冲突操作...
-
解决方案参考:
-
调整事务大小和持续时间
-
统一操作顺序
-
优化SQL和索引
-
考虑使用乐观锁
-
适当降低隔离级别
-
如果造成死锁的2个查询超过了 InnoDB_lock_wait_timeout 设置的时间会怎样?
MySQL死锁与锁等待超时的关系
当发生死锁的两个查询超过了InnoDB_lock_wait_timeout
设置的时间时,MySQL的处理方式会有特殊之处,这与普通的锁等待超时不同。
死锁与锁等待超时的关键区别
-
死锁(Deadlock):
-
是循环等待条件:事务A等待事务B,同时事务B也在等待事务A
-
MySQL的InnoDB引擎有死锁检测机制(默认启用)
-
检测到死锁后会立即回滚其中一个事务(选择代价较小的事务)
-
-
锁等待超时(Lock Wait Timeout):
-
是单向等待:事务A等待事务B,但事务B没有在等事务A
-
等待时间超过
InnoDB_lock_wait_timeout
值(默认50秒)才会触发 -
等待的事务会收到ERROR 1205 (HY000): Lock wait timeout exceeded错误
-
如果两个造成死锁的查询超过了InnoDB_lock_wait_timeout时间会怎样?实际上,这种情况不会发生,因为:
-
InnoDB的死锁检测机制(默认开启)会在死锁形成时立即介入,不会等待超时
-
检测到死锁后会在毫秒级时间内做出反应
-
选择回滚其中一个事务(依据事务的undo log量判断)
-
-
InnoDB_lock_wait_timeout
仅适用于非死锁的锁等待场景- 例如:一个长事务持有锁不释放,其他会话等待这些锁
-
即使将
InnoDB_lock_wait_timeout
设为很短(如1秒):SET SESSION InnoDB_lock_wait_timeout=1;
死锁仍会被优先检测并处理,不会等到超时
验证实验
可以通过以下实验验证:
-- 会话1
START TRANSACTION;
select balance from eps_user where id = 1;
UPDATE eps_user SET balance = balance + 100 WHERE id = 1;
-- 会话2
START TRANSACTION;
UPDATE eps_user SET balance = balance - 100 WHERE id = 1; -- 这里会死锁
-- 观察结果:会立即报错"Deadlock found"而非等待超时
提示:如果不是死锁: MySQL 会报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
相关参数控制
-
死锁检测开关:
SHOW VARIABLES LIKE 'InnoDB_deadlock_detect'; -- 默认ON
如果关闭(
OFF
),系统会依赖锁等待超时来处理死锁(不推荐) -
锁等待超时设置:
SHOW VARIABLES LIKE 'InnoDB_lock_wait_timeout'; -- 默认50秒 SET GLOBAL InnoDB_lock_wait_timeout=30; -- 修改全局设置
生产环境建议
-
保持
InnoDB_deadlock_detect=ON
(默认值) -
设置合理的
InnoDB_lock_wait_timeout
:-
OLTP系统:建议10-30秒
-
报表系统:可以设置更长(如120秒)
-
-
应用程序应处理这两种错误:
-
死锁错误:
ERROR 1213 (40001): Deadlock found
-
锁等待超时错误:
ERROR 1205 (HY000): Lock wait timeout exceeded
-
-
实现自动重试逻辑(针对死锁情况)
总结:死锁情况会被InnoDB优先检测并立即处理,不会等到InnoDB_lock_wait_timeout
超时。该参数仅影响非死锁的锁等待场景。
死锁的例子
------------------------
2025-04-30 15:00:30 0x7fd045975700
*** (1) TRANSACTION:
TRANSACTION 9426720547, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24978624, OS thread handle 140531733772032, query id 170473145852 172.16.10.123 zentao updating
DELETE FROM `zt_config` wHeRe 1=1 AND `vision` = '' AND `owner` = 'system' AND `module` = 'common' AND `section` = 'global' AND `key` = 'mode'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `怎套`.`zt_config` trx id 9426720547 lock_mode X locks rec but not gap waiting
Record lock, heap no 103 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 0; hex ; asc ;;
1: len 30; hex 73797374656d202020202020202020202020202020202020202020202020; asc system ;;
2: len 6; hex 636f6d6d6f6e; asc common;;
3: len 30; hex 676c6f62616c202020202020202020202020202020202020202020202020; asc global ;;
4: len 30; hex 6d6f64652020202020202020202020202020202020202020202020202020; asc mode ;;
5: len 3; hex 007480; asc t ;;
*** (2) TRANSACTION:
TRANSACTION 9426720546, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 24978625, OS thread handle 140532497471232, query id 170473145857 172.16.10.123 zentao update
INSERT INTO `zt_config` (`owner`,`module`,`section`,`key`,`vision`,`value`) VALUES('system','common','global','mode','','PLM')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock_mode X locks rec but not gap
Record lock, heap no 103 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 0; hex ; asc ;;
1: len 30; hex 73797374656d202020202020202020202020202020202020202020202020; asc system ;;
2: len 6; hex 636f6d6d6f6e; asc common;;
3: len 30; hex 676c6f62616c202020202020202020202020202020202020202020202020; asc global ;;
4: len 30; hex 6d6f64652020202020202020202020202020202020202020202020202020; asc mode ;;
5: len 3; hex 007480; asc t ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock mode S waiting
Record lock, heap no 103 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 0; hex ; asc ;;
1: len 30; hex 73797374656d202020202020202020202020202020202020202020202020; asc system ;;
2: len 6; hex 636f6d6d6f6e; asc common;;
3: len 30; hex 676c6f62616c202020202020202020202020202020202020202020202020; asc global ;;
4: len 30; hex 6d6f64652020202020202020202020202020202020202020202020202020; asc mode ;;
5: len 3; hex 007480; asc t ;;
*** WE ROLL BACK TRANSACTION (1)
MySQL 死锁日志分析
这段文字是 MySQL 数据库的死锁日志(Deadlock Log),记录了数据库发生死锁时的详细信息。
- 关于
lock_mode X locks rec but not gap waiting
和lock_mode X locks rec but not gap
:
-
lock_mode X locks rec but not gap waiting
英文翻译:排他锁模式,锁定记录但不锁定间隙,正在等待中 -
lock_mode X locks rec but not gap
英文翻译:排他锁模式,锁定记录但不锁定间隙
关键术语解析:
-
lock_mode X
→ 排他锁模式(X锁) -
locks rec
→ 锁定记录(record lock) -
but not gap
→ 但不锁定间隙(非间隙锁) -
waiting
→ 等待状态(表示该锁尚未获取,正在等待)
补充说明:
- 第一种带
waiting
的描述表示事务正在尝试获取这个排他锁但被阻塞 - 第二种不带
waiting
的描述表示该排他锁已被成功获取 - "不锁定间隙"意味着该锁只锁定索引记录本身,不锁定记录之前的间隙(与
gap lock
相对)
这种锁状态常见于:
-
UPDATE语句
-
DELETE语句
-
SELECT...FOR UPDATE语句
当使用唯一索引精确查询时(如WHERE id=1
),InnoDB通常会使用这种"仅锁定记录"的锁模式。
- MySQL日志中判断排他锁(X)和共享锁(S)的方法
在MySQL的死锁日志中,锁类型会明确标注出来。以下是最初提供的死锁日志中判断锁类型的方法:
日志关键部分解析
在最初提供的日志中,有以下关键锁信息:
事务1等待的锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720547 lock_mode X locks rec but not gap waiting
-
lock_mode X
:明确表示这是一个排他锁(X锁) -
locks rec but not gap
:锁定记录但不锁定间隙 -
waiting
:正在等待获取这个锁
事务2持有的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock_mode X locks rec but not gap
-
lock_mode X
:同样是排他锁(X锁) -
没有
waiting
标记,表示这个锁已被该事务持有
事务2等待的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock mode S waiting
-
lock mode S
:明确表示这是一个共享锁(S锁) -
waiting
:正在等待获取这个锁
MySQL锁类型标识总结
在InnoDB死锁日志中,锁类型会通过以下方式明确标识:
-
排他锁(Exclusive Lock/X锁):
-
标识为
lock_mode X
-
用于UPDATE、DELETE、SELECT...FOR UPDATE等操作
-
阻止其他事务获取任何类型的锁
-
-
共享锁(Shared Lock/S锁):
-
标识为
lock mode S
-
用于普通SELECT...LOCK IN SHARE MODE操作
-
允许其他事务获取共享锁,但阻止排他锁
-
-
意向锁(Intention Lock):
-
意向共享锁:
lock_mode IS
-
意向排他锁:
lock_mode IX
-
-
间隙锁(Gap Lock):
-
lock_mode X locks gap before rec
-
防止其他事务在索引记录间隙中插入
-
实际日志分析案例
根据原始日志,死锁形成的原因是:
-
事务1:尝试获取X锁(删除操作)但被阻塞
DELETE FROM `zt_config` WHERE ...
-
事务2:
-
已经持有同一记录的X锁(来自INSERT操作)
-
同时又在尝试获取同一记录的S锁(可能是约束检查)
-
这样就形成了:
-
事务1等待事务2释放X锁
-
事务2等待事务1释放它需要的S锁
-
循环等待导致死锁
如何预防这类死锁
-
统一操作顺序:确保所有事务以相同顺序访问表
-
减少事务范围:尽快提交事务
-
使用合适的索引:减少锁定的范围
-
考虑使用乐观锁:通过版本号控制而非悲观锁
-
调整隔离级别:在某些场景下使用READ COMMITTED
理解日志中的锁标识对于诊断和解决死锁问题至关重要,X和S的明确标注是分析死锁原因的关键线索。
死锁原因分析
-
事务1 (TRANSACTION 9426720547) 正在执行一个 DELETE 操作,试图删除
zt_config
表中的特定记录。- 它正在等待获取该记录的排他锁(X lock)
-
事务2 (TRANSACTION 9426720546) 正在执行一个 INSERT 操作,试图向
zt_config
表插入新记录。-
它已经持有该记录的排他锁(X lock)
-
但同时又在等待获取同一记录的共享锁(S lock)
-
-
死锁形成:
-
事务1等待事务2释放锁
-
事务2在等待事务1释放锁(因为事务1的DELETE操作可能导致事务2需要检查约束)
-
形成循环等待,MySQL检测到后选择回滚事务1
-
解决方案
-
应用层修改:
-
确保对
zt_config
表的操作顺序一致 -
将相关操作封装在同一个事务中
-
考虑使用乐观锁而非悲观锁
-
-
数据库层优化:
-
调整事务隔离级别(如从 REPEATABLE READ 改为 READ COMMITTED)
-
为
zt_config
表添加适当的索引 -
考虑使用
SELECT ... FOR UPDATE
明确锁定顺序
-
-
监控与重试:
-
实现应用层的死锁检测和自动重试机制
-
监控频繁发生死锁的表和操作
-
这种死锁通常发生在高并发环境下对同一表进行混合操作(INSERT/DELETE/UPDATE)时,特别是在有唯一索引约束的情况下。
使用代码(如php+pdo 驱动)连接使用InnoDB引擎的MySQL服务器,如果php没有显式使用事务,那么也可能会出现上述死锁的情况么?
PHP+PDO连接MySQL时死锁的可能性分析
即使PHP代码没有显式使用事务,使用InnoDB引擎的MySQL仍然可能出现死锁情况。以下是详细解释:
可能发生死锁的情况
- 自动提交模式下的单条语句死锁
// 连接MySQL(默认autocommit=1)
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
// 这条UPDATE语句本身就会创建一个隐式事务
$pdo->exec("UPDATE eps_user SET balance = balance - 100 WHERE id = 1");
即使没有显式事务,单条DML语句(INSERT/UPDATE/DELETE)在InnoDB中也会自动创建一个事务,当多个会话同时操作同一数据时可能发生死锁。
- 复合操作导致的死锁
// 假设有两个脚本几乎同时执行以下操作
// 脚本1:
$pdo->exec("UPDATE table_a SET col1 = 1 WHERE id = 5");
$pdo->exec("UPDATE table_b SET col2 = 2 WHERE id = 3");
// 脚本2:
$pdo->exec("UPDATE table_b SET col2 = 2 WHERE id = 3");
$pdo->exec("UPDATE table_a SET col1 = 1 WHERE id = 5");
虽然每条语句都是自动提交的,但当两个脚本以相反顺序操作相同行时,仍可能形成循环等待。
为什么会出现这种情况
-
InnoDB的特性:
-
所有DML操作都会创建事务(即使autocommit=1)
-
锁是在语句执行时获取的,不是事务开始时
-
锁只在语句完成后才释放(autocommit=1时立即提交)
-
-
死锁形成的条件:
-
资源互斥:多个会话需要相同的锁
-
持有并等待:会话持有锁同时等待其他锁
-
循环等待:形成A等B,B等A的环路
-
不可抢占:MySQL不能强制剥夺已获得的锁
-
-
PHP+PDO的特殊性:
-
PDO默认autocommit=1(自动提交模式)
-
但自动提交≠无事务,只是每个语句自动形成独立事务
-
高并发时多个"微事务"仍可能冲突
-
解决方案
- 显式使用事务(推荐):
$pdo->beginTransaction();
try {
$pdo->exec("UPDATE ...");
$pdo->exec("UPDATE ...");
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
// 处理错误或重试
}
-
操作顺序一致化: 确保不同脚本总是以相同顺序访问表/行
-
减少事务持有时间:
-
尽快提交事务
-
避免在事务中执行耗时操作
-
-
死锁检测与重试:
$retry = 3;
while ($retry--) {
try {
$pdo->beginTransaction();
// ...执行操作...
$pdo->commit();
break;
} catch (PDOException $e) {
$pdo->rollBack();
if (strpos($e->getMessage(), 'Deadlock') === false || $retry === 0) {
throw $e;
}
usleep(rand(100000, 500000)); // 随机等待
}
}
- 调整隔离级别(谨慎使用):
// 可以降低到READ COMMITTED减少锁冲突
$pdo->exec("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
总结:即使PHP没有显式使用事务,由于InnoDB的机制和自动提交的特性,在高并发环境下仍然可能出现死锁。良好的事务设计和错误处理是避免这类问题的关键。
介绍一下mysql的事务隔离级别,介绍一下他们的优缺点和适用场景。
MySQL 事务隔离级别详解
MySQL 支持四种标准的事务隔离级别,它们定义了事务之间如何相互影响以及数据可见性的规则。了解这些隔离级别对于设计高性能、高并发的数据库应用至关重要。
- READ UNCOMMITTED (读未提交)
特点:
-
最低的隔离级别
-
事务可以读取其他事务未提交的修改("脏读")
-
没有任何锁机制限制
优点:
-
最高性能,几乎没有锁开销
-
适合对数据一致性要求极低的场景
缺点:
-
会出现脏读、不可重复读和幻读问题
-
数据一致性难以保证
适用场景:
-
统计分析类应用,允许近似结果
-
数据仓库的ETL过程
-
对实时性要求极高但可容忍脏读的场景
- READ COMMITTED (读已提交)
特点:
-
事务只能看到其他事务已提交的修改
-
避免了脏读问题
-
使用行级锁,但只在语句执行期间持有
优点:
-
比REPEATABLE READ性能更好
-
避免了脏读问题
-
Oracle、PostgreSQL等数据库的默认级别
缺点:
-
会出现不可重复读和幻读问题
-
同一个事务中两次读取同一数据可能得到不同结果
适用场景:
-
大多数OLTP应用的合理选择
-
需要避免脏读但可接受不可重复读的场景
-
报表生成等不需要事务内一致性的操作
- REPEATABLE READ (可重复读)
特点:
-
MySQL的默认隔离级别
-
保证在同一个事务中多次读取同一数据结果相同
-
使用MVCC(多版本并发控制)和间隙锁(Gap Lock)
优点:
-
避免了脏读和不可重复读问题
-
适合需要事务内一致性的场景
缺点:
-
仍可能出现幻读问题(MySQL通过间隙锁部分解决)
-
比READ COMMITTED有更高的锁开销
-
可能导致更多的死锁
适用场景:
-
需要事务内读取一致性的应用
-
财务系统、订单处理等关键业务
-
MySQL的默认选择,适合大多数通用场景
- SERIALIZABLE (可串行化)
特点:
-
最高的隔离级别
-
通过强制事务串行执行来避免所有并发问题
-
使用范围锁和表级锁
优点:
-
完全避免脏读、不可重复读和幻读
-
提供最严格的数据一致性保证
缺点:
-
性能最差,并发度最低
-
锁争用严重,可能导致大量超时
-
不适用于高并发系统
适用场景:
-
需要绝对数据一致性的关键系统
-
银行核心系统、证券交易等金融应用
-
数据迁移或批量处理等低并发操作
隔离级别对比表
隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | 并发性 |
---|---|---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 | 最高 | 最高 |
READ COMMITTED | 避免 | 可能 | 可能 | 高 | 高 |
REPEATABLE READ | 避免 | 避免 | 可能 | 中 | 中 |
SERIALIZABLE | 避免 | 避免 | 避免 | 低 | 低 |
“可能”的意思是可能出现、可能发生;“避免”的意思是不会出现,不会发生。
选择建议
-
默认选择:大多数应用使用REPEATABLE READ(MySQL默认)或READ COMMITTED(Oracle/PostgreSQL默认)即可
-
性能优先:对一致性要求不高的场景可考虑READ COMMITTED
-
一致性优先:关键业务系统可能需要SERIALIZABLE
-
特殊需求:某些场景可以混合使用不同隔离级别(如主业务用REPEATABLE READ,报表用READ COMMITTED)
-
MySQL注意:InnoDB在REPEATABLE READ下通过间隙锁部分解决了幻读问题,这是与标准SQL的不同之处
实际应用中,隔离级别的选择需要在数据一致性和系统性能之间找到平衡点。
解释一下mysql的事务隔离级别中的脏读、幻读、不可重复读的概念和例子。
MySQL事务隔离级别中的并发问题详解
- 脏读 (Dirty Read)
概念:一个事务读取了另一个未提交事务修改过的数据。
发生条件:READ UNCOMMITTED隔离级别
例子:
-- 会话1 (事务A)
START TRANSACTION;
UPDATE eps_user SET balance = balance - 100 WHERE id = 1;
-- 尚未提交
-- 会话2 (事务B) 使用READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM eps_user WHERE id = 1; -- 读取到未提交的修改(脏数据)
-- 会话1
ROLLBACK; -- 事务A回滚
-- 会话2
SELECT balance FROM eps_user WHERE id = 1; -- 现在读取的值与之前不同
COMMIT;
影响:事务B基于从未真正存在过的数据做出了决策。
- 不可重复读 (Non-repeatable Read)
概念:在同一个事务内,多次读取同一数据返回不同结果(因为其他事务修改并提交了该数据)。期望是在同一个事务内多次读取得到相同的结果,即可以重复读。
发生条件:READ UNCOMMITTED 或 READ COMMITTED隔离级别
例子:
-- 会话1 (事务A)
START TRANSACTION;
SELECT balance FROM eps_user WHERE id = 1; -- 第一次读取,返回1000
-- 会话2 (事务B)
UPDATE eps_user SET balance = 900 WHERE id = 1;
COMMIT;
-- 会话1
SELECT balance FROM eps_user WHERE id = 1; -- 第二次读取,返回900
COMMIT;
影响:事务A中的两次读取结果不一致,可能导致业务逻辑错误。
- 幻读 (Phantom Read)
概念:在同一个事务内,多次执行相同的查询返回不同的行集合(因为其他事务新增或删除了符合查询条件的行并提交)。
发生条件:READ UNCOMMITTED、READ COMMITTED 或 REPEATABLE READ隔离级别
例子:
-- 会话1 (事务A)
START TRANSACTION;
SELECT * FROM eps_user WHERE balance > 800; -- 返回2条记录
-- 会话2 (事务B)
INSERT INTO eps_user(id, balance) VALUES(3, 1000);
COMMIT;
-- 会话1
SELECT * FROM eps_user WHERE balance > 800; -- 现在返回3条记录
COMMIT;
与不可重复读的区别:
-
不可重复读针对的是已存在行的数据变更
-
幻读针对的是行数量的变化(新增或删除的行)
MySQL各隔离级别对问题的解决情况
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 |
READ COMMITTED | 避免 | 可能 | 可能 |
REPEATABLE READ | 避免 | 避免 | 可能* |
SERIALIZABLE | 避免 | 避免 | 避免 |
*注:MySQL的InnoDB在REPEATABLE READ下通过MVCC和间隙锁(Gap Lock)机制避免了大部分幻读情况,这与SQL标准有所不同。
实际示例演示
创建测试表:
CREATE TABLE test_concurrency (
id INT PRIMARY KEY,
value VARCHAR(100)
) ENGINE=InnoDB;
INSERT INTO test_concurrency VALUES
(1, '原始数据A'),
(2, '原始数据B');
演示1:脏读
-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM test_concurrency; -- 看到原始数据
-- 会话2
START TRANSACTION;
UPDATE test_concurrency SET value = '修改未提交' WHERE id = 1;
-- 会话1
SELECT * FROM test_concurrency; -- 看到未提交的修改(脏读)
-- 会话2
ROLLBACK;
-- 会话1
SELECT * FROM test_concurrency; -- 数据恢复原状
COMMIT;
演示2:不可重复读
-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM test_concurrency WHERE id = 1; -- 第一次读取
-- 会话2
UPDATE test_concurrency SET value = '已提交修改' WHERE id = 1;
COMMIT;
-- 会话1
SELECT * FROM test_concurrency WHERE id = 1; -- 第二次读取结果不同
COMMIT;
演示3:幻读
-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM test_concurrency WHERE value LIKE '%数据%'; -- 看到2条
-- 会话2
INSERT INTO test_concurrency VALUES (3, '新数据C');
COMMIT;
-- 会话1
SELECT * FROM test_concurrency WHERE value LIKE '%数据%'; -- 仍看到2条(MVCC避免幻读)
UPDATE test_concurrency SET value = CONCAT(value, '*') WHERE value LIKE '%数据%';
-- 此时会发现3行被更新,因为UPDATE能看到最新数据
SELECT * FROM test_concurrency WHERE value LIKE '%数据%'; -- 现在看到3条
COMMIT;
理解这些并发问题有助于根据应用需求选择合适的隔离级别,在数据一致性和系统性能之间取得平衡。
如果替换InnoDB引擎为myisam引擎可以避免这种死锁么?
使用MyISAM引擎能否避免死锁
替换InnoDB为MyISAM引擎不能真正避免死锁,但死锁的表现形式和发生机制会有本质不同。以下是详细分析:
MyISAM与InnoDB锁机制的根本区别
特性 | InnoDB | MyISAM |
---|---|---|
锁粒度 | 行级锁 | 表级锁 |
锁类型 | 共享锁(S)、排他锁(X)、意向锁等 | 读锁(共享)、写锁(排他) |
死锁检测 | 支持自动检测和解决 | 不支持 |
事务支持 | 完整ACID事务 | 不支持事务 |
并发控制 | MVCC(多版本并发控制) | 纯锁机制 |
MyISAM下的"类死锁"情况
虽然MyISAM没有严格意义上的死锁(deadlock),但会出现并发写阻塞导致的长时间等待,表现类似死锁:
- 写锁排队问题:
-- 会话1:
LOCK TABLE t WRITE; -- 获取写锁
-- 长时间不释放...
-- 会话2:
LOCK TABLE t WRITE; -- 必须等待会话1释放
-- 此时所有其他会话的读写操作都会被阻塞
- 读写混合阻塞:
-- 会话1:
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE; -- 获取读锁
-- 不释放...
-- 会话2:
UPDATE t SET col1=1 WHERE id=1; -- 需要写锁,必须等待会话1释放
-- 此时会话3的普通SELECT也会被阻塞(MyISAM读会排队等待)
为什么MyISAM不能真正解决问题
-
更粗粒度的阻塞:
-
InnoDB死锁通常只影响少数几行
-
MyISAM的锁会阻塞整个表的访问
-
-
无自动解决机制:
-
InnoDB能自动检测并回滚死锁事务
-
MyISAM只能等待超时(
lock_wait_timeout
,默认31536000秒/1年!)
-
-
更严重的性能问题:
-- 即使是不相关的更新也会互相阻塞 -- 会话1: UPDATE users SET status=1 WHERE id=1; -- 会话2: UPDATE users SET name='test' WHERE id=2; -- 必须等待
-
缺乏事务支持:
// MyISAM下无法回滚 $pdo->beginTransaction(); $pdo->exec("UPDATE table1..."); // 立即生效 $pdo->exec("UPDATE table2..."); // 失败也无法回滚前一句 $pdo->commit(); // 对MyISAM无意义
何时可以考虑MyISAM
虽然不推荐用MyISAM解决死锁问题,但在以下场景可能适用:
- 只读或极少写的表(数据字典、配置表)
- 需要全文索引(MySQL 5.6前InnoDB不支持)
- 空间数据存储(GIS功能在MyISAM中更成熟)
- 极度简单的查询(无事务要求的日志表)
更好的替代方案
-
优化InnoDB使用:
-
合理设计事务范围和持续时间
-
统一操作顺序避免循环等待
-
使用
SELECT...FOR UPDATE
明确锁定
-
-
调整参数:
SET InnoDB_lock_wait_timeout = 50; -- 设置更合理的等待超时 SET InnoDB_deadlock_detect = ON; -- 确保死锁检测开启
-
架构层面:
-
引入消息队列缓解并发压力
-
考虑读写分离
-
对热点数据使用缓存
-
结论:单纯替换存储引擎不是解决死锁的正确方法。MyISAM会带来更严重的并发性能问题,而合理设计和使用InnoDB才是根本解决方案。
如何很容易的复现MySQL 死锁?
MySQL 死锁复现方法
下面我将介绍几种简单有效的方法来复现MySQL死锁场景,帮助理解死锁的形成机制。
方法一:经典交叉更新(最易实现)
准备测试表
CREATE TABLE deadlock_test (
id INT PRIMARY KEY,
value INT
) ENGINE=InnoDB;
INSERT INTO deadlock_test VALUES (1, 100), (2, 200);
复现步骤
会话1:
BEGIN;
UPDATE deadlock_test SET value = value + 1 WHERE id = 1;
-- 不要提交,保持事务开放
会话2:
BEGIN;
UPDATE deadlock_test SET value = value + 1 WHERE id = 2;
-- 不要提交,保持事务开放
会话1:
UPDATE deadlock_test SET value = value + 1 WHERE id = 2; -- 这会阻塞
会话2:
UPDATE deadlock_test SET value = value + 1 WHERE id = 1; -- 触发死锁
此时MySQL会检测到死锁并自动回滚其中一个事务。
方法二:Gap Lock导致的死锁
准备测试表
CREATE TABLE gap_lock_test (
id INT PRIMARY KEY,
name VARCHAR(20)
) ENGINE=InnoDB;
INSERT INTO gap_lock_test VALUES (1, 'A'), (3, 'C'), (5, 'E');
复现步骤
会话1:
BEGIN;
SELECT * FROM gap_lock_test WHERE id BETWEEN 2 AND 4 FOR UPDATE; -- 获取gap锁
会话2:
BEGIN;
SELECT * FROM gap_lock_test WHERE id BETWEEN 2 AND 4 FOR UPDATE; -- 同样获取gap锁
会话1:
INSERT INTO gap_lock_test VALUES (2, 'B'); -- 尝试在gap中插入
会话2:
INSERT INTO gap_lock_test VALUES (4, 'D'); -- 触发死锁
方法三:并发插入唯一键冲突
准备测试表
CREATE TABLE unique_conflict (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) UNIQUE
) ENGINE=InnoDB;
复现步骤
会话1:
BEGIN;
INSERT INTO unique_conflict (username) VALUES ('test_user');
-- 不要提交
会话2:
BEGIN;
INSERT INTO unique_conflict (username) VALUES ('test_user'); -- 阻塞
会话1:
INSERT INTO unique_conflict (username) VALUES ('test_user'); -- 触发死锁
方法四:混合读写操作死锁
准备测试表
CREATE TABLE mixed_ops (
id INT PRIMARY KEY,
balance DECIMAL(10,2)
) ENGINE=InnoDB;
INSERT INTO mixed_ops VALUES (1, 1000.00), (2, 2000.00);
复现步骤
会话1:
BEGIN;
SELECT * FROM mixed_ops WHERE id = 1 FOR UPDATE;
会话2:
BEGIN;
SELECT * FROM mixed_ops WHERE id = 2 FOR UPDATE;
会话1:
SELECT * FROM mixed_ops WHERE id = 2 LOCK IN SHARE MODE; -- 阻塞
会话2:
SELECT * FROM mixed_ops WHERE id = 1 LOCK IN SHARE MODE; -- 触发死锁
查看死锁信息
发生死锁后,可以查看详细信息:
SHOW ENGINE INNODB STATUS\G
在输出中查找"LATEST DETECTED DEADLOCK"部分。
复现技巧提示
- 使用
BEGIN
显式开启事务而非START TRANSACTION
(更易控制) - 在事务中适当添加
SELECT SLEEP(1)
人为制造时间差 - 使用多个终端或MySQL客户端窗口模拟并发
- 先让两个会话各自获取不同的锁,再尝试获取对方持有的锁
自动化测试脚本
#!/bin/bash
# 死锁自动复现脚本(需要两个MySQL客户端)
# 会话1操作
mysql -e "
CREATE TABLE IF NOT EXISTS deadlock_demo(id INT PRIMARY KEY, v INT) ENGINE=InnoDB;
TRUNCATE TABLE deadlock_demo;
INSERT INTO deadlock_demo VALUES (1,100),(2,200);
BEGIN;
UPDATE deadlock_demo SET v = v + 1 WHERE id = 1;
SELECT SLEEP(1); -- 等待会话2执行
UPDATE deadlock_demo SET v = v + 1 WHERE id = 2;
COMMIT;
" &
# 会话2操作
mysql -e "
BEGIN;
UPDATE deadlock_demo SET v = v + 1 WHERE id = 2;
SELECT SLEEP(1); -- 等待会话1执行
UPDATE deadlock_demo SET v = v + 1 WHERE id = 1;
COMMIT;
" &
wait
echo "检查死锁日志:"
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 50 "LATEST DETECTED DEADLOCK"
通过以上方法,可以轻松复现并研究各种MySQL死锁场景,帮助更好地理解和解决生产环境中的死锁问题。
MySQL InnoDB 对死锁的处理机制
InnoDB 存储引擎对死锁有一套完整的检测和处理机制,以下是其核心处理方式:
- 死锁检测机制
自动检测:
-
默认启用 (
innodb_deadlock_detect=ON
) -
周期性检查事务等待图(wait-for graph)中的循环依赖
-
检测频率由系统负载决定,通常非常快速(毫秒级)
检测原理:
-
构建事务等待图(有向图)
-
检查图中是否存在环(cycle)
-
使用深度优先搜索(DFS)算法查找环
- 死锁处理策略
当检测到死锁时,InnoDB会:
-
选择牺牲者(victim):
-
根据事务权重(通常依据修改的数据量)
-
选择回滚代价较小的事务作为牺牲者
-
可通过
innodb_deadlock_detect_algorithm
配置算法(默认DEPTH_FIRST
)
-
-
回滚操作:
-
自动回滚被选中的整个事务
-
释放该事务持有的所有锁
-
写入undo日志保证数据一致性
-
-
错误返回:
-
向客户端返回
ERROR 1213 (40001): Deadlock found
-
在
SHOW ENGINE INNODB STATUS
中记录详细死锁信息
-
-
相关配置参数
参数名 | 默认值 | 说明 |
---|---|---|
innodb_deadlock_detect |
ON | 是否启用死锁检测 |
innodb_lock_wait_timeout |
50(秒) | 锁等待超时时间 |
innodb_deadlock_detect_algorithm |
DEPTH_FIRST | 检测算法 |
innodb_print_all_deadlocks |
OFF | 是否记录所有死锁到错误日志 |
- 特殊情况处理
禁用死锁检测时
(innodb_deadlock_detect=OFF
)
-
依赖
innodb_lock_wait_timeout
处理死锁 -
不推荐生产环境使用,可能导致长时间阻塞
锁升级情况
-
当锁请求需要等待超过
innodb_lock_wait_timeout
时 -
会返回
ERROR 1205 (HY000): Lock wait timeout exceeded
- 死锁信息记录
死锁发生时,InnoDB会在以下位置记录信息:
-
错误日志:
# 需开启 SET GLOBAL innodb_print_all_deadlocks = ON;
-
引擎状态:
SHOW ENGINE INNODB STATUS\G
查看"LATEST DETECTED DEADLOCK"部分
-
性能模式:
SELECT * FROM performance_schema.events_transactions_current;
-
最佳实践建议
-
应用层处理:
-
实现自动重试逻辑(针对死锁错误)
-
添加适当的休眠时间(指数退避)
-
-
数据库设计:
-
统一事务操作顺序
-
合理设计索引减少锁定范围
-
-
监控:
-- 监控死锁频率 SELECT count_star, sum_timer_wait/1000000000 as total_sec FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name = 'wait/io/table/sql/handler';
InnoDB的死锁处理机制设计精巧,能在保证数据一致性的同时,最小化死锁对系统的影响。了解这些机制有助于开发更健壮的数据库应用。
指数退避(Exponential Backoff)详解
指数退避是一种在计算机科学和网络通信中广泛使用的重试策略,特别适用于处理死锁、网络拥塞或资源竞争等情况。
基本概念
指数退避的核心思想是:每次重试前等待的时间呈指数增长,而不是固定间隔或随机间隔。这种设计可以:
- 避免大量请求同时重试导致的"惊群效应"
- 在系统恢复过程中逐渐增加负载
- 平衡即时响应和系统保护的需求
算法原理
典型的指数退避算法包含以下要素:
- 初始等待时间(base delay):如50ms
- 最大等待时间(max delay):如5秒
- 退避因子(通常为2)
- 随机抖动(jitter):避免同步重试
公式表示:
wait_time = min((2^n) * base_delay, max_delay) ± random_jitter
其中n是重试次数
MySQL死锁处理中的应用
在处理MySQL死锁时,指数退避的Java实现示例:
int retryCount = 0;
int maxRetries = 5;
long baseDelayMs = 50;
long maxDelayMs = 5000;
while (retryCount < maxRetries) {
try {
// 尝试执行数据库操作
executeTransaction();
break;
} catch (SQLException e) {
if (e.getErrorCode() == 1213) { // 死锁错误码
retryCount++;
if (retryCount >= maxRetries) {
throw e;
}
// 计算等待时间(指数退避)
long waitTime = Math.min(
(long) (baseDelayMs * Math.pow(2, retryCount)),
maxDelayMs
);
// 添加随机抖动(±20%)
waitTime = waitTime * (80 + (int)(Math.random() * 40)) / 100;
Thread.sleep(waitTime);
} else {
throw e;
}
}
}
为什么使用指数退避?
- 避免雪崩效应:当多个事务同时遇到死锁时,随机化的指数等待可以分散重试时间点
- 自适应负载:系统越繁忙,等待时间越长,自动适应系统状态
- 公平性:较早遇到错误的事务会获得较早的重试机会
实际应用场景
除了数据库死锁,指数退避还常用于:
- 网络请求失败重试(HTTP API调用)
- 分布式锁获取
- 消息队列消费失败处理
- 微服务间的服务调用
变体算法
- 线性退避:等待时间线性增长(如每次增加固定值)
- 多项式退避:等待时间按多项式函数增长
- 自适应退避:根据系统反馈动态调整参数
PHP 实现 MySQL 死锁处理的指数退避机制
以下是使用 PHP 实现指数退避策略处理 MySQL 死锁的完整代码示例:
<?php
function executeWithRetry($maxRetries = 5, $baseDelayMs = 50, $maxDelayMs = 5000) {
$retryCount = 0;
$lastException = null;
while ($retryCount < $maxRetries) {
try {
// 尝试执行数据库操作
return executeTransaction();
} catch (PDOException $e) {
$lastException = $e;
// 检查是否为死锁错误 (MySQL 死锁错误码为 1213)
if ($e->errorInfo[1] == 1213) {
$retryCount++;
if ($retryCount >= $maxRetries) {
break;
}
// 计算等待时间(指数退避)
$waitTime = min(
$baseDelayMs * pow(2, $retryCount),
$maxDelayMs
);
// 添加随机抖动(±20%)
$jitter = 0.8 + mt_rand(0, 40) / 100;
$waitTime = $waitTime * $jitter;
// 转换为微秒
usleep($waitTime * 1000);
} else {
// 非死锁错误直接抛出
throw $e;
}
}
}
// 重试次数用尽后抛出最后捕获的异常
throw $lastException;
}
function executeTransaction() {
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$pdo->beginTransaction();
// 执行你的SQL语句
$stmt = $pdo->prepare("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
$stmt->execute();
// 更多SQL操作...
$pdo->commit();
return true;
} catch (PDOException $e) {
$pdo->rollBack();
throw $e; // 重新抛出异常供外部处理
}
}
// 使用示例
try {
$result = executeWithRetry();
echo "操作成功执行";
} catch (PDOException $e) {
echo "操作失败,最终错误: " . $e->getMessage();
}
?>
关键点说明
-
指数退避计算:
-
使用
pow(2, $retryCount)
实现指数增长 -
min()
函数确保不超过最大等待时间
-
-
随机抖动:
-
通过
mt_rand()
生成 0.8 到 1.2 之间的随机因子 -
避免多个客户端同步重试
-
-
等待实现:
- 使用
usleep()
进行毫秒级等待(注意参数是微秒)
- 使用
-
错误处理:
-
专门捕获
PDOException
-
检查
errorInfo[1]
获取 MySQL 特定错误码 -
只有死锁错误(1213)才会触发重试
-
-
事务管理:
-
每个重试都会开启新的事务
-
确保每次重试都是全新的操作
-
实际应用建议
-
参数调整:
// 生产环境推荐设置 executeWithRetry( $maxRetries = 3, // 最大重试次数 $baseDelayMs = 100, // 初始等待100ms $maxDelayMs = 3000 // 最长等待3秒 );
-
日志记录:
// 在catch块中添加日志记录 error_log("死锁发生,第{$retryCount}次重试,等待{$waitTime}ms");
-
监控集成:
-
可以添加计数器统计死锁发生次数
-
与监控系统集成报警
-
这个实现保持了指数退避的核心优势,同时适应PHP的语言特性,是处理MySQL死锁的可靠方案。
在数据库应用中,指数退避因其简单有效而成为处理死锁的首选策略。通过合理设置初始等待时间和最大等待时间,可以在系统响应速度和稳定性之间取得良好平衡。