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.

What ACID Properties Actually Guarantee (and What They Don't)

ACID stands for Atomicity, Consistency, Isolation, Durability — four properties that define how a database transaction behaves under failure and concurrency. Atomicity means the transaction is all-or-nothing: if any part fails, the entire operation is rolled back as if it never started. This is batch atomicity without requiring explicit transaction boundaries in the application code.

In practice, atomicity is enforced via write-ahead logging (WAL): changes are first recorded to a log, then applied to data pages. If the system crashes mid-transaction, the recovery process uses the log to undo partial writes. Consistency ensures that any transaction brings the database from one valid state to another, respecting all constraints, triggers, and cascades. Isolation controls how concurrent transactions see each other's intermediate states — typically via locking or multi-version concurrency control (MVCC). Durability guarantees that once a transaction commits, its changes survive even a power loss, usually by flushing the log to disk before acknowledging the commit.

Use ACID when correctness is non-negotiable: financial systems, inventory management, booking engines. The trade-off is performance — enforcing these properties adds latency and reduces throughput compared to eventually consistent systems. In production, the isolation level you choose (Read Committed vs. Serializable) directly impacts both correctness and contention. Most teams over-isolate, paying unnecessary latency, or under-isolate, corrupting data silently.

Who Is Responsible for Each ACID Property?

Understanding which component of the DBMS enforces each ACID property is a common interview question and critical for debugging production issues.

Critical insight for debugging: If you see inconsistent data within a transaction, suspect Isolation (check your isolation level). If committed data disappears after a crash, suspect Durability (check fsync settings). If a constraint-violating row persists, suspect a bug in Consistency enforcement (missing constraint or deferred constraint).

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;

How ACID Properties Actually Hammer Your Performance

Every ACID property comes with a tax. Atomicity forces write-ahead logs. Isolation demands locking or serialization. Durability means fsync waits. You don't get free lunch. You get correctness.

In production, that tax shows up as latency spikes and throughput ceilings. PostgreSQL's default isolation level (Read Committed) exists because Serializable would crush your TPS. MySQL's InnoDB uses row-level locking instead of table locks for the same reason — they trade strict isolation for speed.

Real systems don't blindly enable ACID. They pick battles. High-frequency trading? Durable writes before acknowledgment. Analytics dashboards? Relax isolation to Read Uncommitted so queries don't block writes. The trick is knowing which property to bend, not which to break.

When you hit a deadlock under Serializable isolation, it's not a bug. It's the database telling you your concurrency model is wrong. Listen.

// io.thecodeforge — cs-fundamentals tutorial

// Simulating the cost of atomicity with a write-ahead log
import time

def write_with_wal(account_id, amount):
    start = time.perf_counter()
    # Write-ahead log entry (must be durable before data write)
    log_entry = f"PREPARE TRANSFER: {account_id} -> {amount}"
    fsync_log(log_entry)  # Forces disk flush — 5-20ms typical
    
    # Actual data write
    update_balance(account_id, amount)
    fsync_data()           # Another flush
    
    elapsed = (time.perf_counter() - start) * 1000
    print(f"Atomic operation cost: {elapsed:.1f}ms")
    return elapsed

write_with_wal("acc_401k", -5000)

ACID vs BASE — When to Choose Which

ACID and BASE represent opposite ends of the consistency-availability spectrum in distributed systems.

ACID (Atomicity, Consistency, Isolation, Durability) prioritises correctness: every transaction either fully succeeds or fully rolls back, every read sees a consistent snapshot, and committed data survives crashes. This is exactly what you need for financial systems, inventory management, and healthcare records — any domain where a half-written state is worse than an error.

BASE (Basically Available, Soft-state, Eventually Consistent) accepts temporary inconsistency in exchange for higher availability and horizontal scalability. A BASE system might return stale data immediately after a write, but guarantees that all nodes will eventually converge. DynamoDB, Cassandra, and CouchDB are built on BASE principles.

The practical choice: - Use ACID when correctness is non-negotiable: banking, orders, medical records - Use BASE when availability and write throughput matter more than instant consistency: social media feeds, product catalogues, analytics, recommendation engines - Hybrid: many modern systems mix both — use PostgreSQL (ACID) for financial records, Cassandra (BASE) for event logs, Redis (tunable) for session state

The CAP theorem says distributed systems can guarantee at most two of: Consistency, Availability, Partition Tolerance. ACID databases typically choose CP (consistency + partition tolerance). BASE databases choose AP (availability + partition tolerance).

Property      | ACID                          | BASE
-----------   | ----------------------------- | ---------------------------
Consistency   | Immediate, strict             | Eventually consistent
Availability  | May reject during contention  | Always responds
Scalability   | Vertical (scale-up)           | Horizontal (scale-out)
Complexity    | DB handles correctness        | App must handle conflicts
Use cases     | Finance, healthcare, orders   | Social, analytics, IoT
Examples      | PostgreSQL, MySQL, SQLite     | Cassandra, DynamoDB, Mongo*

*MongoDB added multi-doc ACID in v4.0 — but defaults to BASE semantics

Critical Use Cases for ACID in Databases

Not every data store needs ACID. Your analytics cluster reading parquet files doesn't. Your session cache in Redis doesn't. But when money moves or lives depend on data, ACID is non-negotiable.

Banking is the textbook case — transfer $500 from checking to savings. Without atomicity, a partial crash leaves $500 floating in the void. Without durability, the bank acknowledges the transfer but forgets it on power loss. That's not acceptable. Ever.

E-commerce order processing is another. When a customer clicks 'Buy Now', multiple writes happen: decrement inventory, charge card, create shipment record. If inventory decrements but payment fails, you've oversold. If payment succeeds but shipment record is lost, customer gets nothing.

Health records demand both consistency and durability. A lab result update must satisfy all constraints (valid patient ID, proper value ranges) and survive hardware failure. Partial updates could mean wrong diagnosis. The database must stay correct through power outages, disk failures, and network partitions.

For everything else, there's BASE (Basically Available, Soft state, Eventual consistency). Know which camp your data lives in.

// io.thecodeforge — cs-fundamentals tutorial

// Simplified e-commerce transaction with rollback
import sqlite3

conn = sqlite3.connect("shop.db")
cursor = conn.cursor()

try:
    cursor.execute("BEGIN TRANSACTION")
    
    # Decrement inventory
    cursor.execute("UPDATE inventory SET stock = stock - 1 WHERE sku = 'LAPTOP_2024'")
    if cursor.rowcount == 0:
        raise Exception("Product out of stock")
    
    # Charge customer — mock payment gateway
    cursor.execute("INSERT INTO orders (customer_id, sku, status) VALUES (42, 'LAPTOP_2024', 'pending')")
    
    conn.commit()  # All changes stick, or none do
    print("Order placed successfully")

except Exception as err:
    conn.rollback()  # Inventory goes back, no partial order
    print(f"Transaction failed: {err}")