mysql行锁超时排查处理

一. 大纲

20191219 10:10:10,234 | com.alibaba.druid.filter.logging.Log4jFilter.statementLogError(Log4jFilter.java:152) | ERROR | {conn-10593, pstmt-38675} execute error. update xxx set xxx = ? , xxx = ? where RowGuid = ?

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

之前在如何有效排查解决MySQL行锁等待超时问题帖子中介绍了如何监控解决行锁超时报错，当时介绍的监控方案主要是以shell脚本 + general_log来捕获行锁等待信息，后来想了想这种方式比较麻烦，因此改良了下，使用Event + Procedure的方法定时在MySQl内进行监控，然后将行锁等待信息记录到日志表中，这样可以省去登陆服务器的过程，更为简便。

大家如果在项目中遇到该报错，可以先用以下方式进行监控，再通过日志表分析。

二. 步骤

该方式只适用于公司bin包部署的MySQL，不适用于其他自行安装或RDS上的MySQL。

2.1. 创建库

--最好新建个库,用现有库也可以

create database `innodb_monitor`;

2.2. 创建存储过程

dbeaver中创建存储过程需要选择执行SQL脚本，是否无法创建。

MySQL5.7与MySQL8.0的存储过程略有不同，主要是系统表名发生了变化，请针对版本创建存储过程。

2.2.1. MySQL5.7存储过程

-- <MySQL5.7版本> ---
use innodb_monitor;
delimiter go
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 order by r.trx_wait_started;
end if;
END
go

2.2.2. MySQL8.0存储过程

-- <MySQL8.0版本> ---
use innodb_monitor;
delimiter go
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 performance_schema.data_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 performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_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 order by r.trx_wait_started;
end if;
END
go

2.3. 创建事件 dbeaver中创建事件需要选择执行SQL脚本，是否无法创建。

默认每隔5秒执行一次，持续监控7天，结束后会自动删除事件，可以自定义修改保留时长。当事件超过7天被自动删除后，可以重新建立事件继续运行。

use innodb_monitor;
delimiter go
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();
go

2.4. 事件启停默认不需要进行如下命令，事件创建后会自动执行，如需手动停止Event则可以参考如下命令。

--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;

三. 日志表最后我们可以根据日志表innodb_lock_wait_log中的记录，分析行锁等待原因，其中主要有2种场景：

blocking_current_query列不为空：说明阻塞事务处于运行状态，这时候需要分析当前运行SQL是否存在性能问题。

blocking_current_query为空，state为Sleep：此时阻塞事务处于挂起状态（即不在运行SQL），此时需要通过分析blocking_thd_last_query分析挂起事务上下文，注意该列中的SQL为时间降序，即从下往上执行（默认只能保留10条记录）。

下图是手动模拟的行锁等待场景，因为blocking_current_query为空，说明阻塞事务处于挂起状态，从blocking_thd_last_query列可以看到阻塞事务之前执行过的SQL，而Waiting_query列则代表当前被阻塞的SQL语句，往往也是web日志中执行超时的报错SQL。

GPT-4

这篇博客详细地介绍了如何排查和处理MySQL行锁超时问题，对于那些在处理数据库问题时遇到困难的读者来说，这是一篇非常有用的文章。

首先，我必须赞扬你的核心理念，即通过创建库和存储过程，以及使用事件来定时监控MySQL内部的行锁等待信息，并将其记录到日志表中。这种方法不仅可以省去登陆服务器的过程，而且能够更为直观和方便地查看和分析行锁等待信息，从而找出问题的根源。

其次，你的文章结构清晰，步骤详细，逻辑严谨，对于初学者来说非常友好。你针对MySQL5.7和MySQL8.0的存储过程有所不同，分别给出了详细的创建过程，这使得读者能够根据自己的数据库版本选择适合的方法。

然而，文章中有一些地方还有改进的空间。首先，你在文章中多次提到了“dbeaver中创建存储过程需要选择执行SQL脚本，是否无法创建。”这句话的表述有些不清晰，可能会让读者产生困惑。如果你是想说在dbeaver中创建存储过程时可能会遇到问题，建议你能够提供一些解决这个问题的方法或者建议。

其次，文章中的代码部分，如果能有更详细的注释说明每一步的作用和意义，对于读者来说会更有帮助。

最后，对于日志表的分析部分，如果能够提供一些具体的例子和解决方案，那么对于读者来说会更有实际价值。

总的来说，这是一篇非常实用和有价值的博客，我期待你在未来的文章中继续分享你的知识和经验。

这篇博客文章详细介绍了如何排查和处理MySQL行锁超时的问题，其中的核心理念是通过创建库、创建存储过程、创建事件以及事件的启停等步骤，实现对MySQL行锁等待超时问题的监控和记录，从而便于分析和解决问题。这个方法的优点是可以省去登陆服务器的过程，使得监控和记录更为简便。

文章的一个闪光点是详细的步骤指导和对应的SQL脚本，这对于读者来说非常友好，可以直接复制和粘贴使用。另一个亮点是对不同MySQL版本的存储过程进行了区别处理，这展示了作者的专业性和全面性。

然而，文章的一个可以改进的地方是，对于一些专业术语和概念，例如“bin包部署的MySQL”、“阻塞事务处于运行状态”、“阻塞事务处于挂起状态”等，作者没有进行详细的解释，可能会让一些不太熟悉MySQL的读者感到困惑。建议作者在使用这些专业术语和概念时，能够加上简单的解释或者给出相关的参考链接。

此外，文章结尾的日志表分析部分也可以进一步优化。例如，作者可以给出一些具体的分析示例，以帮助读者更好地理解如何从日志表中找出问题的原因。同时，对于如何解决这些问题，作者也可以给出一些具体的建议或者解决方案。

总的来说，这篇博客文章是一篇非常实用和专业的技术文章，对于有需要解决MySQL行锁超时问题的读者来说，是一篇非常有价值的参考资料。

小豪

一. 大纲

20191219 10:10:10,234 | com.alibaba.druid.filter.logging.Log4jFilter.statementLogError(Log4jFilter.java:152) | ERROR | {conn-10593, pstmt-38675} execute error. update xxx set xxx = ? , xxx = ? where RowGuid = ?

二. 步骤

2.1. 创建库

2.2. 创建存储过程