The statement LOCK TABLE allows us to explicitly acquire the shared or exclusive table lock on a specified table. The table lock will last until the current transaction’s end. In order to lock a table, one must be either the owner of the database or the owner of the table.
In this article, we will dive deeper into the LOCK Table Statement in SQL according to the GATE Syllabus for (Computer Science Engineering) CSE. Keep reading ahead to learn more.
Table of Contents
- What is a LOCK Table in SQL?
- Creation of Database
- Creation of Database CHOCOLATE
- Adding Tables to the DB
- Insertion of Rows in a Table
- SQL Locks
- Overcoming the Running Process
- Alternative Way
- Conclusion
What is a LOCK Table in SQL?
SQL Server is a very versatile database that is widely used in the software industry. It is the most widely used Relational Database. Let’s look at the SQL Lock table in SQL Server using some practical examples in this article. A relational DB (database) is defined as one that satisfies the (A) Atomicity, (C) Consistency, (I) Isolation, and (D) Durability requirements. A lock is maintained in SQL Server in order to maintain ACID principles.
Let’s look at the fundamentals of the Lock mechanism using Azure Data Studio, starting with constructing the database, tables, locks, and so on. Azure Data Studio is compatible with Windows 10, Mac OS X, and Linux.
Creation of Database
Given below is the command to create the CHOCOLATE database. CHOCOLATE here is the name of the database.
— CREATE DATABASE
<name_of_database>;
Creation of Database CHOCOLATE
To make the DB active,
USE CHOCOLATE;
Adding Tables to the DB
Create a table with a Primary Key. Here, ID is the PRIMARY KEY, which means that each author will have their own ID.
CREATE TABLE Editors (
ID INT NOT NULL PRIMARY KEY,
<name of column 1> <datatype> <not null/null>,
……….
);
If “NOT NULL” is explicitly specified, then that column must have values. In case it’s not specified, it is “NULL” by default.
Example,
CREATE TABLE Editors (
ID INT NOT NULL PRIMARY KEY,
EditorName VARCHAR(255) NOT NULL,
Age INT,
Skill Sets VARCHAR(255),
NumberOfArticles INT
);
Insertion of Rows in a Table
There would be some scenarios where we can either add all columns or a few column values to a given table. The reason is that some columns might need (by default) null values.
Example 1
INSERT INTO <name_of_table> (1_column, 2_column, 3_column, …) VALUES (1_value, 2_value, 3_value, …);
The above query only inserts the required values for the mentioned columns because they are taken into account.
Example 2
INSERT INTO <name_table> VALUES (1_value, 2_value, 3_value, …);
We are not specifying any columns here, which means that all of the values for all of the columns must be inserted.
Method of Example 1:
— Only ID, EditorName, Age columns would be filled up, and the result would be:
INSERT INTO Editors (ID, EditorName, Age) Values (1, ‘Choc1’ ,25);
GO
Method of Example 2:
INSERT INTO Editors VALUES (1, ‘Choc1’ ,25, ‘Java,Python’ ,10);
GO
Output:
The violation of the PRIMARY KEY constraint ‘PK__Authors__3214EC277EBB8ED1’.
Can’t insert a duplicate key in the object ‘dbo.Authors’. The duplicate key value would be (1).
The errors that occurred above in the code show that the column of “ID” is unique, and it must not have any duplicate value. Let us now correct this and query this table by using:
SELECT * FROM <name_of_table>
Method of Example 1:
— Only the ID, EditorName, Age columns would be filled in. The test would have:
INSERT INTO Editors (ID, EditorName, Age) VALUES (1, ‘Choc2 ,25);
GO
Method of Example 2:
INSERT INTO Editors VALUES (2, ‘Choc2’ ,25, ‘Java,Python’ ,10);
GO
Output:
| ID | EditorName | Age | Skill Sets | NumberOfArticles | |
| 1 | 1 | Choc1 | 25 | NULL | NULL |
| 2 | 2 | Choc2 | 25 | Java,Python | 10 |
It can be observed that Row 1 has ‘Null’ values in place of the ‘Skill Sets’ column and the ‘NumberOfArticles’ column. The reason is that as we haven’t specified the values for those columns, thus it has taken the default Null values.
SQL Locks:
Because SQL Server is a relational database, data consistency is a critical feature that may be achieved with SQL Locks. After a transaction begins, SQL Server creates a lock, which is released when the transaction is completed. There are various types of locks available, which are mentioned below:
1. Shared (S) Locks: This type of lock would occur whenever the object is required to be read, but this is not harmful.
2. Exclusive (X) Locks: It would prevent other transactions such as deleting, updating, inserting, etc. Thus, no modifications could be made to a locked object.
3. Update (U) Locks: It’s more or less similar to the Exclusive lock. The difference is that the operation can be viewed here as the “read phase” and the “write phase”. The other transactions are especially prevented during the read phase.
4. Intent Locks: The intent lock is on the table when the SQL Server has the S (shared) lock or X (exclusive) lock on a row.
5. Regular intent locks: (IX) Intent exclusive, (IS) Intent shared, and (IU) Intent update.
6. Conversion locks: (SIX) Shared with intent exclusive, (SIU) Shared with intent update, and (UIX) Update with intent exclusive.
If the lock hierarchy starts from the database, then the table, and then the row:
Database —> Table —> Page —> Row
On a database level, the shared lock is critical because it prevents the database from being dropped or a database backup from being restored over the current database.
When the “SELECT” statement is issued, then a lock occurs.
Database (Shared Lock (S)) —> Table (Intention Shared Lock (IS)) —> Page (Intention Shared Lock (IS)) —> Row (Shared Lock (S))
During the execution of the DML statement, meaning, either during delete/update/insert:
Database (Shared Lock (S)) —> Table (Intent Exclusive or Intent Update) —> Page (Intent Exclusive or Intent Update) —> Row (Exclusive or Update Lock)
Let us see what the locking mechanisms would be with our example:
— Create an open transaction, analyze the locked resources.
BEGIN TRAN
for ID = 1, update the Skill Sets column
UPDATE Editors SET Skill Sets=’PHP,Android,Java’ where ID = 1
select @@SPID
Output:
| (No column name) | |
| 1 | 52 |
We will insert some more records (about 100) into the table. Let us update a few columns using a transaction, as well as apply the select query parallelly also.
— Create an open transaction. Analyze the locked resources.
BEGIN TRAN
— Update the Skill Sets when ID < 20
UPDATE Editors SET Skill Sets=’R Programming,Android,Java’ where ID < 20
— Update the Skill Sets when ID >= 25
UPDATE Editors SET Skill Sets=’R Programming,IOS,Android’ where ID >= 25
–Other DML statements such as Delete/Update. This statement must take a long time
–(if huge updates happen) as the previous statement
–is either not rolled back or committed yet
SELECT * FROM Editors;
select @@SPID
Actually, if the previous command transaction is not yet complete (if there are large records, at least 100 records) and an update is occurring on each and every row, we proceed to another set of commands, such as “select,” the status may be “Awaiting” (Queries that are executing) or “Suspended” (Queries which are halt).
Overcoming the Running Process
We will now see how to overcome the running process so far.
KILL <spid> -> Killing the session
(Or) apply inside a transaction after every query
COMMIT -> Committing the changes
ROLLBACK -> Rolling Back the changes
By doing it, we enforce the operation to either get committed or rolled back, depending upon every requirement that it has to carry out. Unless we know whether the entire process is required, we cannot commit or roll back this given transaction.
Alternative Way
Using the NOLOCK with SELECT QUERY, one can overcome
SELECT * FROM Editors WITH (NOLOCK);
For the SELECT statement status, with the help of the sp_who2 command, the query would run without waiting for the successful completion of the UPDATE transaction and releasing the locking on a table,
SELECT * FROM Editors WITH (READUNCOMMITTED);
Conclusion
SQL Locks are very essential for any RDBMS. The SQL Server handles these locks in the ways mentioned in this article.
Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments