MySQL Lock and unlock tables Tutorial and Examples

Suppose there is such logic in a banking system:

When user A withdraws 500 yuan from his bank account, user A’s balance is the original balance minus 500.
When another user B transfers 500 yuan to the user A, the balance of user A is the original balance plus 500.

If these two operations occur at the same time, the balance of the User A may be wrong.

MySQL locks are designed to solve this concurrency problem. MySQL supports three types of locks: table-level locks, row-level locks, and page locks.

MySQL allows you to acquire table locks explicitly within a session to prevent other sessions from modifying the table during periods when you need exclusive access to the table.

Table locks is performed in the current session. A session can only acquire locks for itself, and can only release its own locks.

MySQL provides LOCK TABLES and UNLOCK TABLES statements for explicitly acquiring and releasing table locks.

MySQL table lock syntax

MySQL `LOCK TABLES` syntax

To acquire table locks explicitly for the current session, use the LOCK TABLES statement as following:

LOCK TABLES
    table_name [READ | WRITE]
    [, table_name [READ | WRITE], ...];

Here:

The table_name is the table name on which you want to acquire the lock.
READ and WRITE are lock types. READ Locks are used for shared read tables, and WRITE locks are used for exclusive read-write tables.
You can acquire locks on multiple tables in one statement. Use commas to separate multiple table locks.
The LOCK TABLES statement implicitly releases all table locks held by the current session before acquiring a new table lock.
You can use LOCK TABLE instead of LOCK TABLES.

MySQL `UNLOCK TABLES` syntax

To release all table locks acquired by the current session, use the following statement:

UNLOCK TABLES;

Lock Types

Table locks support two types of locks: READ and WRITE. READ Locks are used for shared read tables, and WRITE locks are used for exclusive read-write tables. Their characteristics are as follows。

`READ` Lock

A session holding a table lock can only read the table, but not write to it.
Multiple sessions can acquire READ locks.
Other sessions can read the table without explicitly acquiring the READ lock, but cannot write to the table. Writes from other sessions will wait until the read lock is released.

`WRITE` Lock

The session holding the lock can read and write the table.
Only the session holding the lock can access the table. Other sessions cannot access it until the lock is released.
Lock requests to the table by other sessions will be blocked while the WRITE lock is held.

If you do not explicitly release the table lock, when the session ends, both the READ lock and the WRITE lock will be released by MySQL.

MySQL Table Locks Examples

This example demonstrates how to acquire table locks (READ and WRITE locks) and release table locks in MySQL.

We use the following testdb statement to create a table named test_lock in the database to practice our example:

CREATE TABLE `test_lock` (
  `id` int NOT NULL AUTO_INCREMENT,
  `txt` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

If you do not have a testdb database, create a database and select the database using the following statement first:

CREATE DATABASE testdb;
use testdb;

`READ` Lock Examples

The READ lock is a shared read lock. Let us understand the characteristics of READ locks.

First, open a session and use the SHOW PROCESSLIST statement view the ID of the current session:

SHOW PROCESSLIST;

+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
| Id | User            | Host      | db     | Command | Time   | State                           | Info                                             |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  | 201156 | Waiting on empty queue          | NULL                                             |
|  8 | root            | localhost | testdb | Query   |      0 | init                            | show PROCESSLIST                                 |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+

Here, the ID of the current session is 8.

Insert a new row into the table without acquiring the READ lock,
```
INSERT INTO test_lock(txt)
VALUES('Hello');
```
It can be inserted normally here.
Use the following LOCK TABLES statement to acquire a READ lock on the test_lock table:
```
LOCK TABLES test_lock READ;
```
Let’s insert a new row into the table with the READ lock,
```
INSERT INTO test_lock(txt)
VALUES('Hello World');
```
At this point, MySQL gives an error: “ERROR 1099 (HY000): Table ’test_lock’ was locked with a READ lock and can’t be updated”.

You can reopen a session and test the READ lock in a new session:

SELECT * FROM test_lock;

+----+-------------+
| id | txt         |
+----+-------------+
|  1 | Hello       |
+----+-------------+

This means that the other sessions can also read the table.

Test WRITE operation in a new session. Please execute the above INSERT statement in the session. You will find that the operation will be suspended until the read lock on the table is released.

You can use the SHOW PROCESSLIST statement see if the session is pending:

SHOW PROCESSLIST;

+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
| Id | User            | Host      | db     | Command | Time   | State                           | Info                                             |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  | 201156 | Waiting on empty queue          | NULL                                             |
|  8 | root            | localhost | testdb | Query   |      0 | init                            | show PROCESSLIST                                 |
|  9 | root            | localhost | testdb | Query   |     16 | Waiting for table metadata lock | INSERT INTO test_lock(txt) VALUES('Hello World') |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+--------------------------------------------------+

You can see that the session with Id 9 is waiting for the lock to be released and its status is: Waiting for table metadata lock.

`WRITE` Lock Examples

The WRITE lock is an exclusive lock. Only the session holding the lock can access the table, and operations on the table by other sessions will wait for the lock to be released. Let us understand the characteristics of WRITE locks.

First, open a session and acquire the WRITE lock on the test_lock table.
```
LOCK TABLES test_lock WRITE;
```
Insert a new row into the test_lock table.
```
INSERT INTO test_lock(txt)
VALUES('Hi');
```
Insertion succeeded. This means that the session holding the write lock on the table can write to the table.

Query data from the test_lock table:

SELECT * FROM test_lock;

+----+-------------+
| id | txt         |
+----+-------------+
|  1 | Hello       |
|  2 | Hello World |
|  3 | Hi          |
+----+-------------+

Search successful. This means that the session holding the write lock on the table can read the table.

Start another session and try to read the data:
```
SELECT * FROM test_lock;
```
You will notice that the operation is in a state of waiting and has not returned.

You can use the SHOW PROCESSLIST statement view the status of the session

SHOW PROCESSLIST;

+----+-----------------+-----------+--------+---------+--------+---------------------------------+-------------------------+
| Id | User            | Host      | db     | Command | Time   | State                           | Info                    |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+-------------------------+
|  5 | event_scheduler | localhost | NULL   | Daemon  | 202266 | Waiting on empty queue          | NULL                    |
|  8 | root            | localhost | testdb | Query   |      0 | init                            | SHOW PROCESSLIST        |
|  9 | root            | localhost | testdb | Query   |     81 | Waiting for table metadata lock | SELECT * FROM test_lock |
+----+-----------------+-----------+--------+---------+--------+---------------------------------+-------------------------+

Release the held lock in the first session using the following statement:

UNLOCK TABLES;

You will see the result of the execution of the SELECT statement as follows:

+----+-------------+
| id | txt         |
+----+-------------+
|  1 | Hello       |
|  2 | Hello World |
|  3 | Hi          |
+----+-------------+

Conclusion

MySQL allows you to explicitly acquire table locks within a session using the LOCK TABLES statement to prevent other sessions from modifying the table during periods when you need exclusive access to the table.

MySQL allows you to explicitly release table locks within a session using the UNLOCK TABLES statement.

There are two types of MySQL table locks: shared read locks and exclusive read-write locks.

MySQL table lock syntax

MySQL LOCK TABLES syntax

MySQL UNLOCK TABLES syntax

Lock Types

READ Lock

WRITE Lock