How to Release a MySQL Table Lock (Find the Blocker and KILL It)

This article is a localized edition of the Japanese original. Read the Japanese original

In MySQL, when one process holds a lock on a table and does not finish, other queries that use the same table have to wait. On a WordPress site this shows up as pages that stop loading, saves in the admin area that never finish, or waiting connections piling up until the connection limit is reached.

"Releasing" a lock really means finding the connection (session) that holds the lock and ending what it is doing. This article assumes MySQL 8.0 / 8.4 and explains how to confirm lock waits, identify the connection responsible, release it with KILL, and prevent it from happening again (as of October 2026). SHOW PROCESSLIST and KILL work the same way in MariaDB, but the defaults for the sys schema and Performance Schema differ.

Types of locks and the states you often see

You cannot tell an InnoDB row lock wait from the State alone. Check it with sys.innodb_lock_waits, described below.

Step 1: List running processes

Connect to MySQL and check what is running.

SHOW FULL PROCESSLIST;

Example output (some columns omitted):

Id  User  db  Command  Time  State                            Info
10  wp    wp  Query    7     User sleep                       select sleep(60)
11  wp    wp  Query    5     updating                         update t set v=11 where id=1
12  wp    wp  Query    4     Waiting for table metadata lock  alter table t add column c int

• Id is the connection number. You use it later with KILL.
• Time is the number of seconds in the current state. Long-running processes are your clues.
• Even a connection whose Command is Sleep may keep holding locks if it stopped with a transaction still open.

To see other users' connections, you need the PROCESS privilege. Without it, only your own user's connections are shown.

Step 2: Identify the connection holding the lock

InnoDB row lock waits

In MySQL 8.0 and later, a sys schema view shows "who is blocking whom" in one place.

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       sql_kill_blocking_connection
FROM sys.innodb_lock_waits\G

                 waiting_pid: 11
               waiting_query: update t set v=11 where id=1
                blocking_pid: 10
              blocking_query: select sleep(60)
sql_kill_blocking_connection: KILL 10

blocking_pid is the Id of the connection holding the lock. sql_kill_blocking_connection contains the ready-made KILL statement that ends that connection. blocking_query is the statement it is running now, which is not necessarily the statement that took the lock. (In the example above, the transaction updated the row and then ran a different statement.)

Metadata lock waits (ALTER TABLE or LOCK TABLES)

SELECT object_schema, object_name, waiting_pid, waiting_query,
       blocking_pid, blocking_lock_type, sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE waiting_pid <> blocking_pid\G

This view also returns rows in which the waiting connection appears to wait for itself, so waiting_pid <> blocking_pid filters them out. When an ALTER TABLE is waiting, the connection with the unfinished transaction appears in blocking_pid. The view relies on Performance Schema's metadata lock instrumentation, which is enabled by default in MySQL 8.0 and later.

Transactions left open for a long time

SELECT trx_mysql_thread_id, trx_started, trx_state, trx_rows_locked, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

Transactions with an old trx_started are candidates for holding locks. trx_mysql_thread_id corresponds to the Id in SHOW PROCESSLIST. Rows whose trx_state is LOCK WAIT are on the waiting side.

LOCK TABLES and MyISAM tables

SHOW OPEN TABLES WHERE In_use > 0;

In_use is the number of table locks and lock requests for that table. Tables with a value above 0 are currently locked or have lock requests waiting. It does not tell you which connection holds the lock, so read it together with sys.schema_table_lock_waits and SHOW PROCESSLIST above.

Step 3: Release the lock with KILL

Once you know which connection holds the lock, end that connection.

-- End the whole connection (its locks are released)
KILL 10;

-- Stop only the running statement and keep the connection
KILL QUERY 10;

• KILL (same as KILL CONNECTION) disconnects the connection. Uncommitted InnoDB changes are rolled back, and every lock the connection held is released.
• KILL QUERY only stops the running statement; the connection and its transaction remain. It does not release the locks of a connection that is sitting with a transaction open.
• KILL returns immediately, but rolling back a large transaction can take time. The locks stay until the rollback finishes.
• To stop other users' connections you need the CONNECTION_ADMIN privilege (or the deprecated SUPER). On a shared database at a hosting company, you can stop only your own user's connections.

UNLOCK TABLES does not release other connections’ locks

UNLOCK TABLES releases only the locks that the connection running it took with LOCK TABLES (or FLUSH TABLES WITH READ LOCK). Running it from another connection does not release anyone else's locks. To remove a lock another connection took with LOCK TABLES, KILL that connection. (When a connection ends, its table locks are released automatically.)

Check before you KILL

• What you are stopping: If you interrupt a backup, a plugin update process or a data migration, that process has to start over.
• Do not stop REPAIR TABLE or OPTIMIZE TABLE on MyISAM: The MySQL manual explains that killing these operations on a MyISAM table leaves the table corrupted and unusable.
• Non-transactional work is not rolled back: If you stop an UPDATE or DELETE on a MyISAM table partway through, the changes made so far remain.

Step 4: Confirm the lock is gone

SHOW FULL PROCESSLIST;
SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM sys.schema_table_lock_waits WHERE waiting_pid <> blocking_pid;

If the waiting processes are gone or are now running, the lock has been released. Also check that your WordPress pages and admin area work.

Preventing it from happening again

Use InnoDB for all tables

MyISAM locks whole tables, so every write makes the entire table wait. Older sites sometimes still have MyISAM tables. Check with the following query (replace wp with your database name).

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wp' AND ENGINE <> 'InnoDB';

Convert with ALTER TABLE table_name ENGINE=InnoDB;. Writes to the table wait during the conversion, so take a backup and do it at a low-traffic time.

Review how processes are written and configured

• Do not lock tables during backups: For InnoDB tables, mysqldump with --single-transaction takes a consistent backup without locking tables.
• Shorten the wait for ALTER TABLE: The default metadata lock wait (lock_wait_timeout) is one year. Lowering it for the session before a schema change keeps other work from being blocked for long.

SET SESSION lock_wait_timeout = 10;
ALTER TABLE wp_postmeta ADD INDEX idx_example (meta_key(50));

• Know the row lock wait limit: An InnoDB row lock wait fails with an error after innodb_lock_wait_timeout (default 50 seconds).
• Do not leave transactions open: After START TRANSACTION in your own scripts or admin tools, COMMIT or ROLLBACK promptly.

Summary

• Use SHOW FULL PROCESSLIST to find waiting processes (such as Waiting for table metadata lock) and long-running ones
• Use sys.innodb_lock_waits (row locks) and sys.schema_table_lock_waits (metadata locks) to find the Id of the connection holding the lock
• KILL that connection to roll back its uncommitted changes and release its locks. UNLOCK TABLES does not release other connections' locks
• Do not stop MyISAM repair or optimize operations. To prevent repeats, standardize on InnoDB, back up with --single-transaction, and adjust lock_wait_timeout

Related articles

• Causes of MySQL deadlocks and how to deal with them
• MySQL transaction basics
• How to use MySQL OPTIMIZE TABLE

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)