MySQL-行锁超时记录监控

MySQL行锁超时默认innodb_lock_wait_timeout=50秒，也可以修改为更短的时间，防止cpu突高。
行锁一但超时，MySQL本身没有记录，不利于排查冲突的sql语句。
偶然间看到《MySQL大智小计》中MySQL行锁超时排查方法优化这篇文章，感觉很实用，记录一下

此方法基于 事件 + 存储过程的思路所以需要打开相应参数：
set global performance_schema = on;
set gloabl event_scheduler = 1;

一. MySQL-5.7版本 (mysql8.0请往下走)

创建记录所需要的数据库
create database `innodb_monitor`;

创建存储过程
use innodb_monitor;

delimiter ;;

CREATE PROCEDURE pro_innodb_lock_wait_check()
BEGIN
declare wait_rows int;
 
set group_concat_max_len = 1024000;

CREATE TABLE IF NOT EXISTS `innodb_lock_wait_log` (
`report_time` datetime DEFAULT NULL,
`waiting_id` int(11) DEFAULT NULL,
`blocking_id` int(11) DEFAULT NULL,
`duration` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`waiting_query` longtext DEFAULT NULL,
`blocking_current_query` longtext DEFAULT NULL,
`blocking_thd_last_query` longtext,
`thread_id` int(11) DEFAULT NULL
);

select count(*) into wait_rows from information_schema.innodb_lock_waits ;

if wait_rows > 0 THEN
  insert into `innodb_lock_wait_log` 
  SELECT
	now( ),
	r.trx_mysql_thread_id waiting_id,
	b.trx_mysql_thread_id blocking_id,
	concat( timestampdiff( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ( ) ), 's' ) AS duration,
	t.processlist_command state,
	r.trx_query waiting_query,
	b.trx_query blocking_current_query,
	group_concat( LEFT ( h.sql_text, 10000 ) ORDER BY h.TIMER_START DESC SEPARATOR ';\n' ) AS blocking_thd_query_history,
thread_id 
FROM
	information_schema.innodb_lock_waits w
	JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
	JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
	LEFT JOIN PERFORMANCE_SCHEMA.threads t ON t.processlist_id = b.trx_mysql_thread_id
	LEFT JOIN PERFORMANCE_SCHEMA.events_statements_history h USING ( thread_id ) 
GROUP BY
	thread_id,
	r.trx_id,
	r.trx_mysql_thread_id 
ORDER BY
	r.trx_wait_started;
end if;
END
;;

delimiter ;

创建事件
事件每隔 5 秒 （通常等于 innodb_lock_wait_timeout 的值）执行一次，持续监控 7 天，结束后会自动删除事件，也可以自定义保留时长。
use innodb_monitor;
delimiter ;;
CREATE EVENT `event_innodb_lock_wait_check`
ON SCHEDULE EVERY 5 SECOND
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 7 DAY
ON COMPLETION NOT PRESERVE
ENABLE
DO
call pro_innodb_lock_wait_check();
;;

delimiter ;

事件启停
--1 为全局开启事件,0 为全局关闭
mysql > SET GLOBAL event_scheduler = 1;
--临时关闭事件
mysql > ALTER EVENT event_innodb_lock_wait_check DISABLE;
--关闭开启事件
mysql > ALTER EVENT event_innodb_lock_wait_check ENABLE;

二. MySQL-8.0版本

创建记录所需要的数据库
create database `innodb_monitor`;

创建存储过程
use innodb_monitor;

delimiter ;;

CREATE PROCEDURE pro_innodb_lock_wait_check()
BEGIN
declare wait_rows int;
 
set group_concat_max_len = 1024000;

CREATE TABLE IF NOT EXISTS `innodb_lock_wait_log` (
`report_time` datetime DEFAULT NULL,
`waiting_id` int(11) DEFAULT NULL,
`blocking_id` int(11) DEFAULT NULL,
`duration` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`waiting_query` longtext DEFAULT NULL,
`blocking_current_query` longtext DEFAULT NULL,
`blocking_thd_last_query` longtext,
`thread_id` int(11) DEFAULT NULL
);

select count(*) into wait_rows from sys.innodb_lock_waits ;

if wait_rows > 0 THEN
  insert into `innodb_lock_wait_log` 
  SELECT
	t5.*,
	t4.PROCESSLIST_INFO AS waiting_sql 
FROM
	performance_schema.threads t4,
	(
SELECT
	t1.wait_started,
	t1.wait_age,
	t1.waiting_trx_id,
	t1.waiting_pid,
	t1.blocking_trx_id,
	t1.blocking_pid,
	t1.blocking_trx_age,
	t2.THREAD_ID AS block_thread_id,
	t4.SQL_TEXT AS current_sql,
	t3.SQL_TEXT AS history_sql 
FROM
	sys.innodb_lock_waits t1,
	performance_schema.threads t2,
	(
SELECT
	thread_id,
	group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text 
FROM
	performance_schema.events_statements_history 
GROUP BY
	thread_id 
	) t3,
	performance_schema.events_statements_current t4 
WHERE
	t1.blocking_pid = t2.PROCESSLIST_ID 
	AND t2.THREAD_ID = t3.THREAD_ID 
	AND t2.THREAD_ID = t4.THREAD_ID 
	) t5 
WHERE
	t5.waiting_pid = t4.PROCESSLIST_ID;
end if;
END
;;

delimiter ;

事件启停
--1 为全局开启事件,0 为全局关闭
mysql > SET GLOBAL event_scheduler = 1;
--临时关闭事件
mysql > ALTER EVENT event_innodb_lock_wait_check DISABLE;
--关闭开启事件
mysql > ALTER EVENT event_innodb_lock_wait_check ENABLE;