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)
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)
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)
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)
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)
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)
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.
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.
Let’s check the processlist from the first session.
SHOW PROCESSLIST;
Code language: SQL (Structured Query Language) (sql)
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.
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.