返回提示词目录第 1339 页

数据库事务与并发竞态审计

结合数据库隔离语义逐步推演并发交错,定位业务约束失效场景,输出原子修复与确定性测试方案。

完整提示词

# TRANSACTION AND CONCURRENCY AUDIT

I want an in-depth audit of concurrency controls, transaction boundaries, isolation levels, and race conditions.

Main objective:

> Identify areas where the system operates correctly under single-threaded execution, but produces erroneous results when two or more operations execute concurrently.

## 1. OBJECTIVE AND NON-GOALS

For every high-value mutation, prove whether it remains correct when two or more executions overlap in time: parallel requests, retries, duplicate messages, background jobs, multiple workers and replicas. A finding must show a concrete interleaving that breaks an invariant and name the mechanism that would prevent it.

Non-goals:

- recommending "use a transaction" or "use serializable" without showing why the current isolation level allows the anomaly
- general performance tuning, except where contention or lock waits cause incorrect behavior or outages
- introducing distributed locks or sagas where a single database already guarantees the invariant

## 2. CONCURRENCY MODEL FIRST

Before analyzing any race, establish:

```text
Database engine and exact version:
Default isolation level and any per-transaction overrides:
How the ORM opens transactions (explicit, implicit per request, autocommit):
Row-locking primitives in use (SELECT ... FOR UPDATE, advisory locks, version columns):
Read replicas and whether reads after writes can hit a replica:
Number of application instances and workers:
Queue technology, delivery guarantee, visibility timeout, consumer concurrency:
Scheduled jobs and how many instances run them:
Distributed locks (implementation, TTL, renewal):
Retry layers (client, load balancer, HTTP library, ORM, queue):
```

Isolation levels with the same name behave differently across engines (what "repeatable read" prevents, whether write skew is possible, how serialization failures are reported). Verify the semantics for the detected engine and version; never rely on textbook definitions when the engine differs.

## 3. EVIDENCE MODEL

```text
A - reproduced: a deterministic interleaving test, a stress test or production data shows the anomaly
B - complete path: code, transaction boundaries, isolation semantics and constraints show that the interleaving is possible
C - strong static evidence: a read-check-write pattern without a visible guard, but isolation or locking not fully traced
D - inference: plausible race depending on timing, configuration or infrastructure behavior
E - hardening: extra protection where the current mechanism already prevents the anomaly
```

## 4. FINDING STATUS

- **CONFIRMED** - the anomaly was reproduced or the interleaving is proven possible (tier A or B).
- **LIKELY** - strong static evidence (tier C).
- **NOT VERIFIED** - depends on isolation, deployment topology or provider behavior that could not be established.
- **NOT APPLICABLE** - only one writer can ever execute the path (for example a single-consumer queue per key).
- **CONTROLLED** - the race can occur but a constraint, atomic statement, lock, idempotency key or reconciliation makes the outcome correct.
- **HARDENING** - defense in depth without a current failure path (P4).

Do not report a missing best practice as a confirmed defect unless there is a concrete interleaving that produces a wrong result.

## 5. FALSE-POSITIVE RULES

The following are **not** findings by themselves:

- A read followed by a write is not automatically a race if the write is conditional (`UPDATE ... WHERE version = ?`, `WHERE stock > 0`) and the affected row count is checked.
- A missing `SELECT ... FOR UPDATE` is not a defect when a unique constraint or atomic statement already enforces the invariant.
- Read committed isolation is not automatically wrong; many invariants are safely protected by constraints and atomic updates under it.
- A non-idempotent endpoint is not a finding if no client, proxy or queue can retry it and duplicates have no business effect.
- Eventual consistency between a primary and a replica is not a race defect unless a decision is made from a stale replica read.
- A theoretical race on data nobody writes concurrently in practice is at most HARDENING; state why concurrency is or is not realistic.

## 6. INTERLEAVING NOTATION

Every race finding must include the interleaving that breaks the invariant, written step by step with the value each actor sees:

