ACID Properties — Batch Atomicity Without Transactions

ACID is the set of properties that makes a database trustworthy. Without ACID, a power outage at the wrong millisecond could leave your data in a corrupted, half-updated state — a nightmare for financial records, medical systems, or any application where partial state is not just wrong but dangerous. Imagine a bank transfer: if the system fails after debiting your account but before crediting the recipient, that money vanishes. Not temporarily missing, not in a reconciliation queue — gone. ACID prevents this.

These four properties work in tandem, implemented through sophisticated internal mechanisms like transaction logs, locking protocols, and multi-version concurrency control. As a developer, understanding ACID is not about passing interviews. It is about understanding the performance trade-offs you make every time you choose an isolation level, decide between a traditional RDBMS and a relaxed NoSQL system, or configure how aggressively your database flushes data to disk.

A common misconception is that ACID is binary — either a database has it or it does not. In reality, every property has configurable levels that trade safety for throughput. PostgreSQL lets you trade durability for speed with synchronous_commit. MySQL lets you choose between InnoDB's row-level MVCC and MyISAM's table-level locking. The properties exist on a spectrum, and production tuning means knowing which dial to turn, how far to turn it, and what breaks if you go too far.

Atomicity — The All-or-Nothing Guarantee

Atomicity ensures that a transaction is treated as a single, indivisible unit. If any part of the transaction fails — a constraint violation, a timeout, a crash, an OOM kill — the entire operation is rolled back, leaving the database in the exact state it was in before the transaction started. There is no partial commit. There is no state where half the work is done and the other half is not.

The primary implementation mechanism is the Write-Ahead Log (WAL). Before any data pages are modified on disk, the intended changes are appended sequentially to the WAL. If a crash occurs mid-transaction, the database reads the WAL during recovery and identifies any transaction that started but never committed. Those transactions are rolled back — their changes are undone as if they never happened.

In MySQL/InnoDB, atomicity uses the undo log in addition to the redo log. When a transaction modifies a row, the original value is copied to the undo log before the change is applied to the data page. If ROLLBACK is issued — or if the server crashes before COMMIT — the database replays the undo log to restore every modified row to its pre-transaction state.

The critical production insight is this: atomicity only exists within explicit transaction boundaries. If you run 10,000 SQL statements in auto-commit mode, each statement is its own transaction. A crash at statement 4,700 means 4,699 statements are permanently committed and 5,301 never happened — with no mechanism to identify or recover the boundary. The $47,000 incident described above is exactly this failure mode.

SAVEPOINT extends atomicity within a transaction. It creates a named checkpoint inside a running transaction. If a subsequent operation fails, you can ROLLBACK TO SAVEPOINT to undo only the work after the savepoint, while preserving everything before it. This is essential for long-running batches where reprocessing everything from scratch is not acceptable.

-- ============================================================
-- Atomic E-commerce Order Flow
-- All three operations succeed together or none of them persist.
-- A crash between statement 2 and statement 3 rolls back everything.
-- ============================================================
BEGIN;

-- Step 1: Create the order header
INSERT INTO io_thecodeforge.orders (customer_id, status, total_amount)
VALUES (1024, 'PENDING_PAYMENT', 599.00);

-- Step 2: Reserve inventory — the heavy, contention-prone operation
-- The WHERE clause prevents negative stock without a CHECK constraint race
UPDATE io_thecodeforge.inventory
SET stock_count = stock_count - 1
WHERE product_id = 'SKU-99'
  AND stock_count > 0;

-- Verify the UPDATE actually affected a row (stock was available)
-- If 0 rows affected, the product is out of stock — abort the transaction
-- Application code checks the affected row count here

-- Step 3: Record the payment intent
INSERT INTO io_thecodeforge.payments (order_id, provider, amount, status)
VALUES (currval('orders_order_id_seq'), 'STRIPE', 599.00, 'INITIATED');

-- If ANY constraint violation occurs (FK, CHECK, UNIQUE),
-- the entire transaction is rolled back — no orphaned order,
-- no phantom inventory decrement, no unmatched payment record.
COMMIT;

-- ============================================================
-- SAVEPOINT example for batch processing
-- Enables partial rollback without losing the entire batch
-- ============================================================
BEGIN;

-- Process first 100 records
SAVEPOINT batch_checkpoint_100;

-- ... 100 INSERT/UPDATE statements ...

-- Process records 101-200
SAVEPOINT batch_checkpoint_200;

