JIYIK CN >

In this article, we will learn how to display locks in MySQL.

Displaying Locks in MySQL

You can change the properties of a table by assigning a MySQL lock flag to the table. To restrict other sessions from accessing the same table for a certain period of time, MySQL enables table locks that the client server may assign.

A client can acquire or release MySQL locks only for its session. This means that a client cannot access locks of a different session or release locks held by a different session.

MySQL provides two types of locks: read locks and write locks.

LOCK TABLES table_name READ as alias_table_name

Here, table_name indicates the table to be locked. When you use an alias to lock a table, you must use the alias in the statement to reference the locked table.

If a session has a READ lock, they cannot perform write operations on the table. This is because a READ lock can only read data from a table.

Without releasing the READ lock, other sessions cannot write to the table; therefore, they are blocked from doing so. Write operations enter a wait state until we release the READ lock.

As a result of the MDL reimplementation, GET_LOCK()uniquely named locks acquired using appear in the Performance Schema metadata lock tables. The lock name appears in the OBJECT_NAME column, and the OBJECT_TYPE column shows USER LEVEL LOCK.

You can use this knowledge to understand metadata lock dependencies between sessions. Not only can you see which lock a session is waiting for, but you can also see which lock currently controls it.

The metadata lock table cannot be altered; it is read-only.

Consider the following example to help you better understand the previous 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 preceding example gets data for the supplied lock name. In this case, alias_table_name specifies the name of the lock and 10 represents the timeout.

Retrieve information from the metadata lock table, where the object type is the one in the second statement USER LEVEL LOCK.

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

+----------------------------------+
| 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)