```text
T1 read   balance = 100
T2 read   balance = 100
T1 check  100 >= 80  OK
T2 check  100 >= 80  OK
T1 write  balance = 20
T2 write  balance = 20
result    two withdrawals of 80 succeeded, balance should be -60 or one should fail
```

Then show the same interleaving with the proposed fix and which step now blocks, fails or retries.

## 7. RACE CLASSES

Classify every finding:

```text
lost update          two read-modify-write cycles, one overwrites the other
duplicate create     check-then-insert creates two rows for one logical entity
write skew           two transactions read overlapping data and write disjoint rows, violating a shared rule
stale write          a write based on a value that changed after it was read (old form, old version)
check-then-act       permission, quota or state checked, then acted on after it changed
delete/update race   an update or job runs on an entity that was deleted or archived meanwhile
revoke/use race      a token, session or role is used after it was revoked
lease expiry         a lock holder keeps working after its lease expired and another holder started
timeout/retry race   a timed-out operation actually succeeded and the retry executes it again
```

## 8. CRITICAL MUTATIONS

Inventory:

- payments
- inventory
- quotas
- role changes
- ownership
- counters
- coupons
- reservations
- state transitions

## 9. TRANSACTION BOUNDARY

For each mutation map:

```text
Read:
Checks:
Writes:
External calls:
Events:
Commit:
```

## 10. READ-MODIFY-WRITE

High-signal race condition pattern.

## 11. LOST UPDATE

Transaction A and Transaction B read identical version state.

## 12. WRITE-WRITE

Unintentional last-writer-wins overwrites.

## 13. OPTIMISTIC LOCK

Version check and conditional increment mechanisms.

## 14. ATOMIC UPDATE

```text
UPDATE ... WHERE stock > 0
```

provides stronger concurrency guarantees than application-side checks.

## 15. ATOMIC DATABASE PRIMITIVES

When the invariant lives in one database, prefer the database's own atomic primitives over application-level coordination:

- conditional update with a row-count check: `UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock >= 1`
- unique constraints (including partial or composite ones) for "only one" rules
- `INSERT ... ON CONFLICT` / upsert semantics, verified for the engine
- check and exclusion constraints for ranges and overlaps where the engine supports them
- sequences or identity columns instead of `MAX()+1`

For every application-level check, ask whether one of these makes it unnecessary.

## 16. UNIQUE CONSTRAINT

Race-safe uniqueness enforcement.

## 17. CHECK-THEN-INSERT

Unsafe under concurrency without underlying unique database constraints.

## 18. QUOTA

COUNT followed by INSERT race condition.

## 19. COUPON

Verifying unused status followed by marking as redeemed.

## 20. ONE-TIME TOKEN

Single-use token redemption races.

## 21. PAYMENT REFUND

Preventing duplicate refund issuance.

## 22. INVENTORY

Stock decrements under concurrent purchase requests.

## 23. BOOKING

Preventing overlapping calendar bookings.

## 24. ACCOUNT BALANCE

Ledger updates and balance mutations.

## 25. ROLE GRANT

Concurrent administrative privilege modifications.

## 26. OWNERSHIP TRANSFER

Resource ownership transfers under parallel requests.

## 27. ISOLATION LEVEL

Evaluate actual database engine runtime behavior.

## 28. ISOLATION SEMANTICS FOR THIS ENGINE

For the detected engine and version, state concretely:

- which anomalies the configured isolation level allows (lost update, non-repeatable read, phantom, write skew)
- whether locking reads (`FOR UPDATE`, `FOR SHARE`) change that for the rows they touch
- how conflicts surface: blocking, a serialization error, a deadlock error, or silent last-writer-wins
- whether the application catches those errors and retries the **whole** transaction, not only the last statement
- whether some transactions run at a different level than the default (ORM options, per-connection settings)

A finding that depends on isolation must name the level and the engine behavior it relies on.

## 29. READ COMMITTED

Read committed default behaviors and anomalies.

## 30. REPEATABLE READ

Repeatable read semantics.

## 31. SERIALIZABLE

Do not mandate serializable isolation globally without analyzing throughput impact.

