SQL Server: Transaction was deadlocked (Msg 1205)

Quick fix

Read the commands before running them. Anything that restarts a service, deletes data or changes permissions should be tried on a non-production system first.

-- Capture deadlock graph
CREATE EVENT SESSION deadlocks ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename='deadlocks.xel');
ALTER EVENT SESSION deadlocks ON SERVER STATE = START;
-- Always retry error 1205 with backoff
-- Use consistent table access order and keep transactions short

How to diagnose Database errors

Database errors group into connection exhaustion, lock contention and deadlocks, constraint violations, and resource limits. Connection errors under load are the most misdiagnosed: "too many connections" is almost never fixed by raising max_connections, because each connection costs memory. It is fixed by putting a pooler in front and finding the code path that leaks connections.

If the quick fix above does not resolve it, work through these steps. They apply to this whole class of error, not just to this one message, which is usually what saves the time.

1. Look at what the server currently sees: SELECT * FROM pg_stat_activity in PostgreSQL, SHOW FULL PROCESSLIST in MySQL. Sessions sitting in idle in transaction are the usual culprit behind connection exhaustion.
2. For deadlocks, read the deadlock report the database logs. It names both transactions and the exact lock order. The fix is nearly always to make all code paths acquire locks in the same order.
3. Check whether the error is from the database or from a pooler. PgBouncer, ProxySQL and RDS Proxy return their own errors that look database-native but need pooler-side fixes.
4. Run EXPLAIN (ANALYZE, BUFFERS) on slow queries. Lock-wait timeouts are often just very slow queries holding locks longer than they should.
5. Verify you are connected to the writer, not a read replica. READONLY and "cannot execute in a read-only transaction" errors mean traffic is reaching the wrong endpoint.

Tools worth reaching for

• pg_stat_activity
• SHOW ENGINE INNODB STATUS
• EXPLAIN ANALYZE
• slow query log
• pgbadger

Authoritative references

Primary documentation for this error, worth reading before applying any fix in production.

📖 learn.microsoft.com

Related Database errors

• Cassandra: NoHostAvailableThe driver could not reach any node: wrong contact points, the node is down, a…
• ClickHouse: Memory limit (total) exceededA query tried to use more memory than max_memory_usage (or the server total) allows, common…
• CockroachDB: restart transaction (SQLSTATE 40001)A SerialisABLE transaction conflicted and must be retried. Contended transactions can be…
• DynamoDB: ProvisionedThroughputExceededExceptionThe table or a hot partition exceeded its read/write capacity. A skewed partition key can…
• Elasticsearch: flood stage disk watermark exceededDisk usage >95%. Node effectively read-only to prevent data corruption. Requires manual reset…
• Elasticsearch: mapper_parsing_exception, failed to parse fieldA document's value does not fit the field's mapped type. Dynamic mapping fixes a type from…
• Elasticsearch: Shard allocation failedCannot allocate shards to nodes. Disk space low, node disconnected, or allocation settings…
• MongoDB: E11000 duplicate key errorAn insert or upsert violated a unique index. Common during retries, imports, or migrations…

See all 41 Database errors →

Browse other categories

• HTTP 494xx client errors, 5xx server errors, redirects, headers and protocol problems.
• JavaScript 42npm resolution, async pitfalls, hydration, memory limits and runtime type…
• AI 35Rate limits, context windows, GPU memory and model-serving failures.
• Network 35Refused connections, timeouts, resets, MTU problems and port exhaustion.
• Python 35Imports, virtual environments, encoding, concurrency and dependency conflicts.
• Kubernetes 34CrashLoopBackOff, ImagePullBackOff, OOMKilled, RBAC, scheduling and storage.
• Docker 27Daemon connectivity, disk space, image pulls, ports and architecture mismatches.
• System 26Disk space, systemd units, file descriptors, OOM killer and scheduled jobs.
• Cloud 25IAM permissions, quotas, service limits and credential failures.
• Security 25JWT validation, CSRF, OAuth grants, SELinux, SSH host keys and CSP.
• TLS 24Untrusted authorities, expiry, hostname mismatch, chains and cipher negotiation.
• Frontend 23Hydration mismatches, bundler resolution, layout shift and font loading.

Something missing or wrong?

This entry is maintained by hand. If the fix is out of date, incomplete, or you have a better one, email a correction and it will be reviewed.