How to Troubleshoot Locking and Blocking in SQL Server?

 

Introduction

Locking and blocking are common challenges in SQL Server that can degrade database performance, causing delays and timeouts in applications. Locking is a mechanism SQL Server uses to ensure data consistency during transactions, while blocking occurs when one transaction holds a lock that prevents another from proceeding. This blog provides a detailed, real-life-oriented guide to troubleshooting these issues, complete with examples, scripts, best practices, and alternatives.

Whether you're a beginner learning the ropes or an advanced DBA tackling complex scenarios, this guide covers it all with practical, interactive content designed to be engaging and easy to understand.

---

Table of Contents

1. Understanding Locking and Blocking

2. Why Locking and Blocking Happen

3. Real-Life Scenarios and Examples

4. Basic Troubleshooting Techniques

5. Advanced Troubleshooting Techniques

6. Pros and Cons of Common Approaches

7. Best Practices and Standards

8. Alternatives to Locking and Blocking

9. Conclusion

---

Understanding Locking and Blocking

What is Locking?

Locking is SQL Server's way of ensuring data integrity during transactions. When a transaction modifies data, SQL Server places locks on the affected resources (e.g., rows, pages, or tables) to prevent other transactions from accessing them inconsistently.

Types of Locks:

• Shared (S): Used for read operations, allowing multiple transactions to read data simultaneously.

• Exclusive (X): Used for write operations, preventing other transactions from reading or modifying the data.

• Update (U): Used during updates to prevent deadlocks.

• Intent Locks: Indicate locking at a lower level (e.g., intent shared or intent exclusive).

• Schema Locks: Protect the database schema during structural changes.

What is Blocking?

Blocking occurs when one transaction holds a lock on a resource, and another transaction waits for that lock to be released. For example, if Transaction A holds an exclusive lock on a table row, Transaction B cannot read or modify that row until Transaction A completes.

Key Terms

• Deadlock: When two transactions block each other, causing neither to proceed.

• Lock Granularity: The level at which locks are applied (row, page, or table).

• Lock Escalation: When SQL Server converts fine-grained locks (e.g., row locks) to coarser ones (e.g., table locks) to save resources.

---

Why Locking and Blocking Happen

Locking and blocking are often caused by:

1. Long-Running Transactions: Transactions that take too long to complete hold locks longer, increasing the chance of blocking.

2. High Concurrency: Multiple users accessing the same data simultaneously.

3. Poorly Designed Queries: Inefficient queries that scan entire tables instead of specific rows.

4. Improper Indexing: Missing or poorly designed indexes can lead to table scans, increasing lock scope.

5. Transaction Isolation Levels: Higher isolation levels (e.g., Serializable) hold locks longer, increasing blocking.

---

Real-Life Scenarios and Examples

Let’s explore locking and blocking with real-life scenarios, complete with T-SQL scripts to simulate and troubleshoot them.

Scenario 1: Basic Blocking in an E-Commerce Database

Context: An e-commerce application updates product inventory during a sale. One transaction updates stock quantities, while another transaction tries to read the same data for a customer’s order.

Setup Script:

-- Create a sample table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    StockQuantity INT
);

-- Insert sample data
INSERT INTO Products (ProductID, ProductName, StockQuantity)
VALUES (1, 'Laptop', 100), (2, 'Phone', 200);

-- Transaction 1: Update stock (causes a lock)
BEGIN TRANSACTION;
UPDATE Products
SET StockQuantity = StockQuantity - 10
WHERE ProductID = 1;

-- Simulate a delay to keep the lock
WAITFOR DELAY '00:00:10';
COMMIT;

-- Transaction 2: Read stock (blocked by Transaction 1)
SELECT ProductName, StockQuantity
FROM Products
WHERE ProductID = 1;

Issue: Transaction 2 is blocked because Transaction 1 holds an exclusive lock on the row with ProductID = 1.

Troubleshooting:

1. Open two SQL Server Management Studio (SSMS) windows.

2. Run Transaction 1 in the first window (it will hold the lock for 10 seconds).

