Inventory and orders update at the same time and occasionally report Deadlock found—can increasing retry counts alone suppress it?

When a database occasionally reports Deadlock found, simply increasing the retry count usually only suppresses the error for a period of time; it does not guarantee that the root cause is fixed. Based on common delivery experience in 2026, the more common causes of deadlocks are the scope of locks held by transactions, index usage, or inconsistent access order across multiple entry points. Retries are only a fallback for replayable business operations. First obtain the deadlock log to reconstruct the locking path, then decide whether to unify the order, shrink the transaction, add indexes to reduce lock scope, or add idempotent retries.

Where exactly do deadlocks get stuck, and why is changing statement order alone not enough?

A deadlock occurs when two or more transactions each hold some row locks and wait for the other to release theirs, forming a circular wait. After the database detects this, it actively rolls back one of the transactions. The difference from ordinary lock waiting is: with lock waiting, as long as the holder commits or rolls back, the other side can continue; with a deadlock, if there is no active intervention, they will block each other. Therefore, focusing only on the surface order of SQL can easily miss more common causes such as missing indexes that expand lock scope and remote calls embedded inside transactions.

• Circular wait: Transaction A holds row 1 and waits for row 2, while Transaction B holds row 2 and waits for row 1; neither releases.
• Expanded lock scope: If the where condition does not use an index, row locks may escalate to a larger lock scope, increasing the chance of collisions.
• Overlong transactions: Remote calls, batch operations, and file reads/writes inside a transaction extend lock holding time.
• Isolation level effects: Under repeatable read, gap locks additionally lock ranges, and behavior differs across databases.
• Concurrent multi-entry updates: Online requests, scheduled jobs, and message consumption update the same batch of rows at the same time, making access order difficult to keep naturally consistent.

In real systems, deadlocks are often not caused by a single reason but by the combination of 'no index + long transaction + multiple entry points.' Changing the order of two statements may happen to cover one fixed reverse-access scenario, but once the entry point or data distribution changes, it can reappear.

Which evidence should you review before deciding how to fix it?

When troubleshooting deadlocks, do not change code first; collect evidence first. Databases generally output the SQL of the two transactions and the held-lock and waiting-lock information in the error log or deadlock log. Align logs from the same time period by transaction ID to reconstruct who locked which row first. If logs are unavailable, cross-reference application log timestamps, slow queries, and transaction boundaries; check the specific log fields against the official documentation of the database you use.

1. Enable deadlock logging: Confirm whether the database outputs details of the most recent deadlock; a common practice is to retain the last several occurrences for comparison during reproduction.
2. Reconstruct the locking order: Arrange the SQL from the two transactions by execution time and mark which rows each holds and waits for.
3. Verify index usage: Use the execution plan to see whether the where condition uses an index. If it does not, the lock scope can exceed expectations.
4. Check transaction boundaries: Confirm whether the transaction includes remote calls, message sending, batch loops, or other operations that prolong lock holding time.
5. Record reproduction conditions: Write down concurrent entry points, data volume, isolation level, and lock wait timeout, so you do not judge by feel after changes.

The order of these five steps cannot be reversed. Get the logs first, then determine the order, and only then discuss fixes. If you change SQL order based on impression alone, it may look fine in a low-concurrency test environment but reappear after release with a different entry point. In 2026, many teams have already added deadlock log retention and slow query sampling to their release checklists; the cost is low, but it saves a great deal of time spent guessing later.

Verifiable comparison of several handling approaches

Different approaches solve problems at different levels and should not be mixed blindly. Unifying access order suits scenarios where two business entry points consistently update the same batch of rows; shrinking transactions suits scenarios where non-database operations are mixed into transactions; adding indexes suits scenarios where the where condition does not hit an index and the lock scope is too large; retries are more of a fallback, not a root-cause fix. Below is a verifiable comparison based on common delivery experience; actual timelines vary with code structure and testing conditions.

• Unify access order: Low modification cost; typical range is half a day to two days. Effective for fixed dual-entry updates, but of limited use for dynamic batch updates.
• Shrink transactions: Medium modification cost; typical range is one to two days to a week. Remote calls and message sending need to be moved out of the transaction; the benefit is a clear reduction in lock holding time.
• Add indexes to reduce lock scope: Low to medium cost; experience range is half a day to three days. Check the execution plan and lock wait logs first, then decide whether to add a composite index or adjust conditions.
• Lock timeout and idempotent retries: Low modification cost; typical range is a few hours to one day. Suitable for replayable and idempotent business operations, but it cannot replace root-cause fixes.
• Lower the isolation level: Low configuration adjustment cost, but first verify whether the business depends on repeatable read semantics; the impact may be broader than expected.

The question is not which approach is more advanced, but whether it covers the root cause. If the root cause is not using an index, changing only the order will most likely still recur; if the root cause is inconsistent entry order, adding only retries merely hides the error. A verifiable approach is: after the change, run a load test in the pre-release environment with similar concurrency and observe whether the deadlock log still shows the same type of circular wait.

