SQL LOCK 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:

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: