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:
In the lock type, you can specify READ or READ lock. The example below sets the WRITE lock on the actor table.
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.
MySQL will give you a read error as:
How to Unlock a Table
To unlock a table, use the UNLOCK TABLES query as:
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:
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.