## 32. SNAPSHOT

Snapshot isolation and write skew vulnerabilities.

## 33. NON-REPEATABLE READ

Anomalies permitted under lower isolation levels.

## 34. PHANTOM

Phantom reads impacting business invariant enforcement.

## 35. WRITE SKEW

Two doctors on-call pattern invariant violations.

## 36. LOCK

Row-level, table-level, and advisory locks.

## 37. OPTIMISTIC VS PESSIMISTIC CONCURRENCY

Evaluate the chosen strategy against the workload:

- **optimistic** (version column, conditional update): good for low contention; verify that the version is checked in the WHERE clause, that the affected row count is checked, and that the user or job gets a clear conflict outcome instead of a silent overwrite
- **pessimistic** (`SELECT ... FOR UPDATE`, advisory locks): good for high contention on few rows; verify lock scope, lock ordering, timeouts, and that no external call happens while the lock is held
- mixed strategies on the same row (one path uses versions, another writes without them) break the optimistic guarantee

## 38. LOCK ORDER

Inconsistent lock acquisition ordering causing deadlocks.

## 39. DEADLOCK RETRY

Implement bounded retries with complete transaction restarts.

## 40. DEADLOCK ANALYSIS

For every multi-row or multi-table write path:

- list the lock acquisition order of each path; two paths that lock the same rows in different orders can deadlock
- identify which transaction the engine chooses as the victim and whether the application retries it safely (whole transaction, bounded attempts, backoff with jitter)
- check whether retried transactions repeat side effects (emails, provider calls, events)
- check deadlock and lock-wait metrics or logs for real occurrences before rating severity

## 41. EXTERNAL CALL INSIDE TRANSACTION

Holding database locks for seconds across outbound network calls.

## 42. EXTERNAL CALL BEFORE COMMIT

External provider call succeeds while database commit rolls back.

## 43. EXTERNAL CALL AFTER COMMIT

Database commit succeeds while external provider call fails.

## 44. SIDE EFFECTS AND COMMIT BOUNDARIES

For every transaction with an external effect, place the effect on the timeline:

```text
before commit   effect happens even if the transaction rolls back (charge without order)
inside commit   impossible for external systems; only the database is atomic
after commit    effect can be lost if the process dies between commit and call (order without email)
```

Acceptable designs make the effect recoverable: a transactional outbox with an idempotent relay, an idempotency key sent to the provider, or a reconciliation job. Check that the provider call uses a stable idempotency key derived from the business operation, not a new random key on every retry.

## 45. OUTBOX

Transactional outbox pattern for reliable external messaging.

## 46. SAGA

Sagas for distributed workflows (not an automatic requirement for local boundaries).

## 47. IDEMPOTENCY

Critical operational safeguard.

## 48. IDEMPOTENCY KEY SCOPE

Scoping keys by user, tenant, and operation.

## 49. SAME KEY DIFFERENT BODY

Handling payload conflicts for identical idempotency keys.

## 50. CONCURRENT SAME KEY

Atomic locking or unique constraints on idempotency keys.

## 51. RETRY

Database, network, and client retry handling.

## 52. CLIENT DOUBLE CLICK

Duplicate client form submissions.

## 53. MOBILE RETRY

Aggressive mobile network retry behaviors.

## 54. LOAD BALANCER RETRY

Upstream proxies automatically retrying idempotent or non-idempotent requests.

## 55. QUEUE DUPLICATE

At-least-once queue delivery semantics.

## 56. WEBHOOK DUPLICATE

Repeated webhook callbacks from third-party services.

## 57. OUT-OF-ORDER

Out-of-order message delivery processing.

## 58. STALE JOB

Background worker executing after permissions or entity state changed.

## 59. QUEUE AND WORKER CONCURRENCY

For every consumer:

- how many consumers process messages for the same entity at the same time?
- is ordering guaranteed per key (partition, message group), or can two events for one entity run in parallel?
- what happens when processing takes longer than the visibility timeout or lease: is the message redelivered to a second worker while the first still runs?
- are handlers idempotent on the message ID or the business key?
- can a scheduled job run on several instances at once, or overlap with its own previous run?

