【12】MySQL:锁处理

时间:2022-05-22 05:02:22

写在前面的话

 

在前面的内容中提到过,在以前的 MyISAM 中锁是表级锁,InnoDB 是行级锁。这个锁到底是啥样,怎么找出来,这一节就主要做这个。

 

 

定位锁的问题

 

上一节我们创建了一个 1000万数据的表,这里会用到。

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

 

1. 查看锁等待:

在一个 session 中执行:

use testdb1;
UPDATE  t_100w SET k1=az WHERE id=10008;

在另外一个 session 中执行:

use testdb1;
UPDATE  t_100w SET k1=qz WHERE id=10008;

在另外一个 session 此时我们查看:

show status like innodb_row_lock%;

结果:

【12】MySQL:锁处理

可以看到有一个 row lock 等待。

 

2. 查看哪个事务在等待:

select * from information_schema.INNODB_TRX where trx_state=LOCK WAITG

结果:

【12】MySQL:锁处理

其中主要的几个参数:

trx_id:事务 ID

trx_state:事务状态

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

trx_query:当前被阻塞的 SQL

 

3. 查看谁锁的:

select * from sys.innodb_lock_waitsG

结果:

【12】MySQL:锁处理

locked_table:哪张表被锁住。

waiting_trx_id:等待的事务 ID。

waiting_pid:等待的线程号。

blocking_trx_id:锁源的事务 ID。

blocking_pid:锁源的线程号。

 

4. 查看锁源:

select * from performance_schema.threads where processlist_id=3G

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

【12】MySQL:锁处理

 

5. 找到具体锁的 SQL 是哪一个。

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

 

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

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;

 

 

处理锁的问题

 

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

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

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

 

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

innodb_print_all_deadlocks = 1 

 

 

小结

 

业务中很容易出现锁的情况,毕竟开发水平参差不齐,有些烂 SQL 没法避免。这就需要我们使用慢日志结合锁监控一起,定位问题,最终优化解决问题。