EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL LOCK TABLE

SQL LOCK TABLE

Priya Pedamkar
Article byPriya Pedamkar

Updated March 10, 2023

SQL LOCK TABLE

Introduction to SQL LOCK TABLE

LOCK in SQL standard is used to ensure that database transactions such as read and write operations are performed in an atomic, consistent and durable manner. We usually have two types of LOCKS in SQL databases, namely READ or WRITE, the function of the former lock is to prevent any new updating or inserting into the database table and the later is to prevent other users from using the table at all. The nomenclature of LOCK might differ from one DBMS to another, but the underlying principle remains the same.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

For those who are still wondering what a LOCK is used for in practical life, then here is an example for them. Consider a database table used in a bank and it is being used by more than one user at a time. Let’s say two bank employees try to update the same bank account for two different transactions. Employee 1 and Employee 2 both retrieve the account details from the same table. Employee 1 updates something and commits a transaction. Employee 2 also updates something and saves the result, based on the original record details. But during this process he overwrites the transaction made by Employee 1. The record no longer reflects the changes made by Employee 1. The simple solution to this problem is exclusive use of tables, one at a time and this is possible by using the LOCK command

For the purpose of this article, we have used PostgreSQL as our SQL database. The syntax remains more or less the same.

Syntax of SQL LOCK TABLE

The basic syntax used for LOCKING a table in SQL is as follows:

LOCK [ TABLE ] [ ONLY ]
table_name
[ IN lock_mode MODE ] [ NOWAIT ];

The parameters used in the above-mentioned syntax are as follows:

  • table_name: Name of the table on which you want to apply LOCK.
  • lock_mode: The kind of lock you want to place on the table. You may choose one from {access share. row share, share update exclusive, share, exclusive, access exclusive, row exclusive, share row exclusive}. Each mode serves a different purpose.
Note: By default, ACCESS EXCLUSIVE lock is acquired. This lock guarantees that only one session can have this lock and only the session with this lock can execute transactions on the mentioned table.

Examples of SQL LOCK TABLE

Given below are the examples mentioned :

In order to illustrate the usage of SQL LOCK in relational databases, let us create a dummy table called “sales”.

The CREATE TABLE statement for it looks something as follows:

Code:

CREATE TABLE sales (
order_id int,
salesman_name character varying(50),
product_id character varying(50),
sales_region character varying(50),
sales_date date
);

Output:

SQL LOCK TABLE 1

The sales table has been successfully created. Our next task is to insert a few records in it to work with. Here is the insert statement for the same.

Code:

INSERT INTO public.sales(
order_id, salesman_name, product_id, sales_region, sales_date)
VALUES (1,'Mohit K','Book11','New Delhi','2020-05-01'),
(2,'Rey Holt','Book11','Mumbai','2020-05-02'),
(3,'Swati Singh','Book24','New Delhi','2020-05-03'),
(4,'Indrani K','Book24','Mumbai','2020-05-01'),
(5,'Dave Prakash','Book11','Mumbai','2020-05-02'),
(6,'Joshua S','Book24','New Delhi','2020-05-03'),
(7,'Mrinali Pal','Book11','New Delhi','2020-05-04'),
(8,'Mohit K','Book24','New Delhi','2020-05-02'),
(9,'Rey Holt','Book24','Mumbai','2020-05-05'),
(10,'Indrani K','Book11','Mumbai','2020-05-04'),
(11,'Joshua S','Book24','New Delhi','2020-05-05'),
(12,'Mohit K','Book11','New Delhi','2020-05-04');

Output:

SQL LOCK TABLE 2

Having inserted the records in the sales table, let’s check if the desired rows have been successfully inserted using a SELECT statement.

Code:

SELECT * FROM sales;

Output:

SQL LOCK TABLE 3

The data has been successfully inserted.

Example #1

Insert the total number of orders placed in each region based on records mentioned in the sales table in a new table called “sales_count” in locktable mode. The create statement for the same is as follows.

Code:

CREATE TABLE sales_count(
sales_region VARCHAR,
tot_orders INT
);

Output:

SQL LOCK TABLE 4

Code:

BEGIN WORK;
LOCK TABLE sales IN SHARE MODE;
INSERT INTO sales_count(sales_region,tot_orders)
SELECT sales_region, count(order_id) as tot_orders
FROM sales
GROUP BY sales_region;
COMMIT WORK;

Output:

SQL LOCK TABLE 5

The transaction was completed successfully.

Let’s check if the desired changes have been made in the sales_count table.

Code:

SELECT * FROM sales_count;

Output:

Let’s check if the desired changes have been made

You might have noticed that we have used SHARE MODE lock here. You must be wondering what difference does it make? The SELECT command in the SHARE mode acquires a lock on the mentioned table and while the transaction is being executed in this lockmode, the other users can read the table but not modify it.

Example #2

Assuming that “Rey Holt” was a fictitious character and no true sales have been made by him. Hence delete all the records from the both tables wherever “Rey Holt” made sales in LOCK TABLE mode.

Code:

ROLLBACK;
BEGIN WORK;
LOCK TABLE sales IN SHARE ROW EXCLUSIVE MODE;
UPDATE sales_count
SET tot_orders =  (SELECT tot_orders FROM (SELECT sales_region, count(order_id) as tot_orders
FROM sales
WHERE salesman_name <>'Rey Holt'
GROUP BY sales_region)s WHERE sales_region = 'Mumbai')
WHERE sales_region = 'Mumbai';
DELETE FROM sales WHERE salesman_name = 'Rey Holt';
COMMIT WORK;

Output:

SQL LOCK TABLE 7

Note: In order to ensure that no other transaction is interfering with the current transaction use ROLLBACK command.

The transaction got executed successfully.

Let’s check using SELECT statements if the desired changes have been made in both the tables.

Code:

SELECT * FROM sales;

Output:

SELECT * FROM sales;

Code:

SELECT * FROM sales_count;

Output:

SELECT * FROM sales_count;

In this example, you might have noticed that we have used a ROW EXCLUSIVE MODE. What is it exactly? This lock mode protects the mentioned table from concurrent data changes and modifications, and it is exclusive in nature. Only one session can use it at a time. Also, it is never automatically acquired.

Note: In postgreSQL, we do not have any command such as UNLOCK TABLE. The LOCK is nullified once the transaction is complete.

Conclusion

In this article, we saw SQL LOCKS. LOCK TABLE command in SQL is used to prevent deadlocks and concurrent data changes and modifications to maintain consistency and atomicity of transactions on database tables.

Recommended Articles

We hope that this EDUCBA information on “SQL LOCK TABLE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL UNION ALL
  2. SQL DECODE()
  3. SQL LIKE Clause
  4. SQL DATEPART()
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
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
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