mysql查看死锁,查看与分析死锁的完整指南
死锁监控基础命令
查看最近一次死锁信息
SHOW ENGINE INNODB STATUS\G
在输出结果中查找"LATEST DETECTED DEADLOCK"部分,会显示:
死锁发生时间
涉及的事务ID
被选为牺牲品(victim)的事务
每个事务持有的锁和等待的锁
开启InnoDB监控(持续输出)
-- 创建监控表
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
-- 查看监控信息(会定期刷新)
SHOW ENGINE INNODB STATUS\G
-- 关闭监控
DROP TABLE innodb_monitor;
系统表查询方法
查询当前锁等待情况
SELECT * FROM performance_schema.events_waits_current
WHERE EVENT_NAME LIKE '%lock%';
查看被阻塞的事务
SELECT * FROM sys.innodb_lock_waits;
这个视图会显示:
等待锁的事务
持有锁的事务
被锁定的资源信息
死锁日志配置
永久开启死锁日志记录
修改my.cnf/my.ini配置文件:
[mysqld]
innodb_print_all_deadlocks = ON
log-error = /var/log/mysql/mysql-error.log
重启MySQL服务
验证配置
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
可视化工具分析
使用MySQL Workbench:
打开"Performance"面板
查看"InnoDB Deadlocks"图表
使用pt-deadlock-logger(Percona工具):
pt-deadlock-logger h=localhost,u=root,p=password
死锁预防检查
查看锁等待超时设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
检查事务隔离级别
SELECT @@transaction_isolation;
实战案例分析
典型死锁日志解读示例
LATEST DETECTED DEADLOCK
2023-05-01 10:20:00 0x7f8e5c2e9700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 10 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 100, OS thread handle 139832, query id 1000 localhost root updating
UPDATE accounts SET balance = balance - 100 WHERE id = 1
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index PRIMARY of table test
.accounts
trx id 123456 lock_mode X locks rec but not gap
...
*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 101, OS thread handle 139833, query id 1001 localhost root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 5 n bits 72 index PRIMARY of table test
.accounts
trx id 123457 lock_mode X locks rec but not gap
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 5 n bits 72 index PRIMARY of table test
.accounts
trx id 123457 lock_mode X locks rec but not gap waiting
...
*** WE ROLL BACK TRANSACTION (2)
解决方案与最佳实践
应用层优化:
统一事务中的SQL操作顺序
减小事务范围,尽早提交
添加合理的重试机制
数据库配置:
调整innodb_deadlock_detect参数
合理设置锁等待超时时间
监控告警:
使用脚本监控死锁
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 50 "LATEST DETECTED DEADLOCK"
以上就是关于mysql查看死锁的介绍。还有一款非常便捷的MYSQL导出、导入备份工具也运用的很不错,“80KM-mysql备份工具”。 可定时备份、异地备份,MYSQL导出导入。可本地连接LINUX里的MYSQL,简单便捷。
通过以上方法,您可以全面掌握MySQL中的死锁情况,快速定位并解决死锁问题,保证数据库稳定运行。