SQL Server Deadlocks: Causes, the Deadlock Victim & How to Fix Them

Deadlocks feel mysterious because the error appears on a query that did nothing wrong. The key is that a deadlock is about two transactions and the order in which they take locks — and most are preventable.

Deadlock vs. blocking

Blocking is one-directional; a deadlock is a cycle. In blocking, session A holds a lock and session B waits for it — B proceeds once A is done. In a deadlock, A holds a lock B needs and B holds a lock A needs, so waiting will never resolve it. SQL Server's deadlock monitor detects the cycle and forcibly ends one transaction.

The deadlock victim and error 1205

When the engine detects a deadlock, it picks a deadlock victim, terminates its batch, rolls back that transaction, and returns error 1205 to the application. By default the victim is the transaction estimated to be cheapest to roll back. You can bias the choice with SET DEADLOCK_PRIORITY — the session with the lowest priority is chosen first.

Common deadlock types

• Writer-writer — two transactions try to modify the same resource; both need incompatible exclusive locks.
• Reader-writer — one holds a shared lock from a read while another needs an exclusive lock to modify the same resource.
• Parallelism — threads within a single parallel plan block each other.

How to prevent and handle deadlocks

Most deadlocks are preventable with a handful of disciplines:

• Keep transactions short so locks are held only briefly.
• Access objects in a consistent order across all code paths, so two transactions can't grab the same resources in opposite sequences.
• Add a covering index to reduce the lock footprint — touching fewer rows means fewer chances to conflict.
• For parallelism deadlocks, setting the query's MAXDOP to 1 forces a serial plan.
• On the application side, implement retry logic with TRY...CATCH to re-run a transaction that hit error 1205.

Application-side retry on deadlock (error 1205)BEGIN TRY
    BEGIN TRAN;
        -- ... your statements ...
    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1205   -- chosen as deadlock victim
    BEGIN
        IF XACT_STATE() <> 0 ROLLBACK TRAN;
        -- wait briefly, then retry the transaction
    END
    ELSE THROW;
END CATCH;

The optimization connection

Notice how many deadlock fixes are also performance fixes: shorter transactions, covering indexes, touching fewer rows. A leaner, faster query holds fewer locks for less time, which shrinks the window in which a deadlock cycle can form. SprocOptimizer does not set deadlock priorities or add retry logic — that stays in your application and transaction design — but by making procedures faster and recommending covering indexes, it reduces the lock footprint that makes deadlocks likely. Every such rewrite is validated for identical results before promotion.