3. Run Transaction 2 in the second window—it will wait until Transaction 1 commits.

4. Use the following query to identify the blocking:

SELECT 
    blocking_session_id AS BlockingSessionID,
    session_id AS BlockedSessionID,
    wait_type,
    wait_time,
    wait_resource,
    OBJECT_NAME(object_id, database_id) AS ObjectName
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Output:

BlockingSessionID	BlockedSessionID	WaitType	WaitTime	WaitResource	ObjectName
52	53	LCK_M_S	5000	KEY: 5:72057594038321152 (abc123)	Products

Resolution:

• Reduce the transaction duration by optimizing the query or breaking it into smaller transactions.

• Use an appropriate isolation level (e.g., READ COMMITTED instead of SERIALIZABLE).

Scenario 2: Deadlock in a Banking System

Context: A banking application processes transfers between accounts. Two transactions try to update two accounts simultaneously, leading to a deadlock.

Setup Script:

-- Create Accounts table
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    AccountHolder NVARCHAR(100),
    Balance DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO Accounts (AccountID, AccountHolder, Balance)
VALUES (1, 'Alice', 1000.00), (2, 'Bob', 1500.00);

-- Transaction 1: Transfer from Alice to Bob
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;

-- Transaction 2: Transfer from Bob to Alice
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 2;
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 1;
COMMIT;

Issue: Transaction 1 locks AccountID = 1, and Transaction 2 locks AccountID = 2. When each tries to lock the other’s resource, a deadlock occurs.

Troubleshooting:

1. Enable deadlock logging:

DBCC TRACEON(1222, -1);

2. Check the deadlock graph in the SQL Server error log or use:

SELECT * FROM sys.dm_tran_locks
WHERE resource_type = 'KEY' AND request_status = 'WAIT';

3. SQL Server will choose one transaction as the deadlock victim and roll it back.

Resolution:

• Ensure transactions access resources in the same order (e.g., always update lower AccountID first).

• Use SET DEADLOCK_PRIORITY LOW for less critical transactions to make them the victim.

• Reduce transaction duration by optimizing queries.

---

Basic Troubleshooting Techniques

1. Identify Blocking Sessions

Use dynamic management views (DMVs) to find blocking sessions:

SELECT 
    blocking_session_id,
    session_id,
    wait_type,
    wait_time / 1000 AS WaitTimeSeconds,
    OBJECT_NAME(object_id, database_id) AS ObjectName
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

2. Check Active Locks

View current locks:

SELECT 
    resource_type,
    resource_associated_entity_id,
    request_mode,
    request_session_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('YourDatabaseName');

3. Monitor Long-Running Transactions

Identify long-running transactions:

SELECT 
    t.transaction_id,
    t.name,
    t.transaction_begin_time,
    s.session_id,
    s.login_name
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_exec_sessions s ON t.transaction_id = s.transaction_id
WHERE t.transaction_begin_time < DATEADD(MINUTE, -5, GETDATE());

4. Use Activity Monitor

In SSMS, use the Activity Monitor to view active processes, blocked sessions, and resource waits.

---

Advanced Troubleshooting Techniques

1. Extended Events for Lock Analysis

Create an Extended Events session to capture lock events:

CREATE EVENT SESSION LockAnalysis ON SERVER
ADD EVENT sqlserver.lock_acquired (
    WHERE database_id = DB_ID('YourDatabaseName')
),
ADD EVENT sqlserver.lock_released (
    WHERE database_id = DB_ID('YourDatabaseName')
)
ADD TARGET package0.event_file (
    SET filename = 'C:\Logs\LockAnalysis.xel'
);
ALTER EVENT SESSION LockAnalysis ON SERVER STATE = START;

2. Query Store for Performance Insights

Use Query Store to identify queries causing excessive locking:

SELECT 
    q.query_id,
    t.query_sql_text,
    q.avg_duration,
    q.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text t ON q.query_text_id = t.query_text_id
WHERE q.avg_duration > 1000000; -- Queries with high duration

3. Deadlock Graph Analysis

