Database Transactions, Deadlocks, and Concurrency

Pathrule4 Rules • 2 Memories • 1 Skill

Concurrent requests can each pass a read check and still violate an invariant, while longer transactions, inconsistent lock order, and automatic retries can turn ordinary contention into deadlocks or duplicate external effects. This pattern constrains transaction boundaries, locking, retry safety, and external effects; it records isolation and optimistic-concurrency decisions and supplies a deadlock investigation workflow. It complements schema and query-performance patterns by focusing on interleavings and correctness under concurrency rather than table design or individual statement latency.

Suggested path map

Pathrule places each piece on the matching path, so your assistant only sees it where it belongs. This is the scoping you get on import; you can adjust it in your workspace.

/ workspace root

investigate-database-deadlock

src/

services/

Place the complete database invariant in one transaction

Keep remote effects outside retried transactions

Optimistic concurrency makes lost updates visible

db/

Acquire locks in one deterministic order

Retry only classified transient concurrency failures

Isolation level is chosen per invariant and query shape

Rules

Place the complete database invariant in one transaction/src/serviceshighstrictRead the deciding state, enforce the condition, write every related row, and commit as one explicit service transition.

1	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.
2
3	- Open the transaction in the application service that owns the full business transition, not separately inside each repository method.
4	- Read or lock the rows that determine eligibility, then re-evaluate the invariant inside the transaction immediately before writing.
5	- Update all related records, counters, and idempotency state before commit so no observer sees a partially applied transition.
6	- Return a typed conflict when the invariant no longer holds; do not silently overwrite newer state or convert concurrency loss into generic server failure.
7
8	See /src/db for the adjacent decision or procedure that completes this constraint.

Acquire locks in one deterministic order/src/dbhighstrictDefine row, table, and advisory lock order across code paths and keep locked work minimal to reduce cycles.

1	Deadlocks require a cycle: two transactions each hold something the other needs. Inconsistent resource order creates the cycle even when both operations are legitimate.
2
3	- Sort resource identifiers before locking multiple rows and use the same ordering in every command that touches that resource set.
4	- Document interactions between row locks, foreign-key checks, index updates, and explicit advisory locks where the engine can acquire hidden dependencies.
5	- Do not add sleeps, network calls, user callbacks, or large computation after acquiring locks.
6	- Treat deadlock detection as expected concurrency control: roll back the whole transaction, preserve the operation identity, and retry only through the bounded policy.
7
8	See /tests/concurrency for the adjacent decision or procedure that completes this constraint.

Keep remote effects outside retried transactions/src/serviceshighstrictRepresent intended external work durably in the transaction, then deliver it idempotently after commit.

1	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.
2
3	- Write an outbox or equivalent durable effect record in the same transaction as the domain change.
4	- Publish or call the external system after commit using a stable effect identity and record acknowledgement or reconciliation state.
5	- Never call an external dependency while holding database locks merely to keep code visually inside one callback.
6	- When the external outcome is ambiguous, query or reconcile by idempotency key before retrying rather than assuming failure.
7
8	See /src/db for the adjacent decision or procedure that completes this constraint.

Retry only classified transient concurrency failures/src/dbhighstrictRetry the entire transaction with bounded backoff, a stable operation key, and fresh reads; surface permanent conflicts immediately.

1	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.
2
3	- Classify engine-specific deadlock, serialization, and selected lock-timeout outcomes separately from validation, constraint, authentication, syntax, and capacity errors.
4	- Discard the failed transaction and run the whole service transition again against fresh state.
5	- Use bounded attempts with jitter and respect the request or job deadline; contention retries must not amplify an overloaded database indefinitely.
6	- Require stable operation identity for any transition that clients, queues, or infrastructure may also retry.
7
8	See /tests/concurrency for the adjacent decision or procedure that completes this constraint.

Memories

Isolation level is chosen per invariant and query shape/src/dbSelect the weakest level that still protects the operation, then add locks or constraints where the engine contract requires them.

1	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.
2
3	- Describe the concurrent interleaving that would violate the invariant and identify which reads or predicate ranges participate.
4	- Use database constraints for facts the schema can enforce and transaction isolation or explicit locking for multi-row or conditional transitions.
5	- Test the actual engine and access path because indexes and predicate locks can affect which concurrent changes conflict.
6	- Record why the operation uses its isolation level and what retry behavior callers must support when the engine aborts it.
7
8	See /src/services for the rule or workflow that puts this decision into practice.

Optimistic concurrency makes lost updates visible/src/servicesCarry a version or compared prior state through the write and treat zero updated rows as a domain conflict.

1	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.
2
3	- Return a version token with editable data and include it in the update predicate alongside stable identity and authorization scope.
4	- Increment or replace the token atomically on success and check the affected row count.
5	- On conflict, load the current state and let product policy reject, merge, or ask the user; never overwrite silently with the stale snapshot.
6	- Keep fields with commutative operations, such as counters, on atomic update paths rather than forcing a whole-record version conflict.
7
8	See /tests/concurrency for the rule or workflow that puts this decision into practice.

Skills

investigate-database-deadlock/rootReconstruct the lock cycle, map statements to application transitions, fix ordering, and verify bounded retries.

1	---
2	name: investigate-database-deadlock
3	description: Investigate a database deadlock or repeated serialization failure under production concurrency.
4	---
5
6	# Investigate Database Deadlock
7
8	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.
9
10	1. Capture the engine deadlock graph or lock-wait evidence, transaction and statement identities, bound resource keys, timing, and application operation IDs.
11	2. Map each statement to its service transaction and list the order in which rows, indexes, tables, and advisory locks are acquired.
12	3. Identify the cycle and choose a deterministic order or narrower transaction that removes one edge without weakening the invariant.
13	4. Reproduce with a concurrent test that controls interleaving, then verify one transaction commits and the other retries or conflicts as designed.
14	5. Monitor deadlock rate, retry attempts, transaction duration, and final operation outcomes after release; preserve the graph and fix rationale beside the service.
15
16	## Exit criteria
17
18	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.

Why this pattern

AI agents often read then write outside one transaction, hold locks during HTTP calls, retry a deadlock without making the operation idempotent, or apply locks in inconsistent order.

Built for Backend teams protecting inventory, balances, quotas, workflow state, and other shared relational data.

Keeps your assistant from:

Overselling inventory through concurrent check-then-update requests
Creating a deadlock cycle through inconsistent lock acquisition
Charging or publishing twice after a transaction retry
Holding database locks while waiting on a remote dependency

View as markdown

License: Apache-2.0
Version: 1.0.0
Updated: 2026-08-25