MySQL Lock Table

Introduction to MySQL Lock Table

In MySQL, we can store multiple tables and records in them as per our requirement. This database content can be accessed and manipulated by multiple different users simultaneously. When the same data and records are accessed by multiple users, you have to make sure that each one of them should receive the same information that is correct and appropriate. The manipulations of insertion, updation, and deletion are performed appropriately at the correct time according to the arrival of operation requests to maintain consistency and correctness of database contents.

In short, we have to make sure that read and write operations are managed properly to avoid any inconsistency in the database. MySQL provides the functionality of locking the tables for read and write operations. In this article, we will learn about the working of read lock, write lock, their syntax, and implement the concept and functionality using an example.

Locking the Table

The lock can be considered as the flag that is maintained against every table in MySQL to manage the access of the database when multiple client sessions are trying to use the same table at the same point in time. MySQL provides the facility to every client session to acquire the table by locking it to prevent the other client sessions to use that table until that particular session is completed with its task of usage of that table.

The locks on the table can be acquired or released by the client session only for itself. No other client session can acquire or release the locks on the table for other sessions.

Syntax for Locking the Table

When we want to apply a lock on a single table then we can use the following syntax –

Syntax #1

LOCK TABLE name_of_table [READ | WRITE];

In the above syntax name_of_table is the name of the table that you wish to lock and further you need to specify the type of the lock whether the lock is to be acquired for READ operation or WRITE operation on that table.

In case, you want to acquire the lock on multiple tables then you can use the following syntax of LOCK TABLES statement in which you need to specify the name of the tables and the type of the lock which you want to acquire on each one of them in the comma-separated fashion –

Syntax #2

LOCK TABLES name_of_table1 [READ | WRITE],
name_of_table2 [READ | WRITE],
name_of_table3 [READ | WRITE],
... ;

Syntax for Unlocking the Table

Syntax of unlocking the table acquired by your session:

If you have locked a particular table for performing certain operations on it, you need to release the lock to allow other tables to access that table. For unlocking the lock, you can use the following syntax –

Syntax #3

UNLOCK TABLES;

Examples to Implement MySQL Lock Table

Let us now discuss the two types of locks that can be acquired and it’s working.

1. Read Lock

Multiple client sessions can acquire the READ lock on the table simultaneously. Even the sessions that have not acquired lock on that table can read the contents of that table without the necessity of acquiring a lock on it. When a READ lock is acquired by a particular session then only read operations can be performed on that table and you cannot manipulate the contents of that table by performing any of the write operations on it.

Also, if any other client session wants to write the data to that table then their operations are put into the waiting state and can only be performed when the read lock on that table is released. At the time of termination of the session whether the termination is normal or abnormal, all the locks acquired on all the tables of reading as well as WRITE types are released.

Let us create one table named educba_writers using the following query statement –

Code:

CREATE TABLE `educba_writers` (
`id` int(11) PRIMARY KEY,
`firstName` varchar(10) COLLATE latin1_danish_ci NOT NULL,
`rate` decimal(5,2) DEFAULT NULL,
`joining_date_time` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

Output:

Let us insert some records in it –

Code:

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date_time`) VALUES
(1, 'Payal', '750.00', '2020-05-28 16:02:34'),
(2, 'Vyankatesh', '700.00', '2020-05-29 16:13:19'),
(3, 'Omprakash', '600.00', '2020-05-28 20:32:50'),
(4, 'Parineeta', '980.00', '2020-05-28 13:35:11');

Output:

Before we acquire READ LOCK on the educba_writers table, if we want to know connection id our current database session then we can use the following statement –

Code:

SELECT CONNECTION_ID();

Output:

Now, we will use the following statement to acquire the lock on educba_writers table –

Code:

LOCK TABLE educba_writers READ;

Output:

Now, is we try to perform INSERT, UPDATE or DELETE operation on educba_writers table uisng current or any other session then it will raise an error. Consider execution of following statement –

Code:

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date_time`) VALUES
(5, 'Pihu', '750.00', '2020-05-28 16:02:34');

Output:

If you try to fire the same query from another session it will go to waiting state as shown below –

You can cofirm that by viewing processlist as follows –

Code:

SHOW PROCESSLIST;

Output:

2. Write Lock

When WRITE lock is acquired by a certain session then only that session can perform read and write operations on that table till lock is not released. Other sessions cannot read or write the table to that table when it is write-locked. Let us write lock educba_writers table –

Code:

LOCK TABLE educba_writers WRITE;

Output:

Let us perform the insert operation and check the result –

Code:

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date_time`) VALUES
(6, 'Priya', '650.00', '2020-05-29 16:02:34');

Output:

Inserting or manipulating from other sessions will result into waiting till lock is released.

Code:

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date_time`) VALUES (7, 'Priyanka', '650.00', '2020-05-29 16:02:34');

Output:

3. Unlocking the Table

Let us unlock the tables by using the following statement –

Code:

UNLOCK TABLES;

Output:

You can observe that as soon as the lock on the tables from the current session is released the insert query will get executed that we have fired on another session –

Output:

Conclusion

We can acquire the read locks on the tables of MySQL that are also called as shared locks that prevent any session to acquire write lock on the table. We can use write locks which are also referred to as the exclusive locks as no other session can perform read and write operations on the table than the current one.

Recommended Articles

This is a guide to MySQL Lock Table. Here we discuss an introduction to MySQL Lock Table, syntax, how does it work with respective examples. You can also go through our other related articles to learn more –

1. MySQL Index
2. MySQL Database Repair
3. IF Statement in MySQL
4. MySQL INSTR()