MySQL锁故障排查

1 现象:

这样一个场景,我们 top 看到服务器 CPU 占用超级高,等待也很高,查询慢日志发现有些 SQL 执行特别久。

2 查询是否有锁等待

show status like 'innodb_row_lock%';

对于各个状态说明如下:
Innodb_row_lock_current_waits:当前正在等待锁的数量;
Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
Innodb_row_lock_time_avg:每次等待所花平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度;
Innodb_row_lock_waits:系统启动到现在总共等待的次数;

3. 查看哪个事务在等待

select * from information_schema.INNODB_TRX where trx_state='LOCK WAIT'\G

其中主要的几个参数:

trx_id:事务 ID

trx_state:事务状态

trx_mysql_thread_id:连接线程的 ID,就是 show processlist 看到的 ID。

trx_query:当前被阻塞的 SQL

4 查看谁锁的

select * from sys.innodb_lock_waits\G

locked_table:哪张表被锁住。

waiting_trx_id:等待的事务 ID。

waiting_pid:等待的线程号。

blocking_trx_id:锁源的事务 ID。

blocking_pid:锁源的线程号。

5 查看锁源

select * from performance_schema.threads where processlist_id=3\G

通过上一步获取到的锁源 PID 查询

6 找到具体锁的 SQL 是哪一个

-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

7 涉及到锁监控的一些命令

show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;

8 处理锁的问题

当我们发现锁并且定位到具体的 SQL 以后,就需要对该问题进行处理:

1. 当 SQL 不是很重要,不是什么大事务或者重要的事务的时候,我们可以通过 kill 的方式杀掉 processlist 中的 PID。

2. 当然最终的决绝办法还是让开发修改业务处理逻辑。

3 对于死锁,我们可以将其记录到日志中,设置方法:

innodb_print_all_deadlocks = 1 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值