Applicability and non-applicability boundaries: which systems need prevention, and which do not

Deadlock handling suits scenarios with high-frequency writes, the same batch of rows updated by multiple entry points, and batch jobs concurrent with online requests, such as order status transitions, inventory deduction, and account balance changes. If a system is read-heavy and write-light, updates a single row, and has extremely short transactions, the probability of deadlock itself is low, and there is no need to introduce a complex retry framework. Two numbers can help judge the boundary: the number of updates to the same row per unit time and the average lock holding time per transaction.

• Suitable: Multiple entry points updating the same row, batch updates concurrent with online writes, multiple write operations inside a transaction, and range updates.
• No need for over-engineering: Internal systems that are almost entirely inserts, single-row updates by primary key, transactions with only one SQL statement, and very low write concurrency.
• Need focused verification: Range updates under repeatable read isolation, where conditions without an index, nested transactions, and remote calls inside transactions.
• Not suitable: Treating deadlocks as a pure code bug and repeatedly changing SQL order or adding unlimited retries without checking indexes and transaction boundaries.

The non-applicability boundaries here must be stated clearly: if the business does not allow repeated execution, or retries would cause duplicate deductions or duplicate shipments, you cannot rely only on retries as a fallback; prioritize fixing the root cause. Retry counts and lock wait timeouts are also not better when larger; if set too long, they may drag API response times to the point of user-side timeouts. The typical range should be adjusted according to business tolerance and database load, not copied from some fixed value.

Delivery field experience: constraints, approach, and cost

In one actual delivery in 2026, the constraints were that only one week remained in the schedule and there was no capacity to run lock stress tests for every write API. In the system, two entry points—order status and inventory deduction—would update the same batch of rows at the same time, occasionally reporting Deadlock found. The approach was to first add execution plan verification and deadlock log retention for high-frequency write paths, unify the update order of the two entry points to the same field order, move external calls out of the transaction, and add idempotent retries for replayable write operations. The result was that deadlock errors dropped from several per day to occasional ones, and API timeout complaints also decreased. The cost was that low-frequency batch job paths could still occasionally fail, requiring monitoring alerts to fill the gap; it was impossible to cover all paths. The experience range is: high-frequency write paths can be stabilized in one to three days, while complete governance often takes one to two weeks, depending on the number of entry points and historical transaction patterns.

Common pitfalls in the field also include: only adding retries to the API that reports the error while deadlock logging is not enabled, so the next recurrence still cannot be diagnosed; assuming a row lock locks only one row when the where condition field has no index, while the actual range lock expands; and calling third-party APIs inside a transaction, where third-party timeouts multiply transaction lock holding time. The acceptable bar can be verified by four items: high-frequency write paths have execution plan verification, transaction boundaries are clear, deadlock logs are queryable, and retries have idempotent fallbacks.

• Counterexample 1: Only adding retries to the API that reports the error while deadlock logging is not enabled, so the next recurrence still cannot be diagnosed.
• Counterexample 2: The where condition field has no index, and you assume a row lock locks only one row, while the actual range lock expands.
• Counterexample 3: Calling third-party APIs inside a transaction; a third-party timeout multiplies transaction lock holding time.
• Acceptable bar: High-frequency write paths have execution plan verification, transaction boundaries are clear, deadlock logs are queryable, and retries have idempotent fallbacks.

Frequently asked questions

When the database reports Deadlock found, if retrying a few times stops the error, does that mean it is fine?

An occasional deadlock may succeed on retry, but retries are only a fallback. If you do not check indexes and transaction boundaries, it will recur once traffic increases, and the problem may be masked as occasional timeouts.

Why does swapping the order of two update statements sometimes work and sometimes not?

Swapping order is effective only when two transactions consistently access the same batch of rows in reverse order. If the root cause is not using an index or an overlong transaction, swapping order will not change the lock scope.

If the deadlock log contains only one SQL statement, can the problem be diagnosed?

Not enough. You need the held-lock and waiting-lock information from both transactions, combined with execution plans and transaction boundaries, to reconstruct the circular wait path.

Can lowering the isolation level reduce deadlocks?

Changing from repeatable read to read committed can reduce gap locks, and this common practice can lower some deadlocks, but first confirm that the business does not depend on repeatable read semantics.

Can a single update by primary key also deadlock?

The probability is low, but it can still happen when the same primary key is updated by two long transactions in an interleaved way, or when the transaction also contains other write operations. It cannot be completely ruled out.

If you are dealing with occasional deadlocks, first confirm whether deadlock logs are queryable, then pick one high-frequency write path to verify the execution plan and transaction boundaries. Deadlock retries suit replayable and idempotent business operations; if the business does not allow repeated execution, prioritize fixing the root cause rather than adding retries. Internal systems that are read-heavy, write-light, and have extremely short transactions do not need a complex framework for this; judge by actual write concurrency. In 2026, you can first complete the evidence chain for high-frequency write paths, then decide how much modification to invest.