SaaS Database Retries: Recover from Serialization Failures and Deadlocks

Two customers try to reserve the final available place at almost the same moment. The database prevents an inconsistent outcome, but one request receives a concurrency error. A hurried fix retries only the final update using values read before the failure. The application now repeats a decision made from an obsolete view of the data, even though the database correctly rejected the original transaction.

Retries are part of a business workflow, not just an exception handler. A safe design identifies which failures can be retried, reruns the decision against current data, limits the effort, and keeps external actions from being repeated accidentally. This article uses PostgreSQL examples, while the review method also applies to other databases with their own documented error and transaction rules.

Classify the failure before retrying

PostgreSQL’s serialization failure guidance identifies SQLSTATE 40001 for serialization failures and 40P01 for deadlocks. It explains that serialization failures require retrying the complete transaction, including the logic that chooses statements and values. Other failures, such as unique violations, need more careful interpretation because they may represent a persistent conflict rather than a temporary concurrency condition.

Use structured error codes supplied by the driver instead of matching a translated error message. Keep the retryable-code policy close to the transaction boundary and document exceptions. A validation failure, missing permission, or duplicate business identifier should not be hidden behind repeated attempts that cannot change the outcome.

For the reservation example, a second attempt may discover that no places remain. That is a valid business result, not another infrastructure failure. Return an understandable availability response to the customer. The purpose of the retry is to obtain a correct outcome under current conditions, not force the original request to succeed at any cost.

Rerun the decision from fresh reads

Place the transaction’s reads, calculations, and writes inside a function that can be executed again. Keep values derived from database state within that function. If an available balance, remaining quantity, or current record version was read before the transaction began, decide whether it must be reread on every attempt rather than captured once in an outer variable.

Consider a request that moves a reservation between two resources. The transaction checks the source booking, destination capacity, and customer permission, then applies the change. After a concurrency failure, the next attempt should repeat those checks. It should not simply reuse the old destination capacity or assume the source booking still exists in the same state.

Rollback the failed transaction and start a fresh attempt using the driver’s documented lifecycle. Avoid reusing an aborted transaction context. Keep the operation’s stable identity, such as the customer request identifier, separate from values that need fresh evaluation. This distinction lets the application recognize one business request while recalculating its valid result.

Bound the attempts and the elapsed time

Choose a small retry budget that fits the user-facing operation and infrastructure limits. Define both a maximum attempt count and an overall deadline. A retry policy that looks modest at one layer can multiply when the HTTP client, service worker, job runner, and database wrapper all retry independently. Identify the layer that owns the concurrency retry and document the surrounding behavior.

Use a suitable delay strategy for the workload, often with variation to avoid repeatedly synchronizing competing requests. Treat any suggested delay values as parameters to test, not universal constants. Monitor whether retries are resolving occasional conflicts or merely extending an overloaded system’s response time.

When the budget is exhausted, return a clear result and a traceable request reference. Do not tell the customer the action completed unless the commit is verified. If the outcome is uncertain because the connection failed around commit, reconcile using the operation’s durable identity before repeating a non-idempotent action. An uncertain commit is a different problem from a confirmed rolled-back transaction.

Keep external effects outside repeated work

Sending email, charging a provider, or calling another service inside a retried transaction can repeat an effect that the database cannot roll back. Review every side effect in the transaction function, including apparently harmless analytics or notification calls. A database rollback does not retract an email already accepted by an external provider.

Where the workflow needs an external action after a successful change, record durable intent in the same database transaction and deliver it through a separately controlled process. Give that process its own deduplication and reconciliation rules. This approach makes the relationship between the committed record and the later external effect inspectable without claiming that distributed delivery is automatically exactly once.

For a booking confirmation, the transaction can create the reservation and its notification intent together. A retry that rolls back should leave neither committed. The delivery worker then processes the committed intent with a stable identifier. Test provider timeouts separately so uncertainty in email delivery does not cause the reservation transaction itself to be repeated.

Reduce conflicts instead of normalizing them

Retries should not hide avoidable contention. PostgreSQL’s locking documentation describes deadlocks and recommends consistent lock acquisition order as a defense. Review workflows that update the same set of records in different orders, and keep transactions as short as the business operation permits.

For example, two transfer operations may touch the same pair of records in opposite directions. A consistent ordering rule for acquiring locks can reduce the chance that each holds one record while waiting for the other. Verify that the rule applies across all code paths performing the operation, including administrative tools and background jobs.

Inspect slow queries and large batches that extend the time locks are held. Splitting work can help, but only if it preserves the required business consistency. Do not break one atomic operation into unrelated commits merely to make a lock warning disappear. First identify the invariant the transaction protects, then choose a design that retains it.

Test outcomes under controlled concurrency

Create a test that starts competing operations at a deliberate synchronization point. Verify the final business invariant, the number of committed records, and the external intents created. A test that only mocks one retryable error may check the loop while missing the stale-read bug that matters in production.

Include exhausted retries, changed permissions between attempts, and a valid business rejection after a retry. Record attempts and elapsed time in operational metrics, scoped carefully enough to avoid exposing private query values. A rising retry rate for one workflow can identify a contention problem even when most customer requests eventually succeed.

AI can summarize redacted error patterns or suggest areas to inspect, but it should not broaden the retryable-code list without engineering review. Use the database’s documented semantics and the application’s business invariants as the authority. Keep the final policy small enough that a developer can explain why each class of failure is retried or returned.

Conclusion

Safe database retries rerun a complete decision against current data, within a bounded budget. Classify errors precisely, separate stable request identity from fresh transaction state, and keep external effects accountable. Then test competing operations and inspect contention so retries remain a recovery mechanism rather than a way to conceal an incorrect workflow.