Locking is an important part of an ACID-compliant database, but excessive locks can lead to performance degradation. Here are some strategies for investigating locks in MySQL.
A major cause of long-running queries that might lead to timeouts or other issues are various types of locks. After finding that you have a large number of threads, or threads that are lasting longer than expected, the next thing to investigate is locks.
Dec 22, 2021 · 4 min read
Poorly optimized queries and excessive connections can cause problems in MySQL, here's a quick way to identify and kill them.
A crash course on locks
MySQL (and most relational databases) have a few different types of locks to limit concurrent access from different sessions, protecting schema and data integrity. In MySQL, specifically:
Table locks on either base tables or views limit what sessions can read from or write to the table.
Row locks on individual rows limit what sessions can read or update those rows. If a row has a read or a "shared" lock then no session can modify the row until the lock is released, but any session can read the row. If a row has a write or an "exclusive" lock then only the session holding the lock can modify the row.
SELECT ... FROMstatements do not need to obtain row read locks unless the transaction isolation level is set to
SERIALIZABLE. That means a row write lock won't prevent row reads from other sessions.
If a statement that modifies rows (e.g.
DELETE) has no suitable index, then InnoDB will obtain a write lock on every row in the table.
Metadata locks on objects (schemas, tables, triggers, etc.) limit what sessions can alter the metadata of the database object.
This is not an exhaustive list, but it gives us enough information for the sections below.
InnoDB table and row locks
It's highly likely that you're using InnoDB as the engine for your tables as it has been the default since MySQL v5.5.5 (2010) .
You can see what engine each of your tables is using with this query:
SELECT table_schema , table_name , engine FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('information_schema', 'performance_schema', 'mysql') ORDER BY table_schema, table_name;
Here is a query to see both requested locks and locks held by InnoDB transactions:
-- MySQL <8.0.1 (2017) SELECT l.lock_type , l.lock_table , l.lock_index , CASE WHEN l.lock_mode = 'S' THEN 'SHARED' WHEN l.lock_mode = 'X' THEN 'EXCLUSIVE' WHEN l.lock_mode = 'IS' THEN 'INTENTION_SHARED' WHEN l.lock_mode = 'IX' THEN 'INTENTION_EXCLUSIVE' ELSE l.lock_mode END AS lock_mode , time_to_sec(timediff(now(), trx.trx_started)) AS trx_length_sec , trx.* , concat('KILL ', t.processlist_id, ';') AS kill_command , concat('CALL mysql.rds_kill(', t.processlist_id, ');') AS rds_kill_command FROM information_schema.innodb_locks l INNER JOIN information_schema.innodb_trx trx ON trx.trx_id = l.lock_trx_id INNER JOIN performance_schema.threads t ON t.thread_id = trx.trx_mysql_thread_id ORDER BY trx.trx_wait_started IS NOT NULL , trx.trx_wait_started , trx_length_sec DESC;
Note: your user will need the
PROCESS privilege to access
information_schema.innodb_* tables, and to see threads for other users.
Some important columns to pay attention to:
lock_type: the type of the lock:
lock_table: the table that is locked, or the table that contains the locked rows
lock_index: the name of the index if
lock_type = 'RECORD'
lock_mode: the mode(s) of the lock:
trx_length_sec: how long since the transaction started
trx_state: the state of the transaction:
ROLLING BACK, and
trx_wait_started: when the transaction started waiting on a lock
trx_query: the statement the transaction is executing
Some key things to look for:
Long-running statements likely indicate resource contention. If a lock is held too long, any other transaction that needs to lock the same items will risk timing out. The default
innodb_lock_wait_timeoutvariable value is 50 seconds, but you can check it with this query:
SHOW VARIABLES WHERE variable_name = 'innodb_lock_wait_timeout';
A buildup of many concurrent writes to the same tables might indicate a buildup of write locks needed by your transaction isolation level. For example, gap and next-key locks in
REPEATABLE READand above.
trx_wait_started IS NOT NULL then that transaction is being blocked by locks held by other transactions. Here is a query to see what queries are blocking others:
-- MySQL <8.0.1 (2017) SELECT block.trx_id AS blocking_trx_id , block.trx_query AS blocking_trx_query , time_to_sec(timediff(now(), req.trx_wait_started)) AS requesting_trx_wait_sec , req.trx_id AS requesting_trx_id , req.trx_query AS requesting_trx_query , concat('KILL ', bt.processlist_id, ';') AS kill_command , concat('CALL mysql.rds_kill(', bt.processlist_id, ');') AS rds_kill_command FROM information_schema.innodb_lock_waits lw INNER JOIN information_schema.innodb_trx block ON block.trx_id = lw.blocking_trx_id INNER JOIN performance_schema.threads bt ON bt.thread_id = block.trx_mysql_thread_id INNER JOIN information_schema.innodb_trx req ON req.trx_id = lw.requesting_trx_id ORDER BY requesting_trx_wait_sec DESC , block.trx_id , req.trx_id;
From the MySQL documentation :
A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.
By default, InnoDB can detect deadlocks and will automatically roll back transactions to break the deadlock. InnoDB prefers rolling back small transactions, determined by number of rows affected.
This command will dump a very long status of the entire InnoDB engine which includes information about the last two deadlocks detected, but it only shows the last statement in the two transactions:
SHOW ENGINE INNODB STATUS;
Note: your user will need the
PROCESS privilege to execute this statement.
To see the other statements in the transaction, find the thread ID and query the
performance_schema.events_statements_history table. The table is typically auto-sized, so it has a limit to how many statements are kept.
SELECT * FROM performance_schema.events_statements_history WHERE thread_id = :threadId ORDER BY thread_id, event_id;
A small amount of deadlocks usually isn't dangerous and is somewhat expected in high-throughput databases. Applications should be written to retry transactions that were rolled back due to a deadlock.
Deadlocks become dangerous when they happen so frequently that certain transactions can't execute at all.
See this MySQL documentation page on minimizing and handling deadlocks .
Metadata locks are primarily used to enqueue metadata changes (schema changes, object renames, etc.) after active transactions complete.
From the MySQL documentation on metadata locking :
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 explicitly or implicitly started 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.
Metadata locking applies not just to tables, but also to schemas, stored programs (procedures, functions, triggers, scheduled events), tablespaces, user locks, and locks acquired with the locking service.
Prior to MySQL 5.5.3 a statement that opened a table only held meta data locks till the end of the statement and not the end of the transaction. This meant that transaction was not really isolated, because the same query could return different results if executed twice and if a DDL was executed between the query invocations.
And starting with MySQL v8.0.3 (2017) metadata locks are obtained on sibling tables related by foreign keys:
MySQL now extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables.
Here is a query to see metadata locks and the queries that cause them:
SELECT ml.object_type , ml.object_schema , ml.object_name , ml.lock_type , ml.lock_duration , ml.lock_status , t.* , concat('KILL ', t.processlist_id, ';') AS kill_command , concat('CALL mysql.rds_kill(', t.processlist_id, ');') AS rds_kill_command FROM performance_schema.metadata_locks ml INNER JOIN performance_schema.threads t ON t.thread_id = ml.owner_thread_id WHERE t.type = 'FOREGROUND' AND t.processlist_command != 'Sleep' AND t.processlist_command != 'Daemon' AND t.processlist_id != connection_id() ORDER BY t.processlist_time DESC;
Note: your user will need the
PROCESS privilege to see threads for other users.
It's normal to see a lot of metadata locks, that's not necessarily indicative of any issue. It's also normal to see multiple metadata locks per thread ID where the lock type or locked objects are different.
Metadata locks can become a problem when write locks (which are higher priority than read locks ) cause normally parallelized reads to wait.
Consider this priority order:
- An excessively long
SELECT ... FROMhas a metadata read lock (e.g.
SHARED_READ) on a table
- A schema change is waiting to acquire a write lock (e.g.
INTENTION_EXCLUSIVE) on the table
- Some number of other
SELECT ... FROMqueries on the table, possibly short ones, are all waiting for the schema change to complete first