Using MySQL Events and Stored Procedures to Monitor InnoDB Lock Waits

The author describes an improved method for capturing InnoDB lock‑wait information directly inside MySQL, avoiding external shell scripts and general_log parsing. By leveraging MySQL Events and a stored procedure, lock‑wait details are recorded into a dedicated log table together with transaction context from performance_schema .

Outline

#### 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 = ?

The original approach used a shell script plus general_log to capture lock‑wait events. The new approach uses an Event that periodically calls a stored procedure to insert lock‑wait records into a table.

Key MySQL configuration required:

performance_schema = on
event_scheduler = 1

Step 2.1 – Create Monitoring Database

create database `innodb_monitor`;

Step 2.2 – Create Stored Procedure

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
    ORDER BY r.trx_wait_started;
 end if;
END
;;

Step 2.3 – Create Scheduled Event

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

Step 2.4 – Enable/Disable Event Scheduler

-- Enable globally
SET GLOBAL event_scheduler = 1;

-- Temporarily disable the event
ALTER EVENT event_innodb_lock_wait_check DISABLE;

-- Re‑enable the event
ALTER EVENT event_innodb_lock_wait_check ENABLE;

Log Table Analysis

The generated innodb_lock_wait_log contains rows that can be examined to determine two main scenarios:

If blocking_current_query is not null, the blocking transaction is still running and its SQL should be checked for performance issues.

If blocking_current_query is null and state shows Sleep , the blocking transaction is idle; analysis should focus on blocking_thd_last_query , which stores the historical queries in descending time order.

An example screenshot of the log table is included in the original article.