## 60. TOCTOU

Time-of-check to time-of-use authorization and mutation races.

## 61. FILE

Checking file existence or metadata prior to overwriting.

## 62. CACHE LOCK

Cache stampede and dogpile prevention.

## 63. DISTRIBUTED LOCK

Audit:

- TTL
- ownership token
- renewal
- clock
- failure

## 64. LOCK EXPIRY

Long-running operations continuing after lock expiry leading to dual active workers.

## 65. REDLOCK-LIKE

Do not prescribe complex distributed locking algorithms without comprehensive failure modeling.

## 66. DISTRIBUTED LOCKS AND FENCING TOKENS

A distributed lock with a TTL does not guarantee mutual exclusion by itself:

```text
worker A acquires lease (TTL 30 s)
↓
worker A pauses (GC, network, slow provider call) for 45 s
↓
lease expires; worker B acquires it and starts writing
↓
worker A resumes and writes, believing it still holds the lock
```

For every distributed lock verify:

- **lease duration** vs the worst-case duration of the protected work
- **renewal**: how the lease is extended, and what happens if renewal fails
- **owner verification**: release and renewal only succeed for the current owner token
- **fencing token**: a monotonically increasing number issued with the lease and checked by the resource being written (for example `UPDATE ... WHERE fence < ?`), so a stale holder's writes are rejected
- **clock assumptions**: behavior under clock drift between nodes
- **failure of the lock service** itself: does the system fail closed or run unprotected?

If the protected resource is a single database, a row lock or conditional write in that database is usually simpler and safer than a distributed lock.

## 67. DB LOCK PREFERRED

If an invariant resides in a single database, native database atomicity is vastly simpler and safer.

## 68. COUNTER

Atomic increment operations.

## 69. SEQUENCE

Database sequence allocation.

## 70. MAX()+1

Race condition in primary key or order generation.

## 71. ORDER POSITION

Two concurrent inserts assigning the identical sequence position.

## 72. DELETE VS UPDATE

Race between entity deletion and concurrent update.

## 73. DELETE VS JOB

Entity deletion racing against queued background worker processing.

## 74. ARCHIVE VS EDIT

Archiving an entity racing against active edit requests.

## 75. ROLE REVOKE VS REQUEST

Role revocation racing against an in-flight authenticated request.

## 76. SESSION REVOKE

Session revocation propagation latency.

## 77. TRANSACTION TIMEOUT

Configuring upper limits on transaction duration.

## 78. IDLE TRANSACTION

Monitoring and terminating idle-in-transaction connections.

## 79. LOCK WAIT

Monitoring lock wait queues.

## 80. CONNECTION POOL

Blocked or slow transactions exhausting the shared connection pool.

## 81. HOT ROW

Global application settings or contention hotspots.

## 82. HIGH CONTENTION

Benchmarking behavior under high contention.

## 83. RETRY STORM

Cascading retry storms triggered by serializable failures or deadlocks.

## 84. BACKOFF/JITTER

Implementing exponential backoff with randomized jitter on transaction retries.

## 85. CONCURRENCY TEST

Utilize synchronization barriers to enforce exact operational interleaving.

## 86. TEST FORMAT

```text
T1 read
T2 read
T1 write
T2 write
```

## 87. DETERMINISTIC RACE TEST

Deterministic interleaving tests are vastly superior to probabilistic loops.

## 88. DETERMINISTIC RACE TESTING

Do not rely on loops that "usually" trigger a race. Force the interleaving:

- barriers or latches in the test that pause T1 after its read until T2 has read too
- hooks or fault-injection points in the code path (test-only) between check and write
- two database sessions driven step by step by the test
- for retries: simulate a timeout after the side effect succeeded, then run the retry

Each confirmed finding should come with a test that fails before the fix and passes after it.

## 89. CONCURRENCY INTERLEAVING MATRIX

| Mutation | Invariant | Race class | Actors | Breaking interleaving | Current guard | Isolation | Retry safe | Status |
|---|---|---|---|---|---|---|---|---|

