Bits of .NET

Getting “Transaction was deadlocked” errors randomly? Deadlocks happen when two transactions lock resources in opposite order. Here’s how to fix them permanently.

Identify Deadlocks – System Health Session:

-- SQL Server automatically logs deadlocks to system_health session
SELECT 
    CAST(target_data AS XML) AS deadlock_xml
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health'
    AND st.target_name = 'ring_buffer';

-- Extract deadlock details
WITH DeadlockData AS (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
)
SELECT 
    DeadlockGraph.query('.') AS DeadlockGraph,
    DeadlockGraph.value('(//deadlock/process-list/process/@lasttranstarted)[1]', 'datetime') AS LastTranStarted
FROM DeadlockData
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock(DeadlockGraph)
ORDER BY LastTranStarted DESC;

Common Deadlock Pattern:

-- Transaction 1:
BEGIN TRANSACTION
UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 123;  -- Locks Order 123
-- ... some processing ...
UPDATE OrderItems SET Shipped = 1 WHERE OrderId = 123;     -- Locks OrderItems for Order 123
COMMIT;

-- Transaction 2 (running simultaneously):
BEGIN TRANSACTION  
UPDATE OrderItems SET Reserved = 1 WHERE OrderId = 123;    -- Locks OrderItems for Order 123
-- ... some processing ...
UPDATE Orders SET Status = 'Reserved' WHERE OrderId = 123; -- Needs Order 123 lock → DEADLOCK!
COMMIT;

-- Result: One transaction wins, other gets "Transaction was deadlocked" error

Solution 1 – Consistent Lock Ordering:

-- Always access tables in the same order
-- Transaction 1:
BEGIN TRANSACTION
UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 123;       -- Order first
UPDATE OrderItems SET Shipped = 1 WHERE OrderId = 123;          -- OrderItems second
COMMIT;

-- Transaction 2:
BEGIN TRANSACTION  
UPDATE Orders SET Status = 'Reserved' WHERE OrderId = 123;      -- Order first (same order!)
UPDATE OrderItems SET Reserved = 1 WHERE OrderId = 123;         -- OrderItems second
COMMIT;

-- No deadlock: Both transactions wait for each other in the same sequence

Solution 2 – Use UPDLOCK Hint:

BEGIN TRANSACTION

-- Acquire update lock immediately (before SELECT)
SELECT @CurrentStatus = Status 
FROM Orders WITH (UPDLOCK, ROWLOCK) 
WHERE OrderId = 123;

-- Now safe to update (already have the lock)
IF @CurrentStatus = 'Pending'
BEGIN
    UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 123;
    UPDATE OrderItems SET Shipped = 1 WHERE OrderId = 123;
END

COMMIT;

Why UPDLOCK Works:
Normal SELECT takes shared lock → allows other SELECTs but blocks UPDATEs.
UPDLOCK takes update lock → blocks other updates AND other UPDLOCKs, preventing deadlock races.
When UPDATE executes, update lock converts to exclusive lock (no waiting, already owned).

Solution 3 – Reduce Transaction Scope:

-- BAD: Long transaction holding locks
BEGIN TRANSACTION
UPDATE Orders SET Status = 'Processing' WHERE OrderId = 123;
-- 5 seconds of business logic here...
-- Complex calculations...
-- External API calls...
UPDATE Orders SET Status = 'Complete' WHERE OrderId = 123;
COMMIT;
-- Locks held for 5+ seconds = high deadlock risk

-- GOOD: Minimize lock time
-- Do calculations outside transaction
var result = PerformComplexCalculation();
var apiResponse = await CallExternalAPI();

-- Now quick transaction
BEGIN TRANSACTION
UPDATE Orders SET Status = 'Complete', Result = @result WHERE OrderId = 123;
COMMIT;
-- Locks held for 0.01 seconds = minimal deadlock risk

Solution 4 – Use Snapshot Isolation:

-- Enable snapshot isolation (database level)
ALTER DATABASE YourDatabase 
SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE YourDatabase 
SET READ_COMMITTED_SNAPSHOT ON;

-- Now SELECTs don't block UPDATEs and vice versa
-- Each transaction sees a consistent snapshot
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION
    SELECT * FROM Orders WHERE OrderId = 123;  -- Reads don't block writes
    -- ... processing ...
    UPDATE Orders SET Status = 'Shipped' WHERE OrderId = 123;
COMMIT;

Handling Deadlocks in Application Code (C#):

public async Task UpdateOrderWithRetry(int orderId, int maxRetries = 3)
{
    for (int attempt = 0; attempt < maxRetries; attempt++) { try { using (var transaction = await _dbContext.Database.BeginTransactionAsync()) { var order = await _dbContext.Orders .FirstOrDefaultAsync(o => o.OrderId == orderId);
                    
                order.Status = "Shipped";
                
                await _dbContext.SaveChangesAsync();
                await transaction.CommitAsync();
                
                return true;
            }
        }
        catch (SqlException ex) when (ex.Number == 1205) // Deadlock error code
        {
            _logger.LogWarning($"Deadlock detected, attempt {attempt + 1}/{maxRetries}");
            
            if (attempt == maxRetries - 1)
                throw; // Give up after max retries
                
            // Exponential backoff
            await Task.Delay((int)Math.Pow(2, attempt) * 100);
        }
    }
    
    return false;
}