SQL Server Transaction Locks: Identification & Resolution

SQL Server is a Relational Database Management System (RDBMS) that allows multiple users and applications to access data concurrently. However, to ensure data consistency and integrity during simultaneous database operations, SQL Server employs a locking mechanism. While locks are a necessary part of database management, they can sometimes lead to contention, performance degradation, or even deadlocks in poorly designed systems.

In this article, we'll explore what SQL Server transaction locks are, how to identify them, and strategies for resolving or mitigating locking-related issues.

What is a SQL Server Transaction Lock?

A transaction lock in SQL Server is a mechanism that prevents multiple users or processes from modifying the same data simultaneously, which could lead to data corruption or inconsistencies. Locks ensure that once a user or process accesses data, no other process can interfere with that access until the transaction completes.

Types of Locks in SQL Server

SQL Server uses different types of locks to manage concurrency.

• Shared Lock (S): Allows read-only access to data. Multiple transactions can hold shared locks on the same resource simultaneously, but no transaction can modify the data until the shared locks are released.
• Exclusive Lock (X): Prevents other transactions from reading or modifying data. Only one transaction can hold an exclusive lock on a resource at any given time.
• Update Lock (U): Prevents deadlocks during updates by ensuring that only one transaction can prepare to modify data.
• Intent Locks: Used to signal a lock hierarchy. For example, an Intent Shared (IS) lock indicates that a transaction intends to read a lower-level resource, while an Intent Exclusive (IX) lock signals a write intent.
• Schema Locks: Acquired on database objects (like tables or indexes) during schema-altering operations, such as DDL commands (ALTER, CREATE, etc.).
• Bulk Update Locks: Used during bulk-insert operations to optimize performance.

How to Identify Transaction Locks in SQL Server?
 

Using Dynamic Management Views (DMVs)

SQL Server provides several Dynamic Management Views (DMVs) that can help identify locking issues. Some of the most commonly used DMVs and system views are:

1. sys.dm_tran_locks
   • This DMV shows all the current locks in the system, along with details about the resource and type of lock.
   • Example query

     SELECT 
         request_session_id     AS SessionID,
         resource_type           AS ResourceType,
         resource_description    AS ResourceDescription,
         request_mode            AS LockMode,
         request_status          AS LockStatus
     FROM 
         sys.dm_tran_locks;

2. sys.dm_exec_requests
   • Provides information about currently running queries, including whether they are waiting on a lock.
   • Example query.

     SELECT 
         session_id           AS SessionID,
         blocking_session_id  AS BlockingSessionID,
         wait_type            AS WaitType,
         wait_time            AS WaitTime_ms,
         status               AS QueryStatus,
         command              AS CommandType
     FROM 
         sys.dm_exec_requests
     WHERE 
         blocking_session_id <> 0;
     

3. sys.dm_os_waiting_tasks
   • Displays information about tasks waiting for resources, including locks.
   • Example query.

     SELECT 
         waiting_task_address     AS TaskAddress,
         session_id               AS SessionID,
         wait_type                AS WaitType,
         resource_description     AS ResourceDescription,
         blocking_session_id      AS BlockingSessionID
     FROM 
         sys.dm_os_waiting_tasks
     WHERE 
         blocking_session_id <> 0;
     

Using Activity Monitor in SQL Server Management Studio (SSMS)

The Activity Monitor in SSMS is a visual tool that allows you to view blocked sessions and identify locking issues. To use it.

1. Open SSMS and connect to your SQL Server instance.
2. Right-click the instance name and select "Activity Monitor."
3. Look for "Processes" under the "Overview" section. Here, you can see any processes being blocked and their blockers.

How to Resolve SQL Server Locking Issues?

Identify and Kill Blocking Sessions

Blocking occurs when one session holds a lock on a resource that another session needs. You can resolve blocking by identifying the blocker and optionally terminating the blocking process.

KILL <SessionID>;

Note. Use the KILL command with caution, as it terminates the session immediately and rolls back its transactions.

Practical Example: Review the above commands

We will create a table named Employee, insert some sample data into it, and write a query to apply a lock using a transaction. and then identify the locks using the above command, and terminate the lock on the table.

1. Create the Employee Table

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName  VARCHAR(50),
    LastName   VARCHAR(50),
    Department VARCHAR(50),
    Salary     DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO Employee (
    EmployeeID,
    FirstName,
    LastName,
    Department,
    Salary
) VALUES
    (1, 'John', 'Doe', 'Engineering', 75000),
    (2, 'Jane', 'Smith', 'Marketing', 65000),
    (3, 'Sam', 'Brown', 'HR', 72000),
    (4, 'Lisa', 'Johnson', 'Finance', 80000);

3. Apply a Lock Using a Transaction

You can apply a lock on rows within the transaction. This ensures that no other transaction modifies or reads those rows until your transaction is complete.

Here's an example where we lock rows for employees in the "Engineering" department.

-- Start the transaction
BEGIN TRAN;

-- Select the rows and lock them for update
SELECT * 
FROM Employee
WHERE Department = 'Engineering';

-- Perform your updates or additional logic here
UPDATE Employee
SET Salary = Salary + 5000
WHERE Department = 'Engineering';

-- Commit the transaction
-- COMMIT;

Here, the current session is 52 & marked in Yellow. After running the above command without rollback/commit, the transaction is in active state and applies the lock on rows having Department "Engineering" in the absence of the Transaction Complete command, either RollBack or Commit.

Let's review the locks using DMV.

Open a new Session (id=53), and run the DMV command to identify the locks. Here we can see active locks on session 52.

Let's terminate the active Transaction using

Kill 52.

Now, again, review the active transaction using DMV commands.

Additionally, Test Sample to view active Locks using SSMS Activity Monitor.

The user can revisit the Activity Monitor in SSMS and view the session status.

To further stimulate the transaction lock issue, I ran the same transaction query without committing in sessions 75 and 76, resulting in the query being in the SUSPENDED/RUNNING state and showing the lock as well.

Testing DMV queries under the DeadLock scenario

creates two sessions and runs the below query in sessions 60, 64. Here, transaction 1 is updating Table 1 and Table 2, and has a delay in between.

And I ran the same query under session 64 caused the Deadlock in between.

Now run all 3 DMV queries in a new session to troubleshoot the locks and found the interesting detail below.

Session 60 is in a wait state, and session 64 has the lock.

Additional Steps to Minimize the Locking Issues

Step 1. Optimize Queries to Reduce Lock Contention.

• Use indexing effectively to speed up data reads and minimize the time locks are held.
• Avoid long-running transactions that hold locks for extended periods.
• Retrieve only the rows and columns you need using SELECT statements with filters. For example,

  SELECT Name, Age
  FROM Employees
  WHERE Department = 'IT';
  

Step 2. Use NOLOCK or Read Uncommitted Isolation Level.

If you're performing read-only operations and can tolerate some level of dirty reads, you can use the WITH (NOLOCK) hint or the READ UNCOMMITTED isolation level to prevent acquiring shared locks.

SELECT Name, Age
FROM Employees (NOLOCK)
WHERE Department = 'IT';

Warning: Using NOLOCK may result in reading uncommitted or inconsistent data.

Step 3. Check and Adjust the Isolation Level.

Different isolation levels control how locks are managed. Using a lower isolation level (e.g., READ COMMITTED instead of SERIALIZABLE) may help reduce lock contention.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Step 4. Implement Query Timeouts.

To avoid indefinite waits on locked resources, configure a query timeout. This gives the transaction a grace period before aborting operations if a lock cannot be acquired.

Step 5. Partition Data.

If locks are frequently occurring in a large table, consider partitioning the table to divide data into smaller, more manageable chunks. This reduces contention for frequently accessed rows.

Step 6. Use Snapshot Isolation or Read Committed Snapshot Isolation (RCSI).

Enabling snapshot isolation levels reduces contention caused by locks by using row versions instead of acquiring locks for reading operations.
To enable RCSI for a database.

ALTER DATABASE [DatabaseName] 
SET READ_COMMITTED_SNAPSHOT ON;

Step 7. Detect and Resolve Deadlocks.

Deadlocks occur when two or more transactions permanently block each other by holding locks that the other needs. SQL Server automatically detects and resolves deadlocks by terminating one transaction (the deadlock victim). To investigate deadlocks, enable the trace flag or use Extended Events.

Example of enabling a deadlock trace flag.

DBCC TRACEON (1222, -1);

Conclusion

SQL Server locks are essential for ensuring data consistency and integrity, but they can cause performance bottlenecks if not properly managed. Identifying locking issues using DMVs, Activity Monitor, and other tools is the first step toward resolving the issue. Once identified, you can address the root cause through query optimization, isolation level adjustments, or even terminating problematic sessions.

Understanding how SQL Server transactions and locks work enables database administrators and developers to design systems that are both performant and resilient under heavy loads. By following best practices and monitoring locking behavior, you can minimize contention and ensure smoother database operations.