Transaction Retry, Deadlock, and Side-Effect Boundaries

A database can abort a transaction when concurrent work cannot satisfy its isolation guarantees or when lock acquisition forms a deadlock. The application should treat the aborted transaction as a unit: read the required state again, recompute its decision, and retry the complete set of statements under a bounded policy. Retrying only the last failed SQL statement can preserve a stale business decision. Not every database error is retryable. A uniqueness violation may represent a real conflict, and a timeout may leave uncertainty about whether an earlier operation committed unless the caller checks a stable operation record. External email, payment, and notification effects must not run inside a function that may execute twice.

Working case

Two permit reviewers update cases 47 and 62 in opposite order as they exchange assignments. One transaction locks 47 then asks for 62; the other locks 62 then asks for 47. The database cancels one to break the cycle. A naive handler retries only its second update, retaining an outdated count from the first read, while an email sent before commit reaches the contractor twice. The corrected handler locks records in a consistent order, retries the whole transaction on a recognized conflict with an attempt limit, and writes one outbox intent in the same commit. Delivery workers deduplicate that intent by operation identity.

Implementation boundary

function retryDisposition(databaseCode, attempt, maximumAttempts) {
  const retryable = databaseCode === "serialization_failure" || databaseCode === "deadlock_detected";
  return retryable && attempt < maximumAttempts ? "restart-transaction" : "stop";
}
console.log(retryDisposition("deadlock_detected", 1, 3));
// Output: restart-transaction

Choose a stable lock order when a workflow touches more than one row or resource. Keep transactions short and avoid waiting for user input, HTTP requests, or email providers while holding locks. On a serialization or deadlock result, roll back, start a fresh transaction, reread the rows, recompute the authorization and transition, and retry with a small bounded delay. Carry a stable idempotency key across attempts so a lost response after commit can return the recorded operation result. The retry wrapper must classify errors from the actual database driver; avoid a broad catch that retries constraint violations and programming mistakes. Persist outbox intents or equivalent durable work in the transaction, then send externally after commit. Document what the caller sees when the attempt budget is exhausted.

Cost and boundaries

Retrying consumes extra reads and writes, and high conflict rates can make throughput collapse under load. Consistent lock ordering reduces one deadlock pattern but cannot remove every conflict. Serializable isolation may catch anomalies that weaker isolation permits, yet conflict tracking and transaction restarts have a real cost. Measure abort rate by cause, attempts per operation, lock wait p95, transaction duration, and outbox duplicate suppression. Bound retry count and total deadline; adding unlimited retries makes a hot record appear slow rather than visibly overloaded. A backoff must fit within the caller's time budget and should not hide a persistent schema or data error.

Failure trace

Run two assignment exchanges with reversed record order and confirm a deadlock is detected or prevented, never silently committed with partial state. Force a serialization failure after a read and show that the retry rereads current permission and case status. Fail after a database commit but before the HTTP response; the same idempotency key must return the committed result without a second notification. Return a unique-constraint error unrelated to the retry policy and verify it reaches a domain conflict response. Exhaust the retry budget under a hot case and ensure the handler does not continue in a detached background loop.

Verification

Retry begins a new transaction and repeats all reads and decisions.
External effects are committed as durable intents, then delivered afterward.
Retry count and total operation time are bounded.

Practice drill

Create two transactions for reviewers 29 and 47 exchanging case owners. Make each read both cases, check current authorization, update its target, and create one outbox intent. Execute them in the opposite lock order, then again with a stable order. Inject a retryable database conflict on the first attempt and a unique-constraint conflict on another run. Count committed transitions, outbox rows, delivered messages, and retries. Prove that no notification is visible for a rolled-back transaction.

Decision note

Retry a newly evaluated transaction, never a stale fragment or an external side effect.

Common Mistakes

Retrying only the statement that raised the conflict.
Sending an email before a transaction commits.
Retrying every database error as though it were transient.

Apply and check

Build Project: permit data change under live traffic and review Web Development: database online operations quiz.