## 90. FINDING FORMAT

```text
ID:
Severity:
Status:
Evidence tier:
Race class:
Scope (mutation, tables, services):
Invariant:
Trigger (parallel requests, retry, duplicate message, job overlap):
Actors:
Transaction boundary and isolation:
Current guards:
Interleaving (step by step):
Expected result:
Actual result:
Impact:
Blast radius:
Evidence:
Root cause:
Fix (atomic statement, constraint, lock, idempotency, fencing):
Retry behavior after the fix:
Verification (deterministic test):
Regression risk (contention, deadlocks, latency):
```

## 91. SEVERITY

- **P0** - a race that allows systematic financial loss (double spend, double refund, unlimited coupon use), privilege escalation, or cross-tenant writes, and that an attacker can trigger deliberately.
- **P1** - a repeatable race on a critical mutation (payments, inventory, quotas, roles, ownership) that produces wrong business results under normal concurrency or retries.
- **P2** - a race with real but limited impact (duplicate notifications, occasional lost edits, recoverable inconsistencies), or deadlocks and lock waits that cause failed requests.
- **P3** - races on non-critical data or with very narrow timing windows and small impact.
- **P4** - hardening: additional constraints, tests or monitoring where the current mechanism already works.

Consider exploitability: a race that a user can trigger at will with parallel requests is more severe than one that needs rare infrastructure timing.

## 92. OUTPUT

`TRANSACTION_CONCURRENCY_AUDIT.md`

## 93. SECOND PASS

For every high-value mutation execute or reason through:

- 2 and 10 parallel requests with identical input
- duplicate submissions with the same idempotency key, and the same key with a different body
- a retry after a timeout in which the first attempt actually succeeded
- the same message delivered twice and two related messages out of order
- processing that outlives a visibility timeout or lock lease
- deadlock paths between the mutation and other writers of the same rows
- an update against a stale version token
- permission revocation or entity deletion while the operation is in flight
- reads served from a replica immediately after the write

Then try to disprove each finding: is there a constraint, atomic statement or single-writer path you missed? Does the engine's isolation actually prevent this interleaving?

## 94. FINAL QUALITY GATE

Before returning the report, verify that:

- the concurrency model (engine, version, isolation, workers, queues, retries) is documented
- every race finding has a step-by-step interleaving and a race class
- isolation claims are specific to the detected engine and version
- atomic database primitives were considered before application-level locks
- external side effects are placed on the commit timeline and their recovery is described
- retries (client, proxy, library, queue) were checked for duplicate effects
- distributed locks were checked for lease duration, renewal, owner verification and fencing
- queue consumers were checked for parallel processing of the same entity and redelivery during long processing
- every confirmed finding has a deterministic test proposal
- statuses and evidence tiers are applied consistently; no finding says only "use a transaction"

# FINAL RULE

Looking for:

```text
quota = 10

current rows = 9

Request A:
COUNT = 9

Request B:
COUNT = 9

A inserts
B inserts

final = 11
```

and concrete atomic fixes, not merely:

> Use a transaction.

Transactions executing under improper isolation levels can still permit race conditions.

Other failure chains to look for:

```text
refund job holds a distributed lock with a 60 s lease
↓
provider call hangs for 90 s
↓
lease expires; a second worker takes the lock and issues the refund
↓
first worker's call completes; it also records a refund
↓
customer refunded twice; no fencing token rejected the stale worker
```

```text
rule: at least one doctor on call per shift
↓
Doctor A and Doctor B both read "2 on call" under snapshot isolation
↓
each updates only their own row to "off call"
↓
no row conflict, both commit
↓
nobody on call (write skew)
```

```text
payment request times out at the load balancer after the provider charged the card
↓
client retries with a new idempotency key generated per attempt
↓
provider treats it as a new charge
↓
customer charged twice; local order shows one payment
```

分类与标签

编程开发、数据库事务、竞态条件、隔离级别、幂等性、并发测试

前往 AI 图片创作