# Pathrule Pattern: Database Transactions, Deadlocks, and Concurrency (1.0.0) # ::pathrule:package:database-transactions-deadlocks ### [RULE] Place the complete database invariant in one transaction (path: /src/services) A read followed by a later write can interleave with another request even when each statement is individually correct. The transaction must cover the state on which the decision depends. - Open the transaction in the application service that owns the full business transition, not separately inside each repository method. - Read or lock the rows that determine eligibility, then re-evaluate the invariant inside the transaction immediately before writing. - Update all related records, counters, and idempotency state before commit so no observer sees a partially applied transition. - Return a typed conflict when the invariant no longer holds; do not silently overwrite newer state or convert concurrency loss into generic server failure. See /src/db for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Acquire locks in one deterministic order (path: /src/db) Deadlocks require a cycle: two transactions each hold something the other needs. Inconsistent resource order creates the cycle even when both operations are legitimate. - Sort resource identifiers before locking multiple rows and use the same ordering in every command that touches that resource set. - Document interactions between row locks, foreign-key checks, index updates, and explicit advisory locks where the engine can acquire hidden dependencies. - Do not add sleeps, network calls, user callbacks, or large computation after acquiring locks. - Treat deadlock detection as expected concurrency control: roll back the whole transaction, preserve the operation identity, and retry only through the bounded policy. See /tests/concurrency for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Keep remote effects outside retried transactions (path: /src/services) A database can roll back and retry local state, but it cannot roll back an email, payment, webhook, or message already accepted by another system. Performing the effect inside a retried callback duplicates it. - Write an outbox or equivalent durable effect record in the same transaction as the domain change. - Publish or call the external system after commit using a stable effect identity and record acknowledgement or reconciliation state. - Never call an external dependency while holding database locks merely to keep code visually inside one callback. - When the external outcome is ambiguous, query or reconcile by idempotency key before retrying rather than assuming failure. See /src/db for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Retry only classified transient concurrency failures (path: /src/db) A deadlock victim or serialization failure invalidates the transaction's snapshot and partial work. Retrying only the failed statement preserves incorrect assumptions, while retrying every database error can repeat permanent faults forever. - Classify engine-specific deadlock, serialization, and selected lock-timeout outcomes separately from validation, constraint, authentication, syntax, and capacity errors. - Discard the failed transaction and run the whole service transition again against fresh state. - Use bounded attempts with jitter and respect the request or job deadline; contention retries must not amplify an overloaded database indefinitely. - Require stable operation identity for any transition that clients, queues, or infrastructure may also retry. See /tests/concurrency for the adjacent decision or procedure that completes this constraint. --- ### [MEMORY] Isolation level is chosen per invariant and query shape (path: /src/db) Isolation names do not map to identical implementation details across databases, and stronger isolation can increase aborts or lock duration. The decision starts from the anomaly the business cannot tolerate. - Describe the concurrent interleaving that would violate the invariant and identify which reads or predicate ranges participate. - Use database constraints for facts the schema can enforce and transaction isolation or explicit locking for multi-row or conditional transitions. - Test the actual engine and access path because indexes and predicate locks can affect which concurrent changes conflict. - Record why the operation uses its isolation level and what retry behavior callers must support when the engine aborts it. See /src/services for the rule or workflow that puts this decision into practice. --- ### [MEMORY] Optimistic concurrency makes lost updates visible (path: /src/services) For detached edits and read-mostly records, holding a lock while a user or remote caller decides is impossible. A version check detects whether the state changed since it was read. - Return a version token with editable data and include it in the update predicate alongside stable identity and authorization scope. - Increment or replace the token atomically on success and check the affected row count. - On conflict, load the current state and let product policy reject, merge, or ask the user; never overwrite silently with the stale snapshot. - Keep fields with commutative operations, such as counters, on atomic update paths rather than forcing a whole-record version conflict. See /tests/concurrency for the rule or workflow that puts this decision into practice. --- ### [SKILL] investigate-database-deadlock (path: /) --- name: investigate-database-deadlock description: Investigate a database deadlock or repeated serialization failure under production concurrency. --- # Investigate Database Deadlock Run this procedure when the affected surface changes, before the result is promoted to production. Record evidence for every step instead of accepting a plausible-looking result. 1. Capture the engine deadlock graph or lock-wait evidence, transaction and statement identities, bound resource keys, timing, and application operation IDs. 2. Map each statement to its service transaction and list the order in which rows, indexes, tables, and advisory locks are acquired. 3. Identify the cycle and choose a deterministic order or narrower transaction that removes one edge without weakening the invariant. 4. Reproduce with a concurrent test that controls interleaving, then verify one transaction commits and the other retries or conflicts as designed. 5. Monitor deadlock rate, retry attempts, transaction duration, and final operation outcomes after release; preserve the graph and fix rationale beside the service. ## Exit criteria The change is complete only when the expected behavior, failure behavior, and rollback path have all been exercised with representative data. Preserve the evidence with the change so the next operator can repeat the same checks.