EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Lock Table

MySQL Lock Table

Payal Udhani
Article byPayal Udhani
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 29, 2023

MySQL Lock Table

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

educba_writers

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:

records

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:

database session

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

Code:

LOCK TABLE educba_writers READ;

Output:

MySQL Lock Table - 4

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:

MySQL Lock Table - 5

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

MySQL Lock Table - 6

You can confirm that by viewing processlist as follows –

Code:

SHOW PROCESSLIST;

Output:

MySQL Lock Table - 7

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:

MySQL Lock Table - 8

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:

MySQL Lock Table - 9

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:

MySQL Lock Table - 10

3. Unlocking the Table

Let us unlock the tables by using the following statement –

Code:

UNLOCK TABLES;

Output:

unlock

Once the current session releases the lock on the tables, the insert query initiated in another session will be executed.

Output:

unlock

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.

  1. MySQL Index
  2. MySQL Database Repair
  3. IF Statement in MySQL
  4. MySQL INSTR()
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
37+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more