In the world of relational database management systems, transactions play a crucial role in maintaining data integrity. SQL Server, like other RDBMS, follows the ACID properties of transactions: Atomicity, Consistency, Isolation, and Durability. In this article, we will focus on the concept of isolation and how SQL Server uses locking mechanisms to ensure transaction isolation.

Locking is a mechanism used by SQL Server to control concurrent access to data. It prevents multiple transactions from making conflicting changes to the same data simultaneously, which could lead to inconsistent results. By understanding how SQL Server manages concurrency through locking, developers and database administrators can optimize performance and minimize issues like lock waits and deadlocks.

To explore the concept of locking in SQL Server, let’s conduct a few experiments using the sys.dm_tran_locks dynamic management view. We will create a simple table called Employee and perform various operations on it to observe the different types of locks acquired by SQL Server.

Preparing Our Environment

First, let’s create the Employee table:

CREATE DATABASE DMV;
GO
USE DMV;
GO
CREATE TABLE Employee (
EmployeeID INT IDENTITY (1,1),
FName VARCHAR (50),
LName VARCHAR (50),
HireDate DATETIME,
CountryCode CHAR(2),
DeptID INT,
AvgPerfScore FLOAT
);

Next, let’s populate the table with some sample data:

INSERT INTO Employee
VALUES ('Kenneth','Igiri','20120102','NG',1,96.2);
INSERT INTO Employee
VALUES ('Eben','Owusu','20100102','GH',1,97.2);
INSERT INTO Employee
VALUES ('Prince','Frimpong','20170102','GH',3,98.2);

Experiment: Delete All Rows in Employee

In this experiment, we will invoke a delete operation on all rows in the Employee table and observe the locking behavior:

BEGIN TRAN;
USE DMV;
DELETE FROM Employee;
-- ROLLBACK (Do not commit the transaction)

While the delete transaction is still open, let’s run a SELECT statement in another session:

BEGIN TRAN;
USE DMV;
SELECT * FROM Employee;
-- ROLLBACK

Similarly, let’s run an INSERT statement in another session:

USE DMV;
INSERT INTO Employee
VALUES ('Janet','Afari','20170102','GH',3,98.2);
-- ROLLBACK

By querying the sys.dm_tran_locks view, we can observe the locks acquired by each session:

USE DMV;
SELECT 
resource_type,
DB_NAME(resource_database_id) AS database_name,
request_mode,
request_type,
request_status,
request_reference_count,
request_session_id,
resource_associated_entity_id
FROM sys.dm_tran_locks;

From the results, we can see that the session performing the delete operation acquires exclusive locks on the pages of the Employee table. The other sessions requesting locks for select and insert operations have to wait for the delete transaction to be committed or rolled back.

Experiment: Delete One Row in Employee

In this experiment, we will delete only one row from the Employee table and observe the locking behavior:

BEGIN TRAN;
USE DMV;
DELETE FROM Employee WHERE FName='Kenneth';
-- ROLLBACK

Again, let’s run a SELECT statement and an INSERT statement in separate sessions:

BEGIN TRAN;
USE DMV;
SELECT * FROM Employee;
-- ROLLBACK

USE DMV;
INSERT INTO Employee
VALUES ('Janet','Afari','20170102','GH',3,98.2);
-- ROLLBACK

Querying the sys.dm_tran_locks view will show us the locks acquired by each session:

USE DMV;
SELECT 
resource_type,
DB_NAME(resource_database_id) AS database_name,
request_mode,
request_type,
request_status,
request_reference_count,
request_session_id,
resource_associated_entity_id
FROM sys.dm_tran_locks;

In this case, we can see that the session performing the delete operation acquires an intent exclusive lock on the Employee table and an exclusive lock on the specific row being deleted. The other sessions requesting locks have to wait accordingly.

Experiment: Delete a Range of Rows in Employee

In this experiment, we will delete a range of rows from the Employee table and observe the locking behavior:

BEGIN TRAN;
USE DMV;
DELETE FROM Employee WHERE FName LIKE '%Kokou%';
-- ROLLBACK

As before, let’s run a SELECT statement and an INSERT statement in separate sessions:

BEGIN TRAN;
USE DMV;
SELECT * FROM Employee;
-- ROLLBACK

USE DMV;
INSERT INTO Employee
VALUES ('Janet','Afari','20170102','GH',3,98.2);
-- ROLLBACK

Querying the sys.dm_tran_locks view will reveal the locks acquired by each session:

USE DMV;
SELECT 
resource_type,
DB_NAME(resource_database_id) AS database_name,
request_mode,
request_type,
request_status,
request_reference_count,
request_session_id,
resource_associated_entity_id
FROM sys.dm_tran_locks;

In this case, SQL Server decides to escalate the locking from row level to table level, as the number of rows involved in the delete operation is significant. The other sessions requesting locks have to wait accordingly.

Conclusion

Through these experiments, we have gained insights into the locking and blocking behavior in SQL Server. We have seen the importance of using TRUNCATE instead of DELETE (ALL) when deleting all rows from a table. We have also highlighted the benefits of introducing a clustered index on a table to improve locking efficiency. By understanding these concepts, developers and database administrators can optimize their SQL Server environments and minimize issues related to locking and blocking.

Feel free to repeat these experiments with different data distributions and evaluate the differences in behavior. In a future article, we will explore the use of transaction isolation levels to further control these behaviors in SQL Server.

References:

• Isakov, V. (2018). Exam Ref 70-764 Administering a SQL Database Infrastructure.
• Transaction Locking and Row Versioning Guide
• Blocking Locks and Deadlocks