-- ... 100 INSERT/UPDATE statements ...
-- Record 157 fails with a constraint violation

-- Roll back ONLY to the last checkpoint — records 1-100 survive
ROLLBACK TO SAVEPOINT batch_checkpoint_100;

-- Resume processing from record 101 with corrected data
-- ... retry logic here ...

COMMIT;  -- Everything that was not rolled back is now durable

Consistency — The Valid State Machine

Consistency ensures that a transaction brings the database from one valid state to another valid state, respecting every defined rule — constraints, triggers, cascades, and domain invariants. If a transaction would violate any constraint (foreign key, unique, check, not null), the database rejects the entire transaction. The database never persists an invalid state, even if the application code attempts to create one.

Consistency is the property that ties the other three together. Atomicity ensures no partial transaction leaks invalid intermediate state. Isolation ensures concurrent transactions do not create invalid combinations of values that no single transaction would produce. Durability ensures the valid state, once committed, persists through crashes. Without any one of the other three, consistency cannot be guaranteed.

There are two kinds of consistency that matter in practice. Schema-level consistency is enforced by database constraints: foreign keys prevent orphaned references, unique constraints prevent duplicates, check constraints prevent domain violations like negative stock counts. Application-level consistency is enforced by business logic: a transfer must debit and credit the same amount, an order must have at least one line item, a subscription cancellation must trigger a prorated refund. The database cannot enforce application-level invariants automatically — those are the application developer's responsibility, backed by database constraints as a safety net.

In production, constraint violations surface as SQL errors that applications must handle gracefully. A common anti-pattern is catching constraint violations and retrying blindly without diagnosing why the violation occurred. A unique constraint violation on an idempotency key is normal and expected — the correct response is to return the existing record, not to retry with a new key. A foreign key violation is a bug — it means the application is referencing data that does not exist, and retrying will produce the same failure.

-- ============================================================
-- Consistency Enforcement — Constraints as Safety Nets
-- The database rejects any transaction that would create invalid state.
-- ============================================================

-- 1. Foreign Key: an order cannot reference a non-existent customer
--    This prevents orphaned records that break JOIN integrity.
INSERT INTO io_thecodeforge.orders (customer_id, status, total_amount)
VALUES (99999, 'PENDING', 100.00);
-- ERROR:  insert or update on table "orders" violates foreign key constraint
--         "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(99999) is not present in table "customers".

-- 2. Check Constraint: stock cannot go negative
--    This catches the bug where two concurrent decrements both pass
--    the application-level check but the net result goes below zero.
ALTER TABLE io_thecodeforge.inventory
ADD CONSTRAINT chk_stock_non_negative
CHECK (stock_count >= 0);

UPDATE io_thecodeforge.inventory
SET stock_count = stock_count - 50
WHERE product_id = 'SKU-99' AND stock_count = 3;
-- ERROR:  new row for relation "inventory" violates check constraint
--         "chk_stock_non_negative"
-- DETAIL: Failing row contains (SKU-99, -47).

-- 3. Unique Constraint with idempotency key: prevent duplicate payments
--    This is how you make payment retries safe — the database enforces
--    that each logical payment attempt can only succeed once.
CREATE UNIQUE INDEX IF NOT EXISTS idx_payments_idempotency
  ON io_thecodeforge.payments (idempotency_key);

-- First attempt — succeeds
INSERT INTO io_thecodeforge.payments
  (idempotency_key, order_id, provider, amount, status)
VALUES ('pay_abc123', 1024, 'STRIPE', 599.00, 'COMPLETED');

-- Retry with same idempotency key — safely ignored
INSERT INTO io_thecodeforge.payments
  (idempotency_key, order_id, provider, amount, status)
VALUES ('pay_abc123', 1024, 'STRIPE', 599.00, 'COMPLETED')
ON CONFLICT (idempotency_key) DO NOTHING;
-- INSERT 0 0 — no duplicate created, no error raised

-- To return the existing record on conflict:
INSERT INTO io_thecodeforge.payments
  (idempotency_key, order_id, provider, amount, status)
VALUES ('pay_abc123', 1024, 'STRIPE', 599.00, 'COMPLETED')
ON CONFLICT (idempotency_key)
DO UPDATE SET status = io_thecodeforge.payments.status
RETURNING *;

Isolation — Handling the Chaos of Concurrency

