MySQL Table Locking

In this tutorial, you will learn how to use MySQL locking for cooperating table access between sessions.

MySQL allows a client session to acquire a table lock explicitly for preventing other sessions from accessing the 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 will create a sample database named sampledb that includes a simple table named tbl to practice the table locking statements.

CREATEDATABASE sampledb;

CREATETABLE tbl (

Â Â id int(11) NOT NULLAUTO_INCREMENT,

Â Â col int(11) NOT NULL,

Â Â PRIMARY KEY (id)

);

LOCK and UNLOCK TABLES syntax

The simple form of acquiring a lock for a table is as follows:

LOCK TABLES table_name [READ | WRITE]

You put the name of the table after the LOCK TABLES keywords and followed by a lock type. MySQL provides two lock types: READ and WRITE . We will go into detail of each lock type in the next section.

To release a lock for a table, you use the following statement:

UNLOCK TABLES;

Table locking for READ

A READ lock for a table 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 not write. In addition, other sessions cannot write data into the table until the READ lock is released. The write operations from another session will be put into theÂ waiting states until the READ lock is released.

If the session is terminated normally or abnormally, MySQL will release all the locks implicitly. This is also relevant for the WRITE lock.

Letâ€™s take a look at how the READ lock works in the following scenario.

First, connect to the sampledb database. To find out the current connection id, you use theCONNECTION_ID() function as follows:

Finally, in the same session, if you try to insert a new row into the tbl table, you will get an error message.

INSERTINTO tbl(col) VALUES(11);

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:

SELECTCONNECTION_ID();

Then, retrieve data from the tbl .

SELECT * FROM tbl;

Next, insert a new row into the tbl table from the second session.

INSERTINTO tbl(col) VALUES(20);

The insert operation from the second session is in the waiting state because a READ lock 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.

SHOWPROCESSLIST;

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.

SELECT * FROM tbl;

MySQL table locking for WRITE

The table lock for WRITE has the following features:

Only session that holds the lock of a table can read and write data from the table.

Other sessions cannot read and write from 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.

LOCKTABLE tbl WRITE;

Then, insert a new row into the tbl table.

INSERTINTO tbl(col) VALUES(11);

It works.

Next, read data from the tbl table.

SELECT * FROM tbl;

It is fine.

After that, from the second session, try to write and read data:

INSERTINTO tbl(col) VALUES(21);

SELECT * FROM tbl;

MySQL puts those operations into a waiting state. You can check it using the SHOW PROCESSLISTstatement.

SHOWPROCESSLIST

Finally, release the lock from the first session.

UNLOCK TABLES;

You will see all pending operations from the second session executed.

In this tutorial, we have shown you how to lock and unlock tables for READ and WRITE to cooperate table access between sessions.