Skip to main content

Deadlocks in SQL Server

Overview​

A deadlock is a state where two or more processes are waiting for each other to release a resource that the other has locked, causing processing to stop permanently.

In SQL Server, the Deadlock Monitor periodically checks the system. When it detects a deadlock, it terminates (Kill) the transaction with the lower cost (such as rollback cost) as the "Victim" and returns an error (Error Number 1205).

Deadlock Cycle​

A typical deadlock occurs in a cycle like this:

  1. Transaction A locks Resource 1.
  2. Transaction B locks Resource 2.
  3. Transaction A requests a lock on Resource 2 (Wait).
  4. Transaction B requests a lock on Resource 1 (Wait).

Since they are waiting for each other's resources, neither can proceed.

Main Query Structures and Patterns Causing Deadlocks​

Deadlocks do not occur simply because a query is "heavy", but due to conflicts in resource access order or lock types.

1. Reverse Order Access​

This is the most basic and frequent pattern. It occurs when two transactions access multiple tables (or rows) in reverse order.

  • Transaction A: Table X -> Table Y
  • Transaction B: Table Y -> Table X
-- Transaction A
BEGIN TRAN
UPDATE TableX SET Col1 = 1 WHERE Id = 1;
-- Assume some processing time here
UPDATE TableY SET Col1 = 1 WHERE Id = 1; -- Waiting for lock on TableY
COMMIT

-- Transaction B
BEGIN TRAN
UPDATE TableY SET Col1 = 2 WHERE Id = 1;
-- Assume some processing time here
UPDATE TableX SET Col1 = 2 WHERE Id = 1; -- Waiting for lock on TableX (Deadlock occurs)
COMMIT

2. Conflict due to Bookmark Lookup​

This pattern occurs between SELECT statements and UPDATE/DELETE statements. Reading using a non-clustered index (SELECT) conflicts with updating data (UPDATE).

  • SELECT side: Reads non-clustered index -> Tries to reference Heap/Clustered Index (Key Lookup/RID Lookup) (S Lock).
  • UPDATE side: Updates Heap/Clustered Index -> Tries to update non-clustered index (X Lock).

Deadlock occurs because access paths are in opposite directions.

3. Serializable Isolation Level (Range Lock)​

When the isolation level is set to SERIALIZABLE, Range Locks are acquired. For example, if you perform processing like checking for a non-existent row and then inserting it (like Upsert) in parallel, both acquire Range Shared Locks (RangeS-S), and then when they try to convert to Exclusive Locks (X) for insertion, they interfere with each other's S locks, causing a Conversion Deadlock.

General Countermeasures​

While it may be difficult to completely prevent deadlocks, the following measures can significantly reduce their frequency.

1. Unify Access Order​

Unify the order in which tables and resources are accessed in all transactions. Decide on rules such as Parent Table -> Child Table order, or ascending order of IDs, and implement them.

2. Shorten Transactions​

Keep transactions as short as possible.

  • Do not include user input waiting within transactions.
  • Break batch processing into smaller parts.
  • Move unnecessary processing outside of transactions.

The shorter the lock holding period, the lower the possibility of conflict.

3. Use Appropriate Isolation Level​

If possible, use an isolation level with fewer lock conflicts.

  • Read Committed Snapshot Isolation (RCSI): Read operations no longer block write operations (do not acquire S locks), and writes do not block reads. This can resolve many deadlocks (especially read/write conflicts).

    ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT ON;

4. Index Tuning (Covering Index)​

To prevent deadlocks due to bookmark lookups, create "covering indexes" that include all columns required for the query. This eliminates the need for lookups to the base table and breaks the deadlock cycle.

CREATE NONCLUSTERED INDEX IX_Table_Col1 ON Table (Col1) INCLUDE (Col2, Col3);

5. Use UPDLOCK Hint​

For logic that "reads then updates", specify UPDLOCK at the time of reading to acquire a lock with update intent from the beginning. This prevents conversion deadlocks (deadlocks caused by waiting for conversion from S lock to X lock).

BEGIN TRAN
-- Acquire update lock from the beginning
SELECT * FROM TableX WITH (UPDLOCK) WHERE Id = 1;
UPDATE TableX SET Col1 = 1 WHERE Id = 1;
COMMIT

6. Implement Retry Logic​

Since deadlocks are often temporary states, it is recommended to incorporate logic on the application side to retry the entire transaction when an error (1205) is detected.

// Image of retry logic in C#
int retryCount = 0;
while (retryCount < 3)
{
    try
    {
        // Transaction processing
        break; // Exit loop if successful
    }
    catch (SqlException ex) when (ex.Number == 1205) // Deadlock error number
    {
        retryCount++;
        Thread.Sleep(1000); // Wait a bit before retrying
    }
}

7. Use NOLOCK Hint (Caution Required)​

Adding the WITH (NOLOCK) hint to SELECT statements is a well-known countermeasure for deadlocks, but it requires caution due to significant side effects.

Pros​

  • Avoid Blocking: Does not acquire shared locks (S locks), so it can read data even while it is being updated without waiting.
  • Reduce Deadlocks: Since reads and writes do not block each other, deadlocks caused by this are eliminated.
  • Improve Performance: Lock overhead is reduced, potentially improving throughput.

Cons​

  • Dirty Read: You may read uncommitted data (which might be rolled back later).
  • Data Inconsistency: If you read while data movement (such as page splits) is occurring, you might read the same row twice or skip rows (adverse effects of Allocation Order Scan).
  • Potential Errors: Reading inconsistent data may cause unexpected errors on the application side.

Recommendation/Non-Recommendation​

  • Basically not recommended. Especially in scenarios where data accuracy is required, such as financial systems or inventory management, it should not be used.
  • Recommended Alternative: Consider enabling Read Committed Snapshot Isolation (RCSI) instead of NOLOCK. Using RCSI prevents dirty reads (reads consistent previous versions) while avoiding blocking between reads and writes.
  • Acceptable Scenarios: Should be limited to approximate reports where strict accuracy is not required, or debugging purposes where the risk of dirty reads is completely acceptable.