database-transaction-consistency

Database Transaction Consistency

Core Rule

Correctness belongs in the database and domain layer, not only in application if-statements.

Workflow

1. Identify invariants: what must never be false?
2. Put simple invariants in constraints: primary key, unique, foreign key, check, not-null.
3. Define transaction boundaries around invariant changes.
4. Choose concurrency strategy: optimistic, pessimistic, unique insert, queue, or serialized workflow.
5. Define idempotency for retries.
6. Add outbox or saga when side effects cross process boundaries.
7. Test concurrent writes, retries, rollback, and deadlock behavior.

Isolation Levels

Level	Protects	Still Risky
Read committed	dirty reads	non-repeatable reads, write skew
Repeatable read	non-repeatable reads	some write skew depending on DB
Serializable	strongest correctness	more retries and aborts

Use the weakest level that preserves the invariant, but prove it with tests.

Constraint First

Prefer:

• Unique constraints for duplicate prevention.
• Foreign keys for relationship integrity.
• Check constraints for simple domain bounds.
• Exclusion constraints when ranges must not overlap.
• Partial unique indexes for conditional uniqueness.

Application validation improves UX; database constraints enforce truth.

Concurrency Patterns

Optimistic Locking

Use for low-conflict user edits.

UPDATE documents
SET title = ?, version = version + 1
WHERE id = ? AND version = ?;

If affected rows = 0, return conflict and ask the caller to reload or merge.

Pessimistic Locking

Use for high-value, short critical sections.

SELECT * FROM accounts WHERE id = ? FOR UPDATE;

Keep locked sections short. Never call slow external APIs while holding DB locks.

Unique Insert As Lock

Use for idempotency and once-only operations.

INSERT INTO idempotency_keys (key, status)
VALUES (?, 'processing')
ON CONFLICT DO NOTHING;

Outbox Pattern