MySQL Table Locking And Unlocking

Mysql Table Lock

In this tutorial, we will learn what is table locking and how it works. We will see the types of table locking and syntax for locking and unlocking the table. Also, we will understand the table locking mechanism through real-time examples. So, let’s get started!

Introduction to MySQL Table Locking

In order to collaborate with other sessions and access the table’s contents, MySQL enables a client session to explicitly acquire a table lock.

Only for itself, a client session has the ability to acquire or release table locks. Additionally, no client session may acquire or release a lock on a table on behalf of another client session.

There are two types of table locks in MySQL- READ locks and WRITE locks. Let’s see them one by one.

Types of Table Locking

As stated earlier, mysql provides us with the two types of locking mechanisms – READ and WRITE.

READ Lock

The features of the READ lock are as follows-

  • It is possible for many sessions to simultaneously acquire a READ lock on a table. In addition, the table may be read by other sessions without locking it.
  • The session with the READ lock is only permitted to read data from the table; writing is not permitted. Additionally, until the READ lock is freed, no other sessions can write data to the table. Until the READ lock is freed, write operations from other sessions will be placed in waiting states.
  • Whether the session ends regularly or unexpectedly, MySQL will implicitly release all the locks. The WRITE lock can benefit from this functionality as well.

WRITE Lock

The features of the WRITE lock are as follows-

  • Data from a table can only be read and written to by the session that currently has the lock on the table.
  • Until the WRITE lock is freed, other sessions are unable to read from or write to the table.
  • In short, the WRITE lock prevents other sessions from reading as well as writing the data.

Syntax of MySQL Table Locking

The syntax of table locking is very simple as shown below-

LOCK TABLES table_name [READ | WRITE]
Code language: SQL (Structured Query Language) (sql)

Here, you write the name of the table along with the type of lock.

Also, you can specify multiple table names in a single statement as shown below-

LOCK TABLES table_name1 [READ | WRITE], table_name2 [READ | WRITE], ... ;
Code language: SQL (Structured Query Language) (sql)

To unlock the table, you don’t need to specify the table name. The following syntax is used to unlock all tables.

UNLOCK TABLES;
Code language: SQL (Structured Query Language) (sql)

The above statement will release locks from all tables.

MySQL Table Locking Examples

For the demonstration of the table locking mechanism in the mysql, you will need two sessions to be opened simultaneously.

If you are using a mysql CLI, then you may open two windows of it and check the connection id of both sessions. If the session ids of both windows are different, then you can proceed to the examples given below.

To check the session id-

SELECT CONNECTION_ID();
Code language: SQL (Structured Query Language) (sql)
Find Session Ids
Find Session Ids

Here you can see, that we have merged two images to show the two different session ids of the two different CLI windows.

Read Lock Example

Here, we have a table called ’emps’. We will lock that table using a READ lock in the first session. But first, let’s display the data from the table using the SELECT statement.

SELECT * FROM emps;
Code language: SQL (Structured Query Language) (sql)
Emps Table Data 2
Emps Table Data

Now we will acquire a lock on the table.

LOCK TABLE emps READ;
Code language: SQL (Structured Query Language) (sql)

The table has acquired the lock successfully. Now, let’s try to insert some values into the table in the same session. As per the READ lock documentation, we must get an error if we try to insert values in the LOCK acquired table.

INSERT INTO emps(name,city)VALUES("May","SA");
Code language: SQL (Structured Query Language) (sql)
Read Lock On The Table
Read Lock On The Table

As you can see, we got an error because the table is locked with the READ lock.

If you try to insert some values from other sessions, you will not get any error, but, the query execution will go into the waiting state. Let’s see.

First, let’s check the session id and then run the INSERT command.

SELECT CONNECTION_ID(); INSERT INTO emps(name,city)VALUES("May","SA");
Code language: SQL (Structured Query Language) (sql)
Insert Statement In Waiting State
Insert Statement In Waiting State

As you can see here, the query is not getting executed because it went under the waiting state. You can check it in the first session using the SHOW PROCESSLIST command.

SHOW PROCESSLIST;
Code language: SQL (Structured Query Language) (sql)
Show Processlist
Show Processlist

You can see in the highlighted area, that the query is under the waiting state. As soon as you unlock the table, the query in the second session will get executed and the data gets inserted into the table. Let’s check.

UNLOCK TABLES; SELECT * FROM emps;
Code language: SQL (Structured Query Language) (sql)
Insert After Waiting State
Insert After Waiting State

As you can see here, the data got inserted into the table as soon as the table is unlocked.

Write Lock Example

Now we will see the WRITE lock demonstration.

As per the WRITE lock documentation, we should be able to read and write from the current session that acquires the WRITE lock. But, the other sessions must not be able to read as well as write the data.

First, we will acquire the WRITE lock on the table ’emps’. Then we will insert some data into it and fetch that data to see if we are able to perform a read-write operation on the table from the same session.

LOCK TABLE emps WRITE; INSERT INTO emps(name,city)VALUES("Thomson","LV"); SELECT * FROM emps;
Code language: SQL (Structured Query Language) (sql)

Here, we have first performed the lock operation. Then we inserted some values into the table and finally, we displayed the values. This means we are able to read-write the data in the same session.

Write Lock On Table
Write Lock On Table

Now let’s try to fetch the records from another session.

SELECT CONNECTION_ID(); SELECT * FROM emps;
Code language: SQL (Structured Query Language) (sql)

Here, the query execution will go under the waiting state until the table gets unlocked.

Fetch Data From Write Locked Table
Fetch Data From Write Locked Table

Let’s check the processlist from the first session.

SHOW PROCESSLIST;
Code language: SQL (Structured Query Language) (sql)
SHOW Processlist 1
SHOW Processlist 1

You can see in the highlighted area, that the SELECT query is under the waiting state.

Let’s unlock the table and check the output on the second session.

UNLOCK TABLES;
Code language: SQL (Structured Query Language) (sql)

After unlocking the table from the first session, you will get the table data in the second session.

Fetch Data After Unlocking Table
Fetch Data After Unlocking Table

As you can see, to cross-check, we have displayed the session id as well below the query that was previously in the waiting state.

Summary

In this tutorial, we have learned –

  • What is MySQL Locking Mechanism
  • Types of MYSQL Locks
  • Syntax of MySQL Locks
  • Examples of MySQL Locks

References

MySQL Official Documentation on MySQL LOCKS.