The trap: treating deadlocks like “random database errors”
A deadlock is not random.
It can feel random during load testing because it appears only under pressure, only with certain request mixes, and often disappears when one engineer tries to reproduce it alone. But at the database level, a deadlock is a deterministic outcome: two or more transactions acquire locks in conflicting order, and the database has to choose one transaction as the victim so the system can keep moving.
That distinction matters.
If a team treats deadlocks as random database errors, the usual response is to increase timeouts, add retries, or assume the database is overloaded. Those changes may reduce visible failures for a while, but they rarely fix the underlying contention pattern.
A better approach is to treat every deadlock as evidence.
It is evidence that two parts of the system are touching the same resources under concurrency in a way that cannot safely complete. The job is to find that collision, understand why it happens, and remove or control it.
Why deadlocks show up during load testing
Deadlocks often appear during load testing because load tests reveal timing paths that normal manual testing never exercises.
A single user may never create contention. Ten users may not either. But when many users execute overlapping workflows, the database starts seeing patterns that were always possible but rarely simultaneous.
Under load, deadlocks can expose:
- inconsistent access patterns across code paths
- missing or suboptimal indexes
- overly large transaction scopes
- background jobs colliding with user traffic
- hot rows or hot summary tables
- updates that scan too many rows
- different services writing related data in different orders
- long-running reads blocking writes, depending on isolation behavior
The deadlock is the symptom. The bottleneck is usually the access pattern behind it.
The first mistake: looking only at the victim query
When a database reports a deadlock, it usually identifies a victim transaction. That is the transaction the database chose to terminate.
It is tempting to start by optimizing only the victim query.
But the victim is not necessarily the root cause. It is simply the transaction selected to lose the deadlock. The “winner” query may be just as important. The shared resource may be more important than either query alone.
A deadlock investigation should start with the full cycle:
- which statements were involved
- which locks each statement held
- which locks each statement wanted
- which object, index, key, or range was shared
- which transaction was chosen as victim
- which transaction continued
- what application workflow triggered each side
Until the full cycle is visible, fixes are mostly guesses.
What to capture during the incident
The best time to collect deadlock evidence is while the system is still under the same load pattern.
After the test stops, the database may look healthy again. Blocking clears. queues drain. CPU drops. The deadlock graph remains useful, but the surrounding system context may be gone.
During the incident, capture three types of evidence.
1. A reproducible concurrency scenario
Start with the workload itself.
Document:
- number of concurrent users or virtual users
- ramp-up pattern
- request mix
- endpoints or workflows involved
- payload sizes
- tenant/project/customer context
- hot entities being updated
- test duration
- time when deadlocks started
- whether failures appeared immediately or after saturation
This matters because deadlocks are often workload-specific.
A system may handle 100 users doing read-heavy workflows but fail with 20 users performing the same write-heavy operation against the same entity. Without the request mix, “100 users caused deadlocks” is not specific enough.
The more precise statement is usually something like:
Deadlocks appeared when concurrent users executed workflow A and background job B against the same well/project summary records during the ramp-up period.
That is a much more useful starting point.
2. Database evidence
Next, capture what the database saw.
Useful database evidence includes:
- deadlock graphs or equivalent reports
- victim and survivor statements
- locked object names
- index names
- key or range lock details
- lock modes
- wait types
- wait durations
- execution plans for involved statements
- missing index recommendations, treated carefully
- transaction isolation level
- active blocking sessions around the same time
The deadlock graph is especially important because it shows the cycle. Without it, teams often debate based on symptoms.
Execution plans matter because deadlocks are not only caused by writes. A query that scans too much data can hold locks longer or touch a wider key range than expected. Under low load, that may be invisible. Under concurrent write load, it becomes a problem.
3. Application context
Database evidence tells you what collided. Application context tells you why.
Capture:
- correlation IDs or trace IDs
- user workflow that triggered each query
- service and endpoint names
- transaction boundaries
- retry behavior
- request payload shape, sanitized
- background job activity
- timestamps from application logs
- whether the same entity was targeted repeatedly
- whether the workflow used cached or stale state
Application context connects the database cycle back to product behavior.
For example, two queries may deadlock because two services update the same tables in opposite order. The database graph shows the conflict. Application traces show which workflows created that order.
The investigation flow
Once evidence is collected, avoid jumping straight to fixes. Work through the deadlock systematically.
Step 1: Identify the deadlock cycle
Find the minimal set of statements involved.
Ask:
- Which statement held lock A and wanted lock B?
- Which statement held lock B and wanted lock A?
- Were more than two transactions involved?
- Which indexes or key ranges were involved?
- Which transaction was selected as the victim?
The goal is to reduce the incident from “the database is deadlocking” to a specific cycle.
Deadlock debugging becomes much easier when the cycle can be described in one sentence.
For example:
Transaction 1 updates the detail table and then the summary table, while Transaction 2 updates the summary table and then the detail table for the same entity.
That sentence immediately suggests a fix: consistent lock acquisition order.
Step 2: Determine the shared resource
Next, identify the resource where concurrency collided.
It might be:
- one hot row
- a range of rows
- an index key
- a summary table
- a parent entity row
- a queue/status table
- a metadata table
- a missing index that forces a scan
- a foreign-key relationship touched by multiple paths
The shared resource is important because it determines the fix.
A hot row may need serialization, queuing, or redesign. A range lock may need index changes or isolation review. A scan may need a targeted index. A shared summary table may need narrower updates or delayed aggregation.
Step 3: Ask why the collision happened
A deadlock is not only a database event. It is usually an application design signal.
Ask:
- Why are these workflows touching the same data at the same time?
- Do they acquire locks in different orders?
- Is the transaction doing too much work?
- Is the query scanning when it should seek?
- Is a background job competing with user traffic?
- Is the same entity being updated too frequently?
- Are retries amplifying contention?
- Is the isolation level appropriate for this workload?
The best fixes come from understanding the cause of the collision, not just the fact that a collision occurred.
Step 4: Fix in order of leverage
Not all fixes are equal.
A good order of investigation is:
- Reduce transaction scope.
- Make access order consistent.
- Add or adjust indexes to avoid scans under write load.
- Narrow updates so fewer rows are touched.
- Separate background jobs from hot user paths.
- Add queueing or throttling for known hot operations.
- Review isolation-level implications carefully.
- Add retries with backoff only after the root contention is understood.
This order matters because some fixes hide the issue instead of solving it.
For example, increasing a timeout may reduce deadlock frequency, but it can also make lock waits longer and user experience worse. Blind retries may make the system appear more successful while increasing pressure on the same hot resources.
Fix pattern 1: consistent lock acquisition ordering
One of the strongest deadlock fixes is consistent ordering.
If two code paths update the same set of tables or rows, they should acquire locks in the same order. When one path updates A then B and another updates B then A, deadlocks become much more likely under concurrency.
Consistent ordering prevents circular waits.
This may require changing application code, stored procedures, repository methods, or service orchestration so related updates follow the same sequence.
The key is to define the order deliberately and apply it everywhere.
Fix pattern 2: reduce transaction scope
Large transactions increase the time locks are held.
A transaction should include only the work that must be atomic. If it includes slow API calls, expensive computation, unnecessary reads, or unrelated updates, it creates more opportunities for contention.
Review transaction boundaries carefully:
- Where does the transaction start?
- Where does it commit?
- Are external calls happening inside it?
- Are reads included that could happen before the transaction?
- Are multiple entities updated when only one is required?
- Is the UI workflow causing a transaction to stay open longer than expected?
Shorter transactions do not guarantee no deadlocks, but they reduce the window where deadlocks can form.
Fix pattern 3: index for the write workload, not just reads
Indexes are often discussed as a read-performance tool, but they also affect locking behavior.
A missing or poor index can cause a statement to scan more rows than necessary. Under write load, that wider scan can take more locks, hold locks longer, or conflict with other transactions across a larger range.
When investigating deadlocks, inspect the execution plans for the involved statements.
Look for:
- table scans on hot paths
- index scans where seeks are expected
- key lookups repeated many times
- wide range scans
- predicates not supported by indexes
- updates filtering on non-indexed columns
A targeted nonclustered index can sometimes remove a deadlock pattern by narrowing the touched rows.
But indexes should be added carefully. Extra indexes can slow writes and create additional maintenance cost. The goal is not “add all missing indexes.” The goal is to support the exact access pattern causing contention.
Fix pattern 4: narrow the update
Some updates touch more data than they need.
An update that recalculates a summary, rewrites a whole object, or updates rows by broad criteria can create unnecessary lock pressure.
A narrower update asks:
- Can this update target one row instead of a range?
- Can unchanged fields be left alone?
- Can the workflow update only the affected child records?
- Can summary updates be deferred or batched?
- Can the system avoid touching a shared parent row on every child change?
The fewer rows a transaction touches, the fewer chances it has to collide.
Fix pattern 5: separate background work from user traffic
Deadlocks often happen when background jobs and user workflows touch the same tables at the same time.
The background job may be correct in isolation. The user workflow may be correct in isolation. Together, they collide.
Possible fixes include:
- schedule background jobs outside load-heavy windows
- reduce job batch size
- process hot entities sequentially
- use queueing for shared resources
- split read-heavy aggregation from write-heavy user paths
- add guards so jobs skip records currently being modified
- use lower-impact queries for monitoring or cleanup jobs
The right fix depends on the workload, but the principle is the same: background work should not unknowingly compete with critical user paths.
What about retries?
Retries can be useful for deadlocks because many databases treat deadlock victim errors as retryable. The losing transaction can often succeed if run again.
But retries should be controlled.
A good retry strategy should include:
- maximum attempt count
- exponential backoff or jitter
- logging of the original failure
- idempotency checks
- correlation with the original request
- visibility into retry frequency
- a final clear failure if retries are exhausted
Retries without backoff can amplify contention. If many requests deadlock and immediately retry, they may recreate the same collision pattern again and again.
Retries are a safety net, not a substitute for understanding the deadlock.
What not to do
Some fixes are tempting but risky.
“Just increase the timeout”
Increasing timeouts may reduce visible errors, but it can make users wait longer and keep locks alive for more time. If the root issue is contention, longer timeouts can make the system feel slower without making it healthier.
“Disable locking protections”
Changing isolation levels or locking hints without understanding the side effects can introduce data consistency problems.
Isolation settings exist for a reason. They should be reviewed with a clear understanding of read consistency, phantom reads, write conflicts, and business correctness.
“Add retries everywhere”
Retries can hide failures and increase database pressure. They should be targeted, bounded, and observable.
“Optimize the biggest query only”
The biggest query is not always the deadlock cause. The deadlock cycle, shared resource, and lock order matter more than query size alone.
How to validate the fix
A deadlock fix should be validated under the workload that produced the issue.
Validation should include:
- same or higher concurrency
- same request mix
- same hot entities, where safe
- same background jobs
- same database size or realistic data distribution
- monitoring of deadlock count
- lock wait time
- request latency
- error rate
- retry count
- CPU and IO behavior
- user-visible success rate
A fix that works only in a simplified scenario may not hold under real load.
Also watch for tradeoffs. An index might reduce deadlocks but increase write latency. A queue might reduce contention but increase processing delay. A retry might improve success rate but increase tail latency.
The best fix improves the system’s behavior under load, not only the deadlock metric.
A practical deadlock playbook
When deadlocks appear during a load test, I use this sequence:
- Capture the workload: concurrency, request mix, hot entities, and timing.
- Collect the deadlock graph or equivalent database evidence.
- Identify the victim and survivor statements.
- Find the shared table, index, key, or range.
- Map each statement back to an application workflow.
- Identify transaction boundaries.
- Check whether lock acquisition order differs across code paths.
- Inspect execution plans for scans or wide ranges.
- Look for background jobs touching the same resources.
- Reduce transaction scope where possible.
- Add targeted indexes only when they support the observed access pattern.
- Add bounded retries with backoff only after understanding the contention.
- Re-run the same load scenario and compare deadlocks, latency, and retries.
This playbook turns deadlocks from mysterious database noise into an actionable engineering investigation.
Closing
The goal is not “no deadlocks ever.”
In a highly concurrent system, some contention may always be possible. The real goal is predictable behavior under load: fewer avoidable collisions, shorter lock waits, bounded retries, useful logs, and graceful user-facing failure when contention cannot be avoided.
A deadlock is not random. It is a map of a conflict.
If you collect the right evidence — workload, database graph, application trace, transaction boundaries, and execution plans — the conflict usually becomes explainable. Once it is explainable, the fix becomes much clearer.
Deadlocks are not just database errors. They are signals about how your system behaves under pressure. Treat them that way, and they become one of the most useful findings a load test can give you.