Diagnose contention and uncertain booking outcomes

Understand the likely causes

• Long transactions hold resources while waiting for unrelated work. Different acquisition orders create deadlock cycles. A lost network acknowledgment can hide a successfully committed operation, so transport failure alone does not establish database rollback.

Preserve request identity before deciding on replay

Capture the request key, affected shipment identifiers, exact error, transaction stage and approved timestamps. Keep secrets and customer payloads out of shared diagnostics. Determine whether the application knows the transaction failed or merely lost knowledge of its outcome. A failure before a statement, a lock rejection and a dropped commit response are not equivalent. Use the persisted unique request key to reconcile a known business operation through an authorized path before allowing another submission.

Inspect the stored state transition

If capacity is read and later replaced with an absolute value, concurrent callers can act on stale state. The illustrative conditional update makes eligibility and decrement one statement; the caller must inspect exactly one affected row and commit it with the booking. Zero affected rows should map to a documented rejection rather than an unconditional success. This does not remove the need for transaction boundaries, a nonnegative constraint or request-key uniqueness.

UPDATE capacity SET free_units = free_units - :units
WHERE slot_id = :slot_id AND :units > 0 AND free_units >= :units;

Distinguish a waiting edge from a cycle

An ordinary waiter may proceed when the owner completes. A deadlock requires a cycle such as A holding 7 and waiting for 8 while B holds 8 and waits for 7. Preserve the approved deadlock diagnostics and compare the business resource order, including other tables and constraints. Oracle’s reported deadlock does not justify assuming the whole application unit was undone. Explicitly roll back or recover the intended unit according to the interface policy. Increasing all timeouts does not repair an opposite-order protocol.

A: holds slot 7 -> requests slot 8
B: holds slot 8 -> requests slot 7
reviewed order: every worker requests 7 before 8

Resolve uncertainty across the client boundary

When connection loss occurs after COMMIT is sent, query the unique request identity through the approved reconciliation path. If the booking exists, return its durable outcome rather than replaying the insert. If the result remains unknown, retain a pending state and escalate through the documented recovery workflow. Email or carrier notifications need coordinated delivery, commonly through an outbox, because a database rollback cannot retract an external message. Connection-pool cleanup must also terminate pending transactions before a session is reused.

Keep it from recurring

• Keep transactions short, acquire shared resources consistently, set reviewed wait and retry policies and persist unique request keys. Review pool reset and outbox behavior alongside database code rather than treating retries as a purely network concern.