Diagnostic information to collect for troubleshooting InnoDB Deadlock issues
8:43 PM
One common cause for deadlocks
when using InnoDB tables is from the existence of foreign key
constraints and the shared locks (S-lock) they acquire on referenced
rows.
if one is having any locking issues (deadlocks, lock wait timeouts, hangs due to semaphore waits etc.,),
try to capture all of the following outputs at the time,it happens to give you the best likelihood in tracking down the issue:
1) SHOW ENGINE INNODB STATUS
section: Latest Deadlock Information:
This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
2) SHOW FULL PROCESSLIST
This will show all connected threads. Specifically, when it comes to 'hidden' locks
it would show a user that has been connected, but idle (but who could have issued a LOCK TABLE command).
3) Error log
Always check out the error log for messages and/or anything out of the ordinary.
(Extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor is run.
So please be carefull running on production, run during non peak and make sure you stop after collecting data)
4) SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables
This is only available as of MySQL 5.1 and newer (and you must be using the InnoDB Plugin), but since most will be running this, they contain useful information.
5) SHOW CREATE TABLE outputs for each table involved:
Caution: The following two steps should be run carefully on production as it can affect performance due to collecting monitor information.
Stop running and disabling logging monitor information as soon as information is gathered.
But on test and dev server you can schedule it to run if difficult to wait for the time when the problem occurs.
6) Enable InnoDB Lock Monitor
Enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor.
This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output.
7) Run "mysqladmin debug"
Logs all lock info to the error log. Great because it logs all locks and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table because LOCK TABLE is external to the InnoDB storage engine. It's often most helpful in conjunction with other details.
if one is having any locking issues (deadlocks, lock wait timeouts, hangs due to semaphore waits etc.,),
try to capture all of the following outputs at the time,it happens to give you the best likelihood in tracking down the issue:
1) SHOW ENGINE INNODB STATUS
section: Latest Deadlock Information:
This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
2) SHOW FULL PROCESSLIST
This will show all connected threads. Specifically, when it comes to 'hidden' locks
it would show a user that has been connected, but idle (but who could have issued a LOCK TABLE command).
3) Error log
Always check out the error log for messages and/or anything out of the ordinary.
(Extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor is run.
So please be carefull running on production, run during non peak and make sure you stop after collecting data)
4) SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables
This is only available as of MySQL 5.1 and newer (and you must be using the InnoDB Plugin), but since most will be running this, they contain useful information.
5) SHOW CREATE TABLE outputs for each table involved:
Caution: The following two steps should be run carefully on production as it can affect performance due to collecting monitor information.
Stop running and disabling logging monitor information as soon as information is gathered.
But on test and dev server you can schedule it to run if difficult to wait for the time when the problem occurs.
6) Enable InnoDB Lock Monitor
Enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor.
This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output.
7) Run "mysqladmin debug"
Logs all lock info to the error log. Great because it logs all locks and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table because LOCK TABLE is external to the InnoDB storage engine. It's often most helpful in conjunction with other details.
0 comments