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.
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.
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:
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:
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:
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:
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:
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:
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:
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:
Code:
SELECT * FROM sales_count;
Output:
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.
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
This is a guide to SQL LOCK TABLE. Here we discuss the introduction to SQL LOCK TABLE along with the examples for better understading. You may also have a look at the following articles to learn more –
