MySQL Locking tables details
MySQL allows a client session to acquire a table lock explicitly for
preventing other sessions from accessing the table during a specific
period. A client session can acquire or release table locks only for
itself. It cannot acquire or release table locks for other sessions.
Below are the method to check lock details:
SHOW ENGINE INNODB STATUS
This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
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, but it can get truncated too.
Run "mysqladmin debug"
Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) 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. Not so great because a bit cryptic to read, and I wouldn't solely reply on it, as it's often most helpful in conjuntion with other details).
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).
Error log
Of course, always check out the error log for messages and/or anything out of the ordinary. (Not to mention extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor.)
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 of you will be running this, they contain great information. In fact, I've written a past post on Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables which contains even more details on using these 3 tables for troubleshooting.
SHOW CREATE TABLE outputs for each table involved
Useful Queries
We can use INFORMATION_SCHEMA Tables.
To check about all the locks transactions are waiting for -
SELECT * FROM INNODB_LOCK_WAITS
A list of blocking transactions
SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS)
SELECT INNODB_LOCKS.* FROM INNODB_LOCKS JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID)
A List of locks on perticular table:
SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name
A list of transactions waiting for locks:
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE = 'LOCK WAIT'
Other Queries:
-> show open tables WHERE In_use > 0;
-> SHOW OPEN TABLES WHERE `Table` LIKE '%Table Name%' AND `Database` LIKE 'Data BaseName' AND In_use > 0;
Below are the method to check lock details:
SHOW ENGINE INNODB STATUS
This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
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, but it can get truncated too.
Run "mysqladmin debug"
Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) 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. Not so great because a bit cryptic to read, and I wouldn't solely reply on it, as it's often most helpful in conjuntion with other details).
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).
Error log
Of course, always check out the error log for messages and/or anything out of the ordinary. (Not to mention extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor.)
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 of you will be running this, they contain great information. In fact, I've written a past post on Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables which contains even more details on using these 3 tables for troubleshooting.
SHOW CREATE TABLE outputs for each table involved
Useful Queries
We can use INFORMATION_SCHEMA Tables.
To check about all the locks transactions are waiting for -
SELECT * FROM INNODB_LOCK_WAITS
A list of blocking transactions
SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS)
SELECT INNODB_LOCKS.* FROM INNODB_LOCKS JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID)
A List of locks on perticular table:
SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name
A list of transactions waiting for locks:
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE = 'LOCK WAIT'
Other Queries:
-> show open tables WHERE In_use > 0;
-> SHOW OPEN TABLES WHERE `Table` LIKE '%Table Name%' AND `Database` LIKE 'Data BaseName' AND In_use > 0;
Comments