Skip to content

Instantly share code, notes, and snippets.

@olivertappin
Created November 16, 2019 19:06
Show Gist options
  • Save olivertappin/e61a85766be7f902965e00fb5b30c7a0 to your computer and use it in GitHub Desktop.
Save olivertappin/e61a85766be7f902965e00fb5b30c7a0 to your computer and use it in GitHub Desktop.
Run this query to check for a blocking transaction
SELECT
trx_w.trx_id AS waiting_trx_id,
trx_w.trx_mysql_thread_id AS waiting_process_id,
TIMESTAMPDIFF(SECOND, trx_w.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time,
trx_w.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
trx_b.trx_id AS blocking_trx_id,
trx_b.trx_mysql_thread_id AS blocking_process_id,
CONCAT(pl.user, '@', pl.host) AS blocking_user,
pl.command,
pl.time,
IF(trx_b.trx_query IS NULL, 'Sleeping', trx_b.trx_query) AS blocking_query
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER
JOIN INFORMATION_SCHEMA.INNODB_TRX AS trx_b ON trx_b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS trx_w ON trx_w.trx_id = w.requesting_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl ON pl.id = trx_b.trx_mysql_thread_id
ORDER BY blocking_trx_id ASC LIMIT 5\G
@olivertappin
Copy link
Author

Please note, this will throw 2 warnings:

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                       |
+---------+------+-----------------------------------------------------------------------------------------------+
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release. |
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.      |
+---------+------+-----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Which is expected. I will update this gist when they do eventually become deprecated.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment