Handling Data Consistency

Why this matters

As a Backend Engineer, you must keep data correct even when many users hit your system at once, services fail, or retries happen. Real tasks include:

• Preventing overselling inventory during flash sales.
• Ensuring money transfers never create or lose funds.
• Avoiding duplicate orders when clients retry requests.
• Keeping related records in sync (e.g., order and shipment).

Consistency builds user trust and reduces costly production incidents.

Concept explained simply

Consistency means your data always obeys the rules you set (like “balance can’t be negative” or “one order per request”). You enforce those rules with constraints, transactions, and careful read/write patterns.

Mental model

Think of your database as a ledger with a strict librarian:

• The librarian only files complete, valid entries (transactions).
• They reject entries that break rules (constraints).
• They control who reads or edits a page at a time (isolation and locks).
• They stamp each request with a unique ticket so the same job isn’t filed twice (idempotency keys).

Core tools you will use

• Constraints: primary keys, unique, foreign keys, check constraints.
• Transactions: all-or-nothing units of work.
• Isolation levels: control read/write concurrency to avoid anomalies (dirty reads, lost updates, phantom reads).
• Locks: row/table locks, SELECT ... FOR UPDATE.
• Idempotency & dedup: request_id or operation_key with unique indexes.
• Optimistic vs. pessimistic concurrency: version columns vs. explicit locks.
• Ordering & time: server-side timestamps, monotonic IDs for event ordering.
• Distributed flows: outbox + inbox patterns, sagas as sequences of local transactions.

Worked examples

Example 1: Prevent overselling inventory (pessimistic lock)

1. Start a transaction.
2. Lock the row: SELECT stock FROM inventory WHERE sku = ? FOR UPDATE.
3. If stock < qty: abort with error.
4. Update: UPDATE inventory SET stock = stock - ? WHERE sku = ?.
5. Insert order record with a unique request_id.
6. Commit.

BEGIN;
SELECT stock FROM inventory WHERE sku = $1 FOR UPDATE;
-- app check: stock >= $2
UPDATE inventory SET stock = stock - $2 WHERE sku = $1;
INSERT INTO orders (order_id, customer_id, sku, qty, request_id)
VALUES ($3, $4, $1, $2, $5);
COMMIT;

Example 2: Idempotent order creation with upsert

Goal: the same request from the client should not create multiple orders.

-- orders(request_id UNIQUE)
INSERT INTO orders (order_id, customer_id, sku, qty, request_id)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (request_id) DO NOTHING;
-- Then fetch by request_id to return an existing order.

Example 3: Optimistic locking to prevent lost updates

Use a version column and verify it during updates. If version changed, retry with fresh data.

-- profile(id PK, name, email, version INT)
UPDATE profile
SET name = $2, email = $3, version = version + 1
WHERE id = $1 AND version = $4;
-- If rowcount = 0, someone else updated first. Reload and retry.

Exercises

Do these hands-on tasks. Compare your work to the solutions. Tip: use transactions and constraints, not just code checks.

Exercise 1 — Safe wallet transfer with idempotency (mirrors ex1)

Design a transaction that:

• Debits one account and credits another.
• Prevents negative balances.
• Is idempotent using transfer_key (unique).

Exercise 2 — Stop double-ordering (mirrors ex2)

Write SQL or pseudocode to create an order only if stock allows, avoiding duplicates on client retries.

Self-review checklist

• All critical invariants are enforced by the database (constraints).
• No update can silently overwrite another (locking or version checks).
• Retries do not duplicate side effects (idempotency keys).
• Transactions are minimal and only lock what is needed.
• Errors roll back cleanly with no partial writes.

Common mistakes and self-check

• Only validating in application code. Fix: add UNIQUE, FK, CHECK constraints.
• Forgetting idempotency on endpoints that may be retried. Fix: store a request_id with a unique index.
• Long-running transactions holding locks. Fix: keep them short; move slow work outside.
• Using the strongest isolation everywhere. Fix: choose per-use-case; escalate only where needed.
• Reading before locking. Fix: lock the rows you are about to change with SELECT ... FOR UPDATE.
• Ignoring time/order. Fix: use server-generated timestamps or monotonic IDs when order matters.

Practical projects

• Double-entry ledger service: accounts, entries, and balances with constraints to ensure debits equal credits per transaction.
• Order service with idempotent create and inventory decrement using SELECT ... FOR UPDATE.
• Outbox-based email sender: write events to outbox in the same transaction as the main change; a worker reads and sends reliably with idempotent processing.

Who this is for

Backend and platform engineers who build APIs and services that must remain correct under concurrency, failures, and retries.

Prerequisites

• Basic SQL (SELECT/INSERT/UPDATE/DELETE).
• Transactions and indexes basics.
• Familiarity with your DB’s isolation levels.

Learning path

1. Model invariants as constraints.
2. Add transactions with correct isolation and locking.
3. Implement idempotency on all externally triggered writes.
4. Use optimistic locking for user-edited records; pessimistic locks for hot counters/inventory.
5. Integrate outbox/inbox for cross-service reliability.

Next steps

• Refactor one existing write path in your codebase to be idempotent.
• Add missing constraints for top invariants.
• Run a small concurrency test and verify no anomalies.

Mini challenge

You must ensure: “A customer cannot place more than one free trial order.” Stock is unlimited. Which minimal DB approach would you use?