The MySQL Server has a few options to help deal with transactions holding InnoDB locks for excessive periods of time. The –innodb-lock-wait-timeout option is one such option, but that just affects statements waiting on locks already held by another transaction. If you want to ensure your application isn’t holding locks for long periods of time to start with, what options do you have?
Well, the manual has a useful example that leverages InnoDB tables in INFORMATION_SCHEMA to show which transactions are locking rows needed by other transactions. That’s cool stuff, and you can take this a step further by looking for all connections with open transactions which exceed a given threshold or are blocking other transactions:
mysql> SET @threshold = 30;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT p.user,
-> LEFT(p.HOST, LOCATE(':', p.HOST) - 1) host, p.id,
-> TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) duration,
-> COUNT(DISTINCT ot.REQUESTING_TRX_ID) waiting
-> FROM INFORMATION_SCHEMA.INNODB_TRX t
-> JOIN INFORMATION_SCHEMA.PROCESSLIST p
-> ON ( p.ID = t.TRX_MYSQL_THREAD_ID )
-> LEFT JOIN INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot
-> ON ( ot.BLOCKING_TRX_ID = t.TRX_id )
-> WHERE t.TRX_STARTED + INTERVAL @threshold SECOND <= NOW()
-> GROUP BY LEFT(p.HOST, LOCATE(':', p.HOST) - 1), p.id, duration
-> HAVING duration >= @threshold OR waiting > 0;
+------+-----------+----+----------+---------+
| user | host | id | duration | waiting |
+------+-----------+----+----------+---------+
| root | localhost | 4 | 1062 | 0 |
| ODBC | localhost | 5 | 276 | 0 |
+------+-----------+----+----------+---------+
2 rows in set (0.08 sec)
The next step you can take is to create an EVENT which logs this, or even issues KILL CONNECTION (sadly, there is no KILL TRANSACTION, and KILL QUERY doesn’t rollback the transaction) commands on the blocking transaction. You can also create control tables that allow you to fine-tune reporting or action thresholds for specific user/host combinations:
CREATE TABLE test.innodb_trx_kill_threshold (
user VARCHAR(100) NOT NULL,
host VARCHAR(100) NOT NULL,
trx_kill_threshold INT UNSIGNED,
PRIMARY KEY (user, host)
) ENGINE = InnoDB;
CREATE TABLE test.innodb_trx_kill_audit (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
user VARCHAR(100) NOT NULL,
host VARCHAR(100) NOT NULL,
connection_id INT UNSIGNED NOT NULL,
trx_duration INT UNSIGNED NOT NULL,
other_trx_waiting INT UNSIGNED NOT NULL DEFAULT 0,
kill_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY (user, host, connection_id)
) ENGINE = InnoDB;
CREATE OR REPLACE VIEW test.innodb_trx_past_threshhold AS
SELECT p.user,
LEFT(p.HOST, LOCATE(':', p.HOST) - 1) host, p.id,
TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) duration,
IFNULL(kt.trx_kill_threshold, 30) threshold,
(SELECT COUNT(ot.REQUESTING_TRX_ID)
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot
WHERE ot.BLOCKING_TRX_ID = t.TRX_id) waiting
FROM INFORMATION_SCHEMA.INNODB_TRX t
JOIN INFORMATION_SCHEMA.PROCESSLIST p
ON ( p.ID = t.TRX_MYSQL_THREAD_ID )
LEFT JOIN test.innodb_trx_kill_threshold kt
ON ( p.USER = kt.user AND LEFT(p.HOST, LOCATE(':', p.HOST) - 1) = kt.host )
WHERE t.TRX_STARTED + INTERVAL IFNULL(kt.trx_kill_threshold, 30) SECOND 0 ;
DELIMITER ||
CREATE PROCEDURE kill_long_innodb_trx (IN kill_connections INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE l_user, l_host VARCHAR(100);
DECLARE l_timeout, l_duration, l_conn_id, l_other_trx_waiting INT UNSIGNED;
DECLARE cur CURSOR FOR
SELECT user, host, id, duration, threshold, waiting
FROM test.innodb_trx_past_threshhold;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO l_user, l_host, l_conn_id, l_duration, l_timeout, l_other_trx_waiting;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO test.innodb_trx_kill_audit
( user, host, connection_id, trx_duration, other_trx_waiting)
VALUES
( l_user, l_host, l_conn_id, l_duration, l_other_trx_waiting )
ON DUPLICATE KEY UPDATE trx_duration = l_duration,
other_trx_waiting = GREATEST(other_trx_waiting, l_other_trx_waiting);
IF kill_connections THEN
SET @stmt = CONCAT('KILL CONNECTION ', l_conn_id);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
END IF;
END LOOP;
CLOSE cur;
END;
||
DELIMITER ;
CREATE EVENT innodb_trx_killer ON SCHEDULE EVERY 5 SECOND DO CALL kill_long_innodb_trx (1);
And now there’s an EVENT running every 5 seconds which will kill connections having InnoDB transactions older than the threshold (30s, unless configured differently for the user/host combination in the control table). It will also log information to an audit table (test.innodb_trx_kill_audit) about the connections before they are killed. One could easily tweak the stored procedure to simply record the audit details instead of killing connections. Here’s an example of this in action:
mysql> BEGIN;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO p VALUES (2, 3);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT SLEEP(35);
+-----------+
| SLEEP(35) |
+-----------+
| 0 |
+-----------+
1 row in set (35.00 sec)
mysql> COMMIT;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
And the audit table:
mysql> SELECT * FROM test.innodb_trx_kill_audit\G
*************************** 1. row ***************************
id: 1
user: ODBC
host: localhost
connection_id: 157
trx_duration: 31
other_trx_waiting: 0
kill_time: 2012-08-28 13:08:07
1 row in set (0.00 sec)
Note: It’s been pointed out to me that there are a few similar, earlier suggestions on how to do this, or something similar.