http://www.mysqltutorial.org/mysql-table-locking/
Summary: in this tutorial, you will learn how to use MySQL locking for cooperating table accesses between sessions.
A lock is a flag associated with a table. MySQL allows a client session to explicitly acquire a table lock for preventing other sessions from accessing the same table during a specific period. A client session can acquire or release table locks only for itself. It cannot acquire or release table locks for other sessions.
Before going into detail, we create a table named tbl
for practicing the table locking statements.
1 2 3 4 5 | CREATE TABLE tbl ( id INT NOT NULL AUTO_INCREMENT, col INT NOT NULL, PRIMARY KEY (id) ) Engine = InnoDB; |
LOCK and UNLOCK TABLES syntax
The following statement explicitly acquires a table lock:
1 | LOCK TABLES table_name [READ | WRITE] |
To lock a table, you specify its name after the LOCK TABLES
keywords. In addition, you specify the type of lock, either READ
or WRITE
.
To release a lock for a table, you use the following statement:
1 | UNLOCK TABLES; |
Read Locks
A READ
lock has the following features:
- A
READ
lock for a table can be acquired by multiple sessions at the same time. In addition, other sessions can read data from the table without acquiring the lock. - The session that holds the
READ
lock can only read data from the table, but cannot write. In addition, other sessions cannot write data to the table until theREAD
lock is released. The write operations from another session will be put into the waiting states until theREAD
lock is released. - If the session is terminated, either normally or abnormally, MySQL will release all the locks implicitly. This feature is also relevant for the
WRITE
lock.
Let’s take a look at how the READ
lock works in the following scenario.
In the first session, first, connect to the sampledb
database and use the CONNECTION_ID()
function to get the current connection id as follows:
1 | SELECT CONNECTION_ID(); |
Then, insert a new row into the tbl
table.
1 | INSERT INTO tbl(col) VALUES(10); |
Next, query the data the tbl
table.
1 | SELECT * FROM tbl; |
After that, to acquire a lock, you use the LOCK TABLE
statement.
1 | LOCK TABLE tbl READ; |
Finally, in the same session, if you try to insert a new row into the tbl
table, you will get an error message.
1 | INSERT INTO tbl(col) VALUES(11); |
1 | Error Code: 1099. Table 'tbl' was locked with a READ lock and can't be updated. |
So the once READ
lock is acquired, you cannot write data into the table within the same session.
Let’s check the READ
lock from a different session.
First, connect to the sampledb
and check the connection id:
1 | SELECT CONNECTION_ID(); |
Then, retrieve data from the tbl
table:
1 | SELECT * FROM tbl; |
Next, insert a new row into the tbl
table:
1 | INSERT INTO tbl(col) VALUES(20); |
The insert operation from the second session is in the waiting state because a READ
lock is already acquired on the tbl
table by the first session and it has not released yet.
You can see the detailed information from the SHOW PROCESSLIST
statement.
1 | SHOW PROCESSLIST; |
After that, go back to the first session and release the lock by using the UNLOCK TABLES
statement. After you release the READ
lock from the first session, the INSERT
operation in the second session executed.
Finally, check it the data of the tbl
table to see if the INSERT
operation from the second session really executed.
1 | SELECT * FROM tbl; |
Write Locks
A WRITE
lock has the following features:
- The only session that holds the lock of a table can read and write data from the table.
- Other sessions cannot read data from and write data to the table until the
WRITE
lock is released.
Let’s go into detail to see how the WRITE
lock works.
First, acquire a WRITE
lock from the first session.
1 | LOCK TABLE tbl WRITE; |
Then, insert a new row into the tbl
table.
1 | INSERT INTO tbl(col) VALUES(11); |
It works.
Next, read data from the tbl
table.
1 | SELECT * FROM tbl; |
It also works.
After that, from the second session, try to write and read data:
1 2 3 | INSERT INTO tbl(col) VALUES(21);
SELECT * FROM tbl; |
MySQL puts these operations into a waiting state. You can check it using the SHOW PROCESSLIST
statement.
1 | SHOW PROCESSLIST |
Finally, release the lock from the first session.
1 | UNLOCK TABLES; |
You will see all pending operations from the second session executed and the following picture illustrates the result:
Read vs. Write locks
- Read locks are “shared” locks which prevent a write lock is being acquired but not other read locks.
- Write locks are “exclusive ” locks that prevent any other lock of any kind.
In this tutorial, you have learned how to lock and unlock tables for reading and writing to cooperate the table accesses between sessions.