Fix SQL Server Deadlock

What's Actually Happening

SQL Server reports deadlock errors. Transactions are being killed as deadlock victims.

The Error You'll See

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Why This Happens

1. 1.Circular dependency - Two transactions waiting on each other
2. 2.Lock escalation - Row locks escalate to table locks
3. 3.Out-of-order access - Different access patterns
4. 4.Long transactions - Holding locks too long

Step 1: Capture Deadlock Graph

```sql -- Enable deadlock trace: ALTER EVENT SESSION [system_health] ON SERVER STATE = START;

-- Query deadlock info: SELECT xed.value('@timestamp', 'datetime') as CreationTime, xed.query('.') as DeadlockGraph FROM sys.dm_xe_session_targets xet INNER JOIN sys.dm_xe_sessions xes ON xes.address = xet.event_session_address CROSS APPLY xet.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]/data/value') xed(xed) WHERE xes.name = 'system_health'; ```

Step 2: Analyze Deadlock

-- Check involved processes:
-- Look for KEY LOCK and PAGE LOCK in deadlock graph
-- Identify victim and winner

Step 3: Optimize Access Order

```sql -- BAD: Different access order -- Transaction 1: UPDATE A, UPDATE B -- Transaction 2: UPDATE B, UPDATE A

-- GOOD: Same access order -- Transaction 1: UPDATE A, UPDATE B -- Transaction 2: UPDATE A, UPDATE B ```

Step 4: Add Retry Logic

DECLARE @retry INT = 3;
WHILE @retry > 0
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        -- Your queries here
        COMMIT TRANSACTION;
        SET @retry = 0;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        IF ERROR_NUMBER() = 1205
            SET @retry = @retry - 1;
        ELSE
            SET @retry = 0;
    END CATCH
END

SQL Server Deadlock Checklist

Verify the Fix

-- No deadlock errors
-- Transactions complete successfully

Related Issues

• [Fix SQL Server Connection Timeout](/articles/fix-sql-server-connection-timeout)
• [Fix SQL Server Query Slow](/articles/fix-sql-server-query-slow)