Skip to content

SQL Server deadlocks

Turn error 1205 into an actionable investigation.

The problem

Two or more transactions can form a cycle of competing locks. SQL Server chooses a victim, but restarting that transaction alone does not explain why the cycle formed.

Recognise the symptoms

  • Intermittent error 1205 in application logs
  • A transaction succeeds on retry but repeatedly fails at peak load
  • The same objects recur in deadlock graphs

A practical investigation workflow

  1. Collect the deadlock graph

    Use the xml_deadlock_report Extended Event, including available system_health evidence, to identify participants and resources.

  2. Read the access sequence

    Compare statements, transaction boundaries and object access order. Separate the victim from the underlying conflict.

  3. Review the smallest effective change

    Consider consistent access order, shorter transactions and appropriate indexing. Test changes against the actual graph.

  4. Check recurrence and retry behaviour

    Measure deadlock frequency and confirm the application handles rollback and bounded retries correctly.

What success looks like

A reproducible explanation of the lock cycle and fewer repeated failures.

A deadlock is different from a long blocking wait. Blanket query hints or unlimited retries can conceal the underlying problem.

Microsoft: deadlocks guide

Related workflows

SQL Server blockingDiscuss your DBA workflow