EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW