Updated May 29, 2023
Introduction to MySQL Lock Table
In MySQL, we can store multiple tables and records as per our requirements. Various users can simultaneously access and manipulate this database content. When multiple users access the same data and records, you must ensure that each one of them receives the same correct and appropriate information. The system performs the appropriate insertion, updating, and deletion manipulations at the correct time based on the arrival of operation requests. This ensures the consistency and correctness of the database contents.
In short, we must ensure that we appropriately manage read and write operations to prevent any inconsistencies in the database. MySQL provides the functionality of locking the tables for read and write operations. In this post, we will use an example to study how to read and write locks work, their syntax, and how to implement the concept and functionality.
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 simultaneously. MySQL provides the facility to every client session to acquire the table by locking it to prevent the other client sessions from singing that table until that particular session is completed with its task using that table.
The client session can acquire or release the locks on the table only for itself. No other client session can accept or release the locks on the table for other sessions.
The syntax for the Locking 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, you should provide the table name as “name_of_table” for the table you want to lock. Additionally, you must specify the type of lock, indicating whether it is for a READ 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 the LOCK TABLES statement, where you must mention 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],
... ;The Syntax for Unlocking the Table
The syntax for unlocking the table acquired by your session is as follows:
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. To unlock the lock, you can use the following syntax –
Syntax #3
UNLOCK TABLES;Examples to Implement MySQL Lock Table
Now, let’s discuss the two types of locks that can be acquired and how they work.
1. Read Lock
Multiple client sessions can acquire the READ lock on the table simultaneously. Even the sessions that have not acquired a lock on that table can read the contents of that table without the necessity of acquiring a lock on it. When a particular session acquires a READ lock, only read operations can be performed on that table, and you cannot manipulate the table’s contents by performing any of the write operations on it.
Also, if any other client session wants to write the data to that table, 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 ending is normal or abnormal, all the locks acquired on all the tables of reading and 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 the connection id of 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 the educba_writers table –
Code:
LOCK TABLE educba_writers READ;Output:
Trying to perform INSERT, UPDATE, or DELETE operation on the educba_writers table using current or any other session will raise an error. Consider the execution of the 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 a waiting state, as shown below –
You can confirm that by viewing processlist as follows –
Code:
SHOW PROCESSLIST;Output:
2. Write Lock
If a specific session acquires a WRITE lock, only that session can carry out read and write operations on the table until the lock is released. Other sessions cannot read from or write to the table while writing 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:
If you insert or manipulate data from other sessions, you will have to wait until the 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:
Once the current session releases the lock on the tables, the insert query initiated in another session will be executed.
Output:
Conclusion
We can acquire the read locks on the tables of MySQL, also called shared locks, that prevent any session from reaching a write lock on the table. We can use write locks, also referred to as exclusive locks, as no other session can perform read and write operations on the table than the current one.
Recommended Articles
We hope that this EDUCBA information on “MySQL Lock Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
