Detecting Locked Tables in MySQL

1. Overview

Table locking is useful for ensuring data consistency during various operations in MySQL. However, a lock can sometimes create issues, especially if they remain active longer than necessary, causing other queries to wait.

In this tutorial, we’ll cover effective techniques for detecting locked tables in MySQL. Additionally, we’ll use the Baeldung University database for illustration purposes. Further, we’ll intentionally lock tables to observe how to detect and manage locked tables.

2. Intentionally Locking a Table

By default, none of the tables in the Baeldung University database are locked. To observe locked tables, let’s first apply a lock to the Student table:

LOCK TABLES Student WRITE;
Query OK, 0 rows affected (0.001 sec)

Here, the query applies a write lock to the Student table, preventing other sessions from reading or writing to the table.

3. Detecting Locked Tables

MySQL provides several commands to identify locked tables. Let’s explore the most effective methods for detecting locked tables.

3.1. Using SHOW OPEN TABLES

The SHOW OPEN TABLES command lists all open tables, along with information on whether they’re currently in use (locked) by any thread.

For example, let’s check if the Student table is locked:

SHOW OPEN TABLES WHERE `Table` LIKE 'Student' AND `Database` LIKE 'University' AND In_use > 0;
+------------+---------+--------+-------------+
| Database   | Table   | In_use | Name_locked |
+------------+---------+--------+-------------+
| University | Student |      1 |           0 |
+------------+---------+--------+-------------+
1 row in set (0.001 sec)

The query identifies tables with a lock by checking if the In_use value is greater than zero. The results indicate that the Student table is currently locked.

Alternatively, we can explicitly check for all locked tables in the current database:

SHOW OPEN TABLES WHERE In_use > 0;
+------------+---------+--------+-------------+
| Database   | Table   | In_use | Name_locked |
+------------+---------+--------+-------------+
| University | Student |      1 |           0 |
+------------+---------+--------+-------------+
1 row in set (0.003 sec)

The result shows all currently locked tables in the database.

3.2. Checking Locks in performance_schema.metadata_locks

The performance_schema.metadata_locks table in MySQL provides comprehensive details about active locks. Furthermore, the table includes details such as the lock type, status, object schema, and the owner thread ID. In particular, it provides an efficient way to monitor locking activities across the database.

For example, we can view active locks in the database:

SELECT * FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'USER LEVEL LOCK';
+-----------------+---------------+-------------+-------------+-----------------------+-----------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE     | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-----------------+---------------+-------------+-------------+-----------------------+-----------+---------------+-------------+-------------------+-----------------+----------------+
| USER LEVEL LOCK | NULL          | student     | NULL        |       139967884905408 | EXCLUSIVE | EXPLICIT      | GRANTED     | item_func.cc:5508 |              45 |             22 |
+-----------------+---------------+-------------+-------------+-----------------------+-----------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.006 sec)

The output provides detailed insights into the lock status. Further, let’s explain some key columns in the result:

• OBJECT_NAME: the locked table in the database
• LOCK_STATUS: status of lock
• LOCK_DURATION: tells if it’s explicit or implicit

We use the performance_schema.metadata_locks table to check both user-defined locks and table locks across the database in a detailed manner.

4. Releasing Locks

We can release the lock table once we don’t need them again. This is important to avoid blocking other operations.

4.1. Releasing a Lock With Unlock Tables

For regular table locks, the UNLOCK TABLES command will release all locked tables in the current session:

UNLOCK TABLES;
Query OK, 0 rows affected (0.010 sec)

This query removes the lock from all tables, allowing other sessions to access the previously locked resources.

4.2. Releasing a Named Lock

For user-level locks created with GET_LOCK, the RELEASE_LOCK function can remove a specific lock by name:

SELECT RELEASE_LOCK('Student');
+-------------------------+
| RELEASE_LOCK('Student') |
+-------------------------+
|                       1 |
+-------------------------+
Query OK, 0 rows affected (0.010 sec)

The result 1 indicates the lock has been successfully released. Other threads can now acquire the lock on the Student table.

5. Conclusion

In this article, we explored several techniques for identifying locks, including SHOW OPEN TABLES, GET_LOCK, and performance_schema.metadata_locks. Detecting and managing locks in MySQL is crucial for maintaining database performance and consistency, particularly in high-traffic applications.

Furthermore, using these techniques, we can monitor and troubleshoot locked tables efficiently, ensuring optimal database performance and minimal contention among concurrent operations.