How to Show Locks in MySQL

In today’s post, we’ll learn how to show locks in MySQL.

Show Locks in MySQL

A table’s attributes can be changed by assigning a MySQL Lock flag to the table. To restrict other sessions from accessing the same table over a certain period, MySQL enables a table lock that a client-server may assign.

A client can acquire or release a MySQL lock only for their session. This means the client cannot access locks for a different session or release locks held by a different session.

MySQL provides two kinds of lock: Read lock and Write lock.

LOCK TABLES table_name READ as alias_table_name

Here, the table_name denotes the table on which the lock needs to be applied. When locking a table with an alias, you must use that alias in your statements to refer to the locked table.

They cannot perform a write action on a table if the session has the READ lock. This is due to the READ lock’s ability to only read data from the table.

No other sessions can write data into the table without releasing the READ lock; hence they are all prevented from doing so. Until we release the READ lock, the write operations enter a waiting state.

Uniquely named locks obtained with GET_LOCK() show up in the Performance Schema metadata locks table as a result of the MDL reimplementation. The lock name is shown in the OBJECT_NAME column, while the OBJECT_TYPE column states USER LEVEL LOCK.

You can understand metadata lock dependencies between sessions with the help of this knowledge. Not only can you see which lock a session is awaiting, but also which is in control of that lock right now.

It is not possible to alter the metadata locks table; it is read-only.

Consider the following example to help you better understand the prior idea.

SELECT GET_LOCK('alias_table_name', 10);
SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_TYPE='USER LEVEL LOCK'

The first statement in the previous example obtains data for the supplied lock name. The alias_table_name, in this case, specifies the name of the lock, and 10 stands for the timeout.

The information is retrieved from the metadata locks table, where the object type is a USER LEVEL LOCK in the second statement.

Run the above code line in any browser compatible with MySQL. It will display the following outcome:

+----------------------------------+
| GET_LOCK('alias_table_name', 10) |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

*************************** 1. row ***************************
          OBJECT_TYPE: USER LEVEL LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: alias_table_name
OBJECT_INSTANCE_BEGIN: 139872019610944
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: GRANTED
               SOURCE: item_func.cc:5481
      OWNER_THREAD_ID: 35
       OWNER_EVENT_ID: 3
1 row in set (0.00 sec)