How to Find Out Who Is Locking a Table in MySQL

A MySQL lock refers to a lock flag connected to a table. The main use of MySQL locks is in user sessions to prevent other user sessions from accessing the table during an active session. A specific MySQL session can only access its locks and not locks associated with other clients.

Once a table is locked in a particular session, other sessions cannot read or write to the table unless the lock from the session is released. Therefore, all other sessions are in waiting mode until the lock is released.

This short guide will show you how to show available locks using the show process list command.

How to Lock a Table

To acquire a lock state on a specific table, you can use the LOCK TABLES statement. The general syntax for locking tables is:

mysql > LOCK TABLES tb_name [LOCK_TYPE]

In the lock type, you can specify READ or READ lock. The example below sets the WRITE lock on the actor table.

LOCK TABLES sakila.actor READ;

Once you have the lockset, all the other sessions will not update any data stored in the table.

For example, the following statement will fail unless the table is unlocked.

INSERT INTO sakila.actor(first_name, last_name, last_update) VALUES ('Hello', 'world', current_date());

MySQL will give you a read error as:

ERROR 1099 (HY000): Table 'actor' was locked with a READ lock and can't be updated

How to Unlock a Table

To unlock a table, use the UNLOCK TABLES query as:

mysql > UNLOCK TABLES;

Show Locked Tables

By default, there is no definitive way you can query to show for locked table like: (SHOW LOCKED TABLES;).

However, we can use a processlist command to show the locked tables and the users.

Use the command:

SHOW PROCESSLIST;

This will dump the information as well as the queries waiting to lock.

The above example shows the root user locking the table in the actor table.

Conclusion

This short tutorial showed you how to lock and unlock tables and view the users who have a table locked in a database.

from Linux Hint https://ift.tt/3jGDP2y