SQL Server Error 1205: Transaction Deadlocked — How to Retry or Avoid Deadlock

 

Introduction

SQL Server Error 1205: Your transaction (process ID #) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. occurs when two or more transactions block each other, causing SQL Server to terminate one to resolve the conflict. This guide provides code-oriented solutions to retry transactions and prevent deadlocks, ensuring robust database operations.

---

1. Understand the Deadlock

A deadlock occurs when two transactions hold locks on resources and request locks on each other’s resources, creating a cycle. SQL Server selects a "victim" to terminate, triggering Error 1205.

Code-Oriented Solution

Enable deadlock tracing to identify the involved queries and resources:

-- Enable trace flag to log deadlock details
DBCC TRACEON (1222, -1);

-- View deadlock details in SQL Server error log
EXEC xp_readerrorlog 0, 1, N'deadlock';

Action:

• Check the error log at C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\ERRORLOG for the deadlock graph.

• Identify the conflicting queries, tables, and locks (e.g., row, page, or table locks).

Why? Understanding the deadlock’s cause (e.g., conflicting UPDATE or SELECT operations) is key to prevention.

---

2. Implement Retry Logic

Automatically retry the transaction after a deadlock occurs.

Code-Oriented Solution

Use T-SQL to implement retry logic:

DECLARE @RetryCount INT = 0;
DECLARE @MaxRetries INT = 3;
DECLARE @ErrorNumber INT;

WHILE @RetryCount <= @MaxRetries
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Your transaction code here
        UPDATE Orders
        SET OrderStatus = 'Processed'
        WHERE OrderID = 1;

        INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate)
        VALUES (1, 'Processed', GETDATE());

        COMMIT TRANSACTION;
        BREAK; -- Exit loop on success
    END TRY
    BEGIN CATCH
        SET @ErrorNumber = ERROR_NUMBER();
        IF @ErrorNumber = 1205 -- Deadlock
        BEGIN
            SET @RetryCount = @RetryCount + 1;
            IF @RetryCount <= @MaxRetries
            BEGIN
                WAITFOR DELAY '00:00:01'; -- Wait 1 second before retry
                CONTINUE;
            END
            ELSE
            BEGIN
                -- Log error and rethrow
                DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
                ROLLBACK TRANSACTION;
                RAISERROR (@ErrorMessage, 16, 1);
            END
        END
        ELSE
        BEGIN
            -- Handle other errors
            ROLLBACK TRANSACTION;
            THROW;
        END
    END CATCH
END;

For application code (e.g., C#):

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=YourDatabaseName;Integrated Security=True;";
int maxRetries = 3;
int retryCount = 0;

while (retryCount <= maxRetries)
{
    try
    {
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlTransaction tran = conn.BeginTransaction())
            {
                using (SqlCommand cmd = new SqlCommand("UPDATE Orders SET OrderStatus = @Status WHERE OrderID = @OrderID", conn, tran))
                {
                    cmd.Parameters.AddWithValue("@Status", "Processed");
                    cmd.Parameters.AddWithValue("@OrderID", 1);
                    cmd.ExecuteNonQuery();
                }
                using (SqlCommand cmd = new SqlCommand("INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate) VALUES (@OrderID, @Status, @ChangeDate)", conn, tran))
                {
                    cmd.Parameters.AddWithValue("@OrderID", 1);
                    cmd.Parameters.AddWithValue("@Status", "Processed");
                    cmd.Parameters.AddWithValue("@ChangeDate", DateTime.Now);
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
                Console.WriteLine("Transaction successful!");
                break;
            }
        }
    }
    catch (SqlException ex) when (ex.Number == 1205) // Deadlock
    {
        retryCount++;
        if (retryCount <= maxRetries)
        {
            Thread.Sleep(1000); // Wait 1 second
            continue;
        }
        Console.WriteLine($"Error: {ex.Message}");
        throw;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error: {ex.Message}");
        throw;
    }
}

Action:

• Adjust @MaxRetries or delay duration based on application needs.

• Log retry attempts for monitoring.

Why? Retry logic allows the transaction to succeed if the deadlock was transient.

---

3. Optimize Transaction Scope

Minimize transaction duration and lock contention to prevent deadlocks.

Code-Oriented Solution

Reduce transaction scope by moving non-critical operations outside:

-- Original: Large transaction
BEGIN TRANSACTION;
    UPDATE Orders
    SET OrderStatus = 'Processed'
    WHERE OrderID = 1;

    -- Long-running operation
    WAITFOR DELAY '00:00:05';

    INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate)
    VALUES (1, 'Processed', GETDATE());
COMMIT TRANSACTION;

-- Optimized: Smaller transaction
UPDATE Orders
SET OrderStatus = 'Processed'
WHERE OrderID = 1;

BEGIN TRANSACTION;
    INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate)
    VALUES (1, 'Processed', GETDATE());
COMMIT TRANSACTION;

Action:

• Keep transactions short and focused.

• Avoid user input, long computations, or external calls within transactions.

Why? Long transactions increase the likelihood of lock contention, leading to deadlocks.

---

4. Use Appropriate Isolation Levels

Adjust transaction isolation levels to reduce locking.

Code-Oriented Solution

Switch to a less restrictive isolation level, like READ COMMITTED SNAPSHOT:

-- Enable READ COMMITTED SNAPSHOT
ALTER DATABASE YourDatabaseName
SET READ_COMMITTED_SNAPSHOT ON;

-- Example transaction with explicit isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    UPDATE Orders
    SET OrderStatus = 'Processed'
    WHERE OrderID = 1;

    INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate)
    VALUES (1, 'Processed', GETDATE());
COMMIT TRANSACTION;

Action:

• Use SNAPSHOT isolation for read-heavy workloads to avoid read-write conflicts:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- Your transaction here
COMMIT TRANSACTION;

• Check current isolation level:

SELECT CASE transaction_isolation_level 
    WHEN 1 THEN 'READ UNCOMMITTED'
    WHEN 2 THEN 'READ COMMITTED'
    WHEN 3 THEN 'REPEATABLE READ'
    WHEN 4 THEN 'SERIALIZABLE'
    WHEN 5 THEN 'SNAPSHOT'
    END AS IsolationLevel
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Why? Strict isolation levels (e.g., SERIALIZABLE) increase locking, raising deadlock risks.

---

5. Optimize Query Order and Indexing

Ensure consistent table access order and proper indexing to reduce lock contention.

Code-Oriented Solution

Standardize table access order in queries:

-- Transaction 1: Access Orders, then OrderHistory
BEGIN TRANSACTION;
    UPDATE Orders
    SET OrderStatus = 'Processed'
    WHERE OrderID = 1;

    INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate)
    VALUES (1, 'Processed', GETDATE());
COMMIT TRANSACTION;

-- Transaction 2: Use same order (Orders, then OrderHistory)
BEGIN TRANSACTION;
    UPDATE Orders
    SET OrderStatus = 'Shipped'
    WHERE OrderID = 2;

    INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate)
    VALUES (2, 'Shipped', GETDATE());
COMMIT TRANSACTION;

Check and create indexes to reduce lock scope:

-- Check existing indexes
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    name AS IndexName,
    type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('Orders');

-- Create index on frequently queried column
CREATE NONCLUSTERED INDEX IX_Orders_OrderID
ON Orders(OrderID);

Action:

• Ensure all transactions access tables in the same order (e.g., Orders before OrderHistory).

• Add indexes to reduce table scans, minimizing lock duration.

Why? Inconsistent access order or full table scans increase deadlock risks.

---

6. Use Lock Hints (Use with Caution)

Apply lock hints to control locking behavior.

Code-Oriented Solution

Use UPDLOCK to prevent deadlocks in specific scenarios:

BEGIN TRANSACTION;
    -- Acquire update lock early
    SELECT * FROM Orders WITH (UPDLOCK)
    WHERE OrderID = 1;

    UPDATE Orders
    SET OrderStatus = 'Processed'
    WHERE OrderID = 1;

    INSERT INTO OrderHistory (OrderID, StatusChange, ChangeDate)
    VALUES (1, 'Processed', GETDATE());
COMMIT TRANSACTION;

Action:

• Test lock hints carefully, as they can increase locking overhead.

• Monitor lock usage:

-- View current locks
SELECT 
    resource_type,
    resource_associated_entity_id,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('YourDatabaseName');

Why? Lock hints like UPDLOCK can prevent deadlocks by serializing access but may reduce concurrency.

---

7. Monitor and Analyze Deadlocks

Regularly monitor deadlocks to identify patterns.

Code-Oriented Solution

Capture deadlock graphs using SQL Server Profiler or Extended Events:

-- Create Extended Event session for deadlocks
CREATE EVENT SESSION DeadlockMonitor
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (
    SET filename = 'C:\Temp\DeadlockMonitor.xel'
);
ALTER EVENT SESSION DeadlockMonitor ON SERVER STATE = START;

Query deadlock events:

-- Read deadlock events from Extended Events
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    event_data.value('(event/data/value)[1]', 'nvarchar(max)') AS DeadlockGraph
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\Temp\DeadlockMonitor*.xel', NULL, NULL, NULL)
) AS Data;

Action:

• Analyze the deadlock graph to identify conflicting queries and resources.

• Adjust queries, indexes, or transaction logic based on findings.

Why? Monitoring helps identify recurring deadlock patterns for long-term prevention.

---

8. Test with SQLCMD or SSMS

Use SQL Server Management Studio (SSMS) or sqlcmd to simulate and debug deadlocks.

Code-Oriented Solution

Simulate a deadlock in SSMS:

-- Session 1
BEGIN TRANSACTION;
    UPDATE Orders
    SET OrderStatus = 'Processed'
    WHERE OrderID = 1;
    WAITFOR DELAY '00:00:05';
    UPDATE OrderHistory
    SET StatusChange = 'Processed'
    WHERE OrderID = 1;
COMMIT TRANSACTION;

-- Session 2 (run concurrently)
BEGIN TRANSACTION;
    UPDATE OrderHistory
    SET StatusChange = 'Shipped'
    WHERE OrderID = 1;
    WAITFOR DELAY '00:00:05';
    UPDATE Orders
    SET OrderStatus = 'Shipped'
    WHERE OrderID = 1;
COMMIT TRANSACTION;

Test with sqlcmd:

# Session 1
sqlcmd -S sqlserverhostname -U YourUserName -P YourPassword -d YourDatabaseName -Q "BEGIN TRANSACTION; UPDATE Orders SET OrderStatus = 'Processed' WHERE OrderID = 1; WAITFOR DELAY '00:00:05'; UPDATE OrderHistory SET StatusChange = 'Processed' WHERE OrderID = 1; COMMIT TRANSACTION;"

# Session 2
sqlcmd -S sqlserverhostname -U YourUserName -P YourPassword -d YourDatabaseName -Q "BEGIN TRANSACTION; UPDATE OrderHistory SET StatusChange = 'Shipped' WHERE OrderID = 1; WAITFOR DELAY '00:00:05'; UPDATE Orders SET OrderStatus = 'Shipped' WHERE OrderID = 1; COMMIT TRANSACTION;"

Action:

• Reproduce the deadlock to test fixes like retry logic or optimized queries.

• Update application code to include retry logic (see Step 2).

Why? Testing in controlled environments isolates deadlock causes and validates solutions.

---

Conclusion

SQL Server Error 1205 (Transaction Deadlock) can be resolved by implementing retry logic, optimizing transaction scope, adjusting isolation levels, and improving query order and indexing. Use the provided T-SQL and C# examples to handle and prevent deadlocks. 

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone