MySQL Table Locking

By admin | June 14, 2016 | No Comments

mysql table lock

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.

LOCK and UNLOCK TABLES syntax

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

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:

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:

first session id

Then, insert a new row into the tbl table.

Next, retrieve all rows from the same table.

tbl data first session

After that, to acquire a lock, you use the LOCK TABLE statement.

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

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:

table lock second session

Then, retrieve data from the tbl .

tbl data second session

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

table lock waiting status

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.

show processlist table lock

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.

tbl data after releasing lock

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.

Then, insert a new row into the tbl table.

It works.

Next, read data from the tbl table.

table lock write

It is fine.

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

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

show processlist table lock

Finally, release the lock from the first session.

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

table tbl data after unlock

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

 

Source : http://www.mysqltutorial.org/mysql-table-locking/

 

Follow us on twitter : Umesh Ghimire

Show Buttons
Hide Buttons