Translate business rules into invariants
An order cannot reserve more inventory than exists. A payment should not be applied twice. An accepted order must eventually become visible to fulfillment or be explicitly cancelled. These are different guarantees; choose a mechanism for each rather than saying the database is ACID and moving on.
Atomicity groups changes, consistency concerns valid states, isolation controls concurrent observations, and durability concerns acknowledged persistence under the storage system's fault assumptions. Database ACID does not automatically make an email or external payment part of its transaction.
Isolation and anomalies
| Anomaly | Example | Candidate control |
|---|---|---|
| Dirty read | Read an uncommitted balance | Isolation that excludes dirty data |
| Nonrepeatable read | Same row changes between reads | Stable snapshot or stronger locking |
| Phantom | Repeated predicate sees new qualifying rows | Appropriate predicate/snapshot semantics |
| Lost update | Two readers overwrite increments | Atomic update, lock or version comparison |
| Write skew | Two doctors each remove themselves from duty | Serializable transaction or invariant-specific lock |
Implementations differ. PostgreSQL treats Read Uncommitted like Read Committed, and Serializable transactions can require the application to retry after a serialization failure. Do not map names to behavior without checking the database.
Optimistic vs pessimistic concurrency
Optimistic concurrency adds a version and updates only when it matches the version read. Zero affected rows means conflict, not success. Pessimistic locking holds a lock while a transaction makes its decision. Keep transactions short, use a consistent lock order and handle deadlocks.
UPDATE documents
SET body = 'updated text', version = version + 1
WHERE id = 17 AND version = 8;
Version checks prevent silent overwrites but do not merge edits. For collaborative writing, choose conflict resolution separately.
Idempotency needs atomic state
A payment request has a stable client operation ID scoped to account and operation. Store a request fingerprint, processing state and result. Enforce uniqueness in the authoritative store. The same key with a different payload must be rejected; an identical retry should return a semantically compatible outcome.
A naive check-then-charge races. A DB uniqueness constraint alone also cannot atomically include a remote charge. Use the provider's idempotency mechanism, durable workflow state and reconciliation. Handle a timeout as an unknown result until you query or reconcile it. Expiring dedupe records too early reopens duplicate risk.
The dual-write problem and outbox
Unsafe: commit order -> process crashes -> event never published
Safe intent: one DB transaction writes order + outbox event
Relay: publish outbox -> mark delivered; duplicates remain possible
Consumer: unique inbox event ID + business update in one transaction
A relay crash after publish but before marking produces a duplicate; consumer-side idempotency is still necessary. The outbox solves an atomic-intent problem, not universal end-to-end exactly-once execution.
Change Data Capture
CDC reads committed database changes, usually from a transaction log, and moves them into downstream processing. An initial snapshot establishes baseline data; a resumable log position continues the stream. Snapshot boundaries, schema changes, log retention, delete/tombstone handling and connector lag all need design.
An outbox table makes business-event intent explicit; CDC on arbitrary tables exposes storage changes rather than necessarily meaningful domain events. Keep personal data out of broad event streams unless access and retention are deliberately controlled.
Compare three CDC capture paths
| Capture path | How it discovers changes | What to account for |
|---|---|---|
| Timestamp polling | Reads rows modified after a cursor | Hard deletes, equal timestamps, indexing and missed updates |
| Triggers | Writes an audit/change row inside the transaction | Added write work and trigger maintenance |
| Transaction-log capture | Reads a supported WAL/binlog stream | Permissions, log retention, snapshots and schema evolution |
A timestamp cursor needs a stable tie-breaker and a reliable update discipline; simply saving the greatest wall-clock time can skip rows. Represent deletes explicitly if consumers need them. Triggers can record deleted-row details but must participate in the intended transaction. Log-based capture avoids polling every table, yet a stalled connector can retain logs and consume disk.
Start consumers from a coordinated snapshot and log position so the boundary neither loses nor incorrectly reorders changes. Persist processing progress and deduplicate replay. Row changes are not automatically business events: an order's internal column update and an explicit order-accepted event have different meanings.
Repeating an operation versus repeating a response
Idempotency means repeating a request has the same intended effect as one application. It does not require byte-identical responses. A deletion may first return success and later return not found while the resource remains deleted. POST and PATCH are not generally idempotent merely because they use HTTP; an application contract must supply the relevant guarantee.
Saga vs Two-Phase Commit
A saga coordinates local transactions and compensations. Reserve inventory, request payment, then confirm shipment; on payment failure release the reservation. Compensation may fail and require retries or manual repair. It is a new business action, not guaranteed time travel: you cannot un-send a delivered email.
2PC asks participants to prepare, then commit or abort according to the coordinator. Prepared participants retain resources, and failures can block progress in classic forms. Use supported transaction infrastructure where the cost is justified; do not implement it with casual HTTP calls.
Orchestration centralizes workflow state; choreography reacts to events. Both require correlation IDs, timeouts, durable state and operational visibility. Add event sourcing only when history/reconstruction is a requirement; replay side effects and schema evolution make it more than an audit table. CQRS separates read/write models and accepts projection lag that the UI must handle.
Exercise: inventory and payment
Draw every crash point in reserve, charge and confirm. State where uniqueness is enforced, how a timeout is reconciled, how reservations expire and how the read model catches up. Build a repair job that finds stuck orders and uses the same safe operations as normal processing.
Next: Kafka and streaming, queues, and database scaling.