Ecommerce database transactions: isolation, deadlocks and safe retries

Treat concurrency as part of the ecommerce journey

Checkout, stock reservation, coupon redemption and payment confirmation can touch the same records within milliseconds. A transaction protects a group of operations, but it does not make every execution order correct by itself. The outcome depends on the isolation level, the access pattern and the database engine. Begin with the business invariant: quantity must not become negative, a one-use coupon must not be consumed twice, and an order must not enter incompatible states.

Write the invariant next to the transaction boundary and identify every query that can change it. Include readers that make decisions, because an apparently harmless read may observe a state allowed by the selected level but unsuitable for the workflow. The useful question is not “do we use transactions?” but “which anomalies can we accept, and what operational cost will we pay to prevent the others?”

Keep the boundary short and complete

The boundary must contain all changes that should succeed or fail together, while excluding network calls, rendering and unnecessary computation. A long transaction retains resources and can increase contention, waiting and conflicts. Prepare input before opening it, perform essential reads and writes in a consistent order, finish with commit or rollback, and return only after the outcome is known.

Choose isolation from the anomaly you must prevent

Isolation level names look portable, yet concrete behaviour differs across PostgreSQL, MySQL and SQL Server. Record the engine, version, configuration and access mode. For each flow, model two or three concurrent sequences: two carts buy the last unit, a refund meets a capture, or reconciliation reads while an order changes. Check whether the system can make decisions from inconsistent reads, lose an update or rely on a state that does not remain valid until commit.

Do not select the strongest level by habit, and do not lower it only to reduce latency. Stronger isolation may turn anomalies into transaction aborts that require retries. A weaker level may need constraints, conditional updates or carefully designed explicit locks. The choice is an application contract to prove with concurrency tests, not a single global switch.

Use constraints and atomic comparisons

Unique constraints, foreign keys and update predicates can defend invariants close to the data. An update that proceeds only when version and state still match the values read turns a race into a visible outcome. Always inspect the affected-row count and treat zero rows as a conflict rather than silent success. The exact mechanism depends on the database and must be tested against the real schema.

Separate deadlocks, blocking and timeouts

With blocking, one transaction waits for a resource held by another, and the wait may end when the holder finishes. A deadlock is a cycle in which each participant waits for a resource held by another. The database aborts a victim to break that cycle, although victim selection and diagnostics vary between engines. A timeout is different again: it says a time limit expired and does not, on its own, prove that a deadlock occurred.

Classify failures with documented driver codes and states instead of matching words in localised messages. Capture an application transaction identifier, operation, attempt, duration, phase and database code without logging sensitive values. Measure deadlocks, long waits, timeouts and serialization failures separately. Combining them into a generic “database error” hides the different corrective actions.

Reduce cycles through ordering and predictable access

When multiple flows update orders, inventory rows and payments, acquire resources in the same logical order. Keep queries selective, indexes aligned with predicates and batches bounded, because broad access can touch more records and retain resources longer. This discipline reduces the surface for cycles, but it never makes deadlocks impossible. Application code must still handle the documented database outcome.

Reproduce conflicts with tests that coordinate two sessions at defined checkpoints. Avoid relying only on random sleeps, which can pass without exercising the critical interleaving. Inspect query order, chosen indexes and execution plans, then repeat after schema or version changes. A new plan can alter the physical access order even when application statements look unchanged.

Retry the whole transaction, not the final statement

After a deadlock or serialization failure, data read earlier is no longer a reliable basis for a decision. A correct retry starts again: it opens a new transaction, rereads current data, recomputes the decision and performs every write before committing. Repeating only the failed query can combine an old decision with new state and violate the invariant the transaction was meant to protect.

Bound attempts, elapsed time and pressure

Retry only failures classified as transient. Set a maximum attempt count, an overall elapsed-time budget and backoff with random variation, then return an explicit outcome when the budget is exhausted. An infinite loop converts contention into saturation and lengthens the checkout queue. Record how many attempts are needed, and stop early when the client request has expired or the service is overloaded.

The retry wrapper should receive a complete transactional function and must not retain objects from a previous attempt. Each iteration recreates the connection or context as required by the driver, applies the same limits and produces one result. Test budget exhaustion too: the caller must know whether to invite another action, enqueue work or show a recoverable error.

Protect effects that rollback cannot undo

A database rollback does not withdraw an email, an API request to a payment provider, a published message or a written file. If these effects occur inside code that may run again, every attempt can duplicate them. Store the intent in the database, use a stable idempotency identifier and send after commit through a reliable process. When an external call cannot be moved, apply that service’s idempotency contract and retain the mapping between request and outcome.

Distinguish failure before commit, successful commit with a lost response, and failure of a later effect. They are different states that require reconciliation, not indiscriminate retry. A transactional outbox can store the business change and intent to publish in one transaction, but delivery may still be at least once and consumers must tolerate duplicates.

Observe and release without invented benchmarks

Establish a baseline for your workload: completed-transaction rate, failures by class, typical and high attempt counts, wait time, total duration and operations that consume the retry budget. Link telemetry to application version, query fingerprint and plan without exposing personal values. More retries may reflect legitimate traffic growth, a new access order or an index that no longer fits. Correlation matters more than a universal threshold.

Release one change at a time and expose it to a controlled share of traffic. Prepare rollback for code, schema and configuration, and preserve a deterministic concurrency test in CI. To design transaction boundaries, observability and recovery around your ecommerce system, explore our digital systems and ecommerce services. The aim is not to pretend conflicts disappear; it is to make them expected, bounded and verifiable.