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 to prevent other sessions from accessing the same table during a specific period.
A client session can acquire or release table locks only for itself. A client session cannot acquire or release table locks for other client sessions.
Before we move on, let’s create a table named messages
for practicing with the table locking statements.
CREATE TABLE messages (
id INT AUTO_INCREMENT PRIMARY KEY,
message VARCHAR(100) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
MySQL LOCK TABLES statement
The following LOCK TABLES
statement explicitly acquires a table lock:
LOCK TABLES table_name [READ | WRITE]
Code language: SQL (Structured Query Language) (sql)
In this syntax, you specify the name of the table that you want to lock after the LOCK TABLES
keywords. In addition, you specify the type of lock, either READ
or WRITE
.
MySQL allows you to lock multiple tables by specifying a list of comma-separated table names with lock types that you want to lock after the LOCK TABLES
keywords:
LOCK TABLES table_name1 [READ | WRITE],
table_name2 [READ | WRITE],
... ;
Code language: SQL (Structured Query Language) (sql)
MySQL UNLOCK TABLES statement
The UNLOCK TABLES statement releases any table locks held by the current session:
UNLOCK TABLES;
Code language: SQL (Structured Query Language) (sql)
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. And 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 to the
WRITE
lock.
Let’s take a look at how the READ
lock works in the following scenario.
First, connect to the database in the first session and use the CONNECTION_ID()
function to get the current connection id as follows:
SELECT CONNECTION_ID();
Code language: SQL (Structured Query Language) (sql)
Then, insert a new row into the messages
table.
INSERT INTO messages(message)
VALUES('Hello');
Code language: SQL (Structured Query Language) (sql)
Next, query the data from the messages
table.
SELECT * FROM messages;
Code language: SQL (Structured Query Language) (sql)
After that, acquire a lock using the LOCK TABLE
statement.
LOCK TABLE messages READ;
Code language: SQL (Structured Query Language) (sql)
Finally, try to insert a new row into the messages
table:
INSERT INTO messages(message)
VALUES('Hi');
Code language: SQL (Structured Query Language) (sql)
MySQL issued the following error:
Error Code: 1099. Table 'messages' was locked with a READ lock and can't be updated.
Code language: SQL (Structured Query Language) (sql)
So once the READ
lock is acquired, you cannot write data to the table within the same session.
Let’s check the READ
lock from a different session.
First, connect to the database and check the connection id:
SELECT CONNECTION_ID();
Code language: SQL (Structured Query Language) (sql)
Next, query data from the messages
table:
SELECT * FROM messages;
Code language: SQL (Structured Query Language) (sql)
Then, insert a new row into the messages
table:
INSERT INTO messages(message)
VALUES('Bye');
Code language: SQL (Structured Query Language) (sql)
Here is the output:
The insert operation from the second session is in the waiting state because a READ lock is already acquired on the messages table by the first session and it has not been released yet.
From the first session, use the SHOW PROCESSLIST
statement to show detailed information:
SHOW PROCESSLIST;
Code language: SQL (Structured Query Language) (sql)
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 is executed.
Finally, check the data of the messages
table to see if the INSERT
operation from the second session was executed.
SELECT * FROM messages;
Code language: SQL (Structured Query Language) (sql)
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.
LOCK TABLE messages WRITE;
Code language: SQL (Structured Query Language) (sql)
Then, insert a new row into the messages
table.
INSERT INTO messages(message)
VALUES('Good Morning');
Code language: SQL (Structured Query Language) (sql)
It worked.
Next, query data from the messages
table.
SELECT * FROM messages;
Code language: SQL (Structured Query Language) (sql)
It also works.
After that, from the second session, attempt to write and read data:
INSERT INTO messages(message)
VALUES('Bye Bye');
SELECT * FROM messages;
Code language: SQL (Structured Query Language) (sql)
MySQL puts these operations into a waiting state. You can check it using the SHOW PROCESSLIST
statement:
SHOW PROCESSLIST;
Code language: SQL (Structured Query Language) (sql)
Finally, release the lock from the first session.
UNLOCK TABLES;
Code language: SQL (Structured Query Language) (sql)
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 that 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 to cooperate with the table accesses between sessions.