MySQL Locks

statements.rst

Metadata Locks

If an InnoDB table is being accessed at all via SELECT or DML (INSERT, UPDATE, DELETE), you should rightly expect a metadata lock.

According to the MySQL Documentation on MetaData Locking (http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html):

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

SELECTs will obtain a shared metadata lock, while DML and DDL will obtain an exclusive metadata lock. So an uncommitted transaction (even if only SELECTing) will cause another connection seeking to do DML or DDL to wait for the metadata lock.

The metadata lock timeout is governed by lock_wait_timeout, which is set to 3600 seconds on some production boxes here at Jump. The timeout to get a row lock is governed by innodb_lock_wait_timeout, which is set to 600 seconds on some production boxes here at Jump. Both timeouts cause an ER_LOCK_WAIT_TIMEOUT (1205) error.

SHOW VARIABLES LIKE '%timetout%';
SELECT @@lock_wait_timeout;

To monitor metadata locks, do some session setup:

INSTALL SONAME 'metadata_lock_info';

Metadata locks:

SELECT * FROM information_schema.metadata_lock_info;

Processes holding locks:

SELECT * FROM information_schema.PROCESSLIST WHERE ID IN (SELECT DISTINCT `THREAD_ID` FROM information_schema.metadata_lock_info);

Non-metadata locks

To check about all the locks transactions are waiting for:

USE INFORMATION_SCHEMA;
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);

Or:

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 particular 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';

References

• https://www.xaprb.com/blog/2006/07/31/how-to-analyze-innodb-mysql-locks/
• https://github.com/innotop/innotop
• https://stackoverflow.com/a/13155778/1843746
• MySQL Troubleshooting: What To Do When Queries Don't Work, Chapter 6 - Page 96. (https://www.amazon.com/dp/1449312004)
• https://www.percona.com/blog/2016/12/28/quickly-troubleshooting-metadata-locks-mysql-5-7/
• https://dba.stackexchange.com/questions/41141/unable-to-drop-the-table-since-its-showing-waiting-for-meta-data-lock
• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html