Isolation defines how and when the changes made by one transaction become visible to other concurrent transactions. Without isolation, the lost update anomaly occurs: two transactions read the same row, both compute a new value independently, and the second commit silently overwrites the first commit's changes. Neither transaction is aware that the other existed.

Databases solve concurrency problems using two fundamentally different approaches. Pessimistic locking (SELECT ... FOR UPDATE) acquires an exclusive lock on the row at read time — any other transaction that tries to read or write the same row blocks until the lock is released. This guarantees no lost updates but reduces concurrency. Optimistic concurrency (MVCC plus version columns) allows concurrent reads without blocking and detects conflicts at write time — if the row changed since you read it, your update fails and the application retries.

The SQL standard defines four isolation levels, from weakest to strongest:

READ UNCOMMITTED allows dirty reads — you can see uncommitted changes from other transactions. PostgreSQL silently promotes this to READ COMMITTED because dirty reads are almost never desirable.

READ COMMITTED is the PostgreSQL default. Each SQL statement within a transaction sees a fresh snapshot of committed data at the moment that statement begins. This prevents dirty reads but allows non-repeatable reads: if you read a row twice within the same transaction, you might get different values if another transaction committed a change between your two reads.

REPEATABLE READ takes a snapshot when the transaction starts and uses that snapshot for every statement within the transaction. This prevents both dirty reads and non-repeatable reads. In PostgreSQL, REPEATABLE READ also prevents phantom reads because MVCC snapshots are transaction-scoped. In MySQL/InnoDB, REPEATABLE READ uses gap locks to prevent phantom inserts, which makes it stricter than the SQL standard requires.

SERIALIZABLE provides full isolation — transactions execute as if they ran one at a time, sequentially. PostgreSQL implements this with Serializable Snapshot Isolation (SSI), which detects read-write dependencies and aborts transactions that would violate serializability. This is the safest level but reduces throughput by 20-40% under contention because conflicting transactions are aborted and must be retried.

-- ============================================================
-- Preventing the Lost Update Anomaly
-- Two concurrent transactions try to update the same row.
-- Without isolation controls, the second commit silently
-- overwrites the first commit's changes.
-- ============================================================

-- Approach 1: Pessimistic Locking — SELECT FOR UPDATE
-- Acquires an exclusive row lock at read time.
-- Any other transaction that tries to read this row FOR UPDATE
-- will block until this transaction commits or rolls back.

-- Transaction A:
BEGIN;
SELECT stock_count FROM io_thecodeforge.inventory
WHERE product_id = 'SKU-99'
FOR UPDATE;  -- exclusive lock acquired on this row
-- Returns stock_count = 10

UPDATE io_thecodeforge.inventory
SET stock_count = stock_count - 1,
    updated_at = now()
WHERE product_id = 'SKU-99';
-- stock_count is now 9

COMMIT;  -- lock released, Transaction B can proceed

-- Transaction B (concurrent):
BEGIN;
SELECT stock_count FROM io_thecodeforge.inventory
WHERE product_id = 'SKU-99'
FOR UPDATE;  -- BLOCKS here until Transaction A commits
-- After A commits, returns stock_count = 9 (the updated value)

UPDATE io_thecodeforge.inventory
SET stock_count = stock_count - 1
WHERE product_id = 'SKU-99';
-- stock_count is now 8 — CORRECT

COMMIT;

-- ============================================================
-- Approach 2: Optimistic Locking — Version Column
-- No locks held during read. Conflict detected at write time.
-- If someone else changed the row, the UPDATE affects 0 rows
-- and the application retries.
-- ============================================================

-- Add a version column to the table
ALTER TABLE io_thecodeforge.inventory
ADD COLUMN IF NOT EXISTS version INTEGER DEFAULT 1;

-- Transaction A reads the row (no lock)
SELECT stock_count, version FROM io_thecodeforge.inventory
WHERE product_id = 'SKU-99';
-- Returns: stock_count=10, version=1

-- Transaction A writes with version check
UPDATE io_thecodeforge.inventory
SET stock_count = stock_count - 1,
    version = version + 1
WHERE product_id = 'SKU-99'
  AND version = 1;  -- only succeeds if no one else changed it
-- UPDATE 1 — success, version is now 2

-- Transaction B (concurrent) tries the same
UPDATE io_thecodeforge.inventory
SET stock_count = stock_count - 1,
    version = version + 1
WHERE product_id = 'SKU-99'
  AND version = 1;  -- FAILS: version is now 2, not 1
-- UPDATE 0 — zero rows affected, application detects and retries