Capture deadlock graphs using SQL Server Profiler or Extended Events:

CREATE EVENT SESSION DeadlockCapture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (
    SET filename = 'C:\Logs\DeadlockCapture.xel'
);
ALTER EVENT SESSION DeadlockCapture ON SERVER STATE = START;

4. Lock Escalation Monitoring

Monitor lock escalation events:

SELECT 
    object_name,
    index_name,
    lock_escalation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL)
WHERE lock_escalation_count > 0;

---

Pros and Cons of Common Approaches

Approach 1: Reducing Transaction Duration

• Pros:

  • Minimizes lock hold time, reducing blocking.

  • Improves application performance.

• Cons:

  • Requires query optimization, which can be complex.

  • May need application code changes.

Approach 2: Adjusting Isolation Levels

• Pros:

  • Lower isolation levels (e.g., READ COMMITTED) reduce locking scope.

  • Easy to implement with SET TRANSACTION ISOLATION LEVEL.

• Cons:

  • Lower isolation levels may lead to dirty reads or non-repeatable reads.

  • Not suitable for applications requiring strict consistency.

Approach 3: Indexing Improvements

• Pros:

  • Reduces lock granularity by targeting specific rows.

  • Improves query performance.

• Cons:

  • Additional indexes increase storage and maintenance overhead.

  • Incorrect indexes can worsen performance.

---

Best Practices and Standards

1. Minimize Transaction Scope:

   • Keep transactions short and focused to reduce lock duration.

   • Avoid user input or long computations within transactions.

2. Use Appropriate Isolation Levels:

   • Default to READ COMMITTED for most applications.

   • Use READ UNCOMMITTED for reporting queries if dirty reads are acceptable.

   • Reserve SERIALIZABLE for critical transactions requiring strict consistency.

3. Optimize Queries and Indexes:

   • Use covering indexes to reduce table scans.

   • Regularly update statistics and rebuild fragmented indexes:

   UPDATE STATISTICS Products;
   ALTER INDEX ALL ON Products REBUILD;

4. Implement Deadlock Handling:

   • Use TRY...CATCH blocks to retry transactions after a deadlock:

   DECLARE @Retry INT = 3;
   WHILE @Retry > 0
   BEGIN
       BEGIN TRY
           BEGIN TRANSACTION;
           -- Your transaction code here
           COMMIT;
           BREAK;
       END TRY
       BEGIN CATCH
           IF ERROR_NUMBER() = 1205 -- Deadlock
           BEGIN
               SET @Retry = @Retry - 1;
               IF @Retry = 0 THROW;
               WAITFOR DELAY '00:00:01';
           END
           ELSE THROW;
       END CATCH;
   END;

5. Monitor and Alert:

   • Set up SQL Server Agent alerts for blocking and deadlocks.

   • Use tools like SQL Server Management Studio, Extended Events, or third-party monitoring solutions.

---

Alternatives to Locking and Blocking

1. Snapshot Isolation:

   • Uses row versioning to avoid blocking readers.

   • Enable with:

   ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;
   SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

   • Pros: Reduces blocking for read-heavy workloads.

   • Cons: Increases tempdb usage due to versioning.

2. In-Memory OLTP:

   • Uses memory-optimized tables to eliminate locking.

   • Example:

   CREATE TABLE InMemoryProducts (
       ProductID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
       ProductName NVARCHAR(100),
       StockQuantity INT
   ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

   • Pros: High performance for high-concurrency workloads.

   • Cons: Requires significant memory and schema changes.

3. Application-Level Locking:

   • Implement locking in the application layer using flags or status columns.

   • Pros: Reduces database-level locking.

   • Cons: Increases application complexity and potential for errors.

---

Conclusion

Locking and blocking are inevitable in SQL Server, but with the right tools and techniques, you can minimize their impact. By understanding the root causes, using DMVs and Extended Events for monitoring, optimizing queries, and applying best practices, you can ensure smooth database performance. For advanced scenarios, consider alternatives like snapshot isolation or in-memory OLTP to further reduce contention.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone