EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL LOCK TABLE
Secondary Sidebar
SQL Tutorial
  • Advanced
    • MDF File in SQL Server
    • SQL Aliases
    • SQL Hosting
    • SQL Auto Increment
    • SQL Injection
    • SQL Wildcards
    • SQL Check
    • SQL Indexes
    • Select Distinct
    • SQL BETWEEN
    • SQLPlus spool
    • SQL Create Table
    • SQL Schema
    • Comparison Operators in SQL
    • SQL_plus
    • SQL Formatter
    • SQL LEFT INNER JOIN
    • SQL Plus Command
    • SQLPlus not found
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • SQL REVOKE
    • SQL Select Distinct Count
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • SQL DROP Table
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Quick References
    • SQL Like Wildcard
    • SQL Like with Multiple Values
    • SQL Examples
    • SQL Server Versions
    • SQL DROP DB
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • SQL Between Dates
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL SELECT DISTINCT Multiple Columns
    • SQL Null Values
    • SQL LIKE
    • SQL LIKE Query
    • SQL LIKE Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • SQL HAVING Clause
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • SQL Foreign Key
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • SQL UNIQUE Constraint
    • SQL Primary Key
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL Server Functions
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL REGEX
    • SQL Window Functions
    • SQL Syntax
    • SQL CONCAT
    • SQL ALTER TABLE
    • SQL MOD()
    • SQL Timestamp
    • SQL Min and Max
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite? functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Ranking Function
    • SQL Server Permission
  • T-SQL
    • T-SQL pivot
    • T-SQL Formatter
    • T-SQL TRY CATCH
    • T-SQL CTE
    • T-SQL CASE
    • T-SQL DATEPART
    • T-SQL Date Format
    • T-SQL ROUND
    • T-SQL Loop
    • T-SQL IIF
    • T-SQL Union
    • T-SQL CREATE TABLE
    • T-SQL INSERT
    • T-SQL Stuff
    • T-SQL ISNULL
    • T-SQL ADD Column
    • T-SQL DATEDIFF
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • SqlAlchemy
    • What is SQLAlchemy
    • SqlAlchemy ORM
    • SQLAlchemy count
    • SQLAlchemy update object
    • SQLAlchemy pip
    • SQLAlchemy Connection
    • SQLAlchemy Metadata
    • SQLAlchemy Raw SQL
    • SQLAlchemy Filter in List
    • SQLAlchemy Alias
    • SQLAlchemy unique
    • SQLAlchemy JSONB
    • SQLAlchemy Async
    • SQLAlchemy Types
    • SQLAlchemy Many to Many
    • SQLAlchemy Example
    • SQLAlchemy Model
    • SQLAlchemy Data Types
    • SQLAlchemy Filter
    • SQLAlchemy SQLite
    • SQLAlchemy DateTime
    • SQLAlchemy create_engine
    • SQLAlchemy Delete
    • SQLAlchemy Migrations
  • NoSQL
    • NoSQL Databases List
    • NoSQL Data Modeling
    • Types of NoSQL Databases
    • NoSQL Injection
    • NoSQL vs SQL Databases
    • NoSQL Use Cases
    • NoSQL Key Value
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions
    • SQL Current Month

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

SQL LOCK TABLE

By Priya PedamkarPriya Pedamkar

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.

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,768 ratings)

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

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 –

  1. SQL UNION ALL
  2. SQL DECODE()
  3. SQL LIKE Clause
  4. SQL DATEPART()
Popular Course in this category
JDBC Training (6 Courses, 7+ Projects)
  6 Online Courses |  7 Hands-on Projects |  37+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

PHP Training (5 Courses, 3 Project)4.9
Windows 10 Training (4 Courses, 4+ Projects)4.8
SQL Training Program (7 Courses, 8+ Projects)4.7
PL SQL Training (4 Courses, 2+ Projects)4.7
Oracle Training (14 Courses, 8+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

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

Let’s Get Started

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