-- ============================================================
-- Check current isolation level
-- ============================================================
SHOW transaction_isolation;
-- Default in PostgreSQL: read committed

-- Set isolation level for a specific transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... your queries here ...
COMMIT;

Durability — Survival After the Crash

Durability guarantees that once a transaction is committed, it remains committed — even if the server loses power one millisecond later, even if the kernel panics, even if the disk controller lies about having flushed its cache. This is the property that lets you show a user 'Payment Confirmed' and know that the confirmation is permanent.

The mechanism is straightforward in principle and expensive in practice. Before the database returns COMMIT to the client, it ensures the WAL record for that transaction has been physically written to non-volatile storage — not just to the OS page cache (which is volatile RAM), but all the way down to the disk platters or flash cells. This operation is called fsync, and it is the single most expensive operation in any database write path.

Each fsync takes 1-5 milliseconds on enterprise SSDs and 5-15 milliseconds on spinning disks. With synchronous_commit=on (the PostgreSQL default), every COMMIT waits for fsync to complete before returning to the client. This limits throughput to roughly 200-1,000 commits per second per disk on SSD, because each commit must wait for the physical write to complete.

Setting synchronous_commit=off changes the bargain dramatically. The database writes the WAL record to the OS page cache and returns COMMIT immediately, without waiting for fsync. The wal_writer background process flushes the page cache to disk every wal_writer_delay milliseconds (default 200ms). This batches multiple transactions' WAL records into a single fsync, boosting throughput to 5,000-10,000 commits per second — a 5-10x improvement.

The cost of that improvement is a durability window. If the server crashes within the wal_writer_delay window after a commit, any transactions that committed during that window but were not yet flushed to disk are lost. The data was in volatile RAM (the OS page cache), and volatile RAM does not survive power loss. In practice, you can lose up to roughly 3x the wal_writer_delay value because the wal_writer may not have completed even one flush cycle before the crash.

On restart, the database replays the WAL from the last confirmed flush point. Any committed transaction whose WAL record was flushed is recovered. Any committed transaction whose WAL record was only in the page cache is gone — permanently.

-- ============================================================
-- Durability Configuration — PostgreSQL
-- synchronous_commit controls whether COMMIT waits for WAL fsync
-- ============================================================

-- Check current durability setting
SHOW synchronous_commit;
-- Default: on (full durability — COMMIT waits for fsync)

-- Check current WAL position
SELECT pg_current_wal_lsn() AS current_wal_position;

-- Check WAL file currently being written
SELECT pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

-- ============================================================
-- Performance vs Safety Trade-off
-- ============================================================

-- OPTION 1: Full durability (default) — for financial data
-- Every COMMIT waits for fsync. Throughput: ~200-1000 TPS on SSD.
SET synchronous_commit = on;
-- Use this for: payments, account balances, audit trails, medical records
-- Rule: if the user sees a success confirmation, the data must be durable.

-- OPTION 2: Relaxed durability — for analytics and session data
-- COMMIT returns before fsync. Throughput: ~5000-10000 TPS on SSD.
-- Risk: lose up to wal_writer_delay (default 200ms) of committed data on crash.
SET synchronous_commit = off;
-- Use this for: analytics events, session caches, non-critical logs
-- NEVER use for: payments, balances, anything a user has seen confirmed

-- OPTION 3: Per-transaction durability — the production sweet spot
-- Default to 'on', relax for specific non-critical writes
BEGIN;
SET LOCAL synchronous_commit = off;  -- only affects THIS transaction
INSERT INTO io_thecodeforge.analytics_events (event_type, payload)
VALUES ('page_view', '{"url": "/product/42"}');
COMMIT;  -- returns immediately, WAL flushed asynchronously

BEGIN;
-- synchronous_commit is back to 'on' for this transaction
INSERT INTO io_thecodeforge.payments (order_id, amount, status)
VALUES (1024, 599.00, 'COMPLETED');
COMMIT;  -- waits for fsync — data is durable before returning

-- ============================================================
-- Monitor WAL performance in production
-- ============================================================
SELECT
  stats_reset,
  wal_records,
  wal_bytes,
  wal_write,
  wal_sync,
  wal_write_time,
  wal_sync_time
FROM pg_stat_wal;

-- Monitor COMMIT latency (requires pg_stat_statements extension)
SELECT
  query,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
WHERE query ILIKE '%COMMIT%'
ORDER BY total_exec_time DESC
LIMIT 5;