SQL Transactions — Missing ROLLBACK Causes Empty Orders

A SELECT that returns the wrong customer's invoice. A funds transfer that vanishes into thin air. That's what you get without understanding ACID. SQL transactions are the engine's contract with you—atomic commits, durable logs, and isolation guarantees that prevent phantom orders and corrupted state. Ignore them, and your production database becomes a minefield of lost data, deadlocked sessions, and unkillable long transactions that bring everything to a crawl.

Why Missing ROLLBACK Creates Phantom Orders

SQL transactions group multiple operations into a single atomic unit. The core mechanic: either all operations commit permanently, or none do — the database rolls back to the prior consistent state. This is the 'A' in ACID (Atomicity). Without explicit ROLLBACK on failure, partial writes persist, corrupting data integrity.

Transactions enforce isolation (the 'I') so concurrent operations don't see intermediate states. In practice, this means two simultaneous order inserts won't interleave: one transaction's uncommitted rows are invisible to others. Durability ('D') ensures committed data survives crashes — typically via write-ahead logging. Consistency ('C') guarantees that constraints (e.g., foreign keys, unique indexes) hold before and after.

Use transactions for any multi-step write: transferring funds, placing orders, updating inventory. Real systems fail when a script inserts order headers but skips ROLLBACK on line-item failure — leaving orphaned headers that break reporting and billing. Always wrap multi-statement writes in BEGIN/COMMIT/ROLLBACK blocks, and test the failure path.

Atomicity and Durability — The Write-Ahead Log Is the Real Hero

Atomicity means all-or-nothing. Either every statement in a transaction lands in the database, or none of them do. But how does a database engine actually enforce this when the server can die at any microsecond?

The answer is the Write-Ahead Log (WAL). Before any data page is modified in memory, the engine writes an intent record to the WAL — an append-only file on disk. If the server crashes mid-transaction, the WAL lets the engine replay or undo operations on restart. This is called crash recovery, and it's what makes Atomicity a real guarantee rather than a hopeful suggestion.

Durability is the flip side: once you get a COMMIT acknowledgement, the data is safe even if the server immediately crashes. That acknowledgement is only sent after the WAL record is flushed to disk (fsync). This is why COMMIT can feel slightly slow — it's waiting on a real disk write, not just a memory operation. Disabling fsync breaks Durability entirely and is a production disaster waiting to happen.

-- Transfer $500 from Alice to Bob — both debit and credit must succeed together
BEGIN;

UPDATE accounts
   SET balance = balance - 500
 WHERE account_id = 'alice_001'
   AND balance >= 500;

DO $$
BEGIN
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds for alice_001';
    END IF;
END;
$$;

UPDATE accounts
   SET balance = balance + 500
 WHERE account_id = 'bob_002';

INSERT INTO transfer_audit (from_account, to_account, amount, transferred_at)
VALUES ('alice_001', 'bob_002', 500.00, NOW());

-- COMMIT waits for WAL fsync — this is what makes it durable
COMMIT;

ACID Pipeline — How a Transaction Flows Through the Engine

Understanding ACID in production means knowing the exact sequence of steps the database executes from BEGIN to COMMIT. The pipeline consists of five phases:

1. Transaction Start: The database assigns a transaction ID (XID) and records the start in shared memory structures. For PostgreSQL, the snapshot data for MVCC is initialized.
2. Statement Execution: Each SQL statement is parsed, planned, and executed. Data pages are read from the buffer pool (or disk if not cached). Modifications are applied to pages in memory, and WAL records are written to the WAL buffer in shared memory.
3. Pre-commit: On COMMIT, the database writes a commit record to the WAL buffer. This record contains the transaction ID and a list of all data pages modified.
4. WAL Flush (fsync): The WAL buffer is flushed to disk. This is the blocking step — the database waits for the OS to confirm the write reached stable storage. Only after this does the transaction become durable.
5. Post-commit: The transaction's XID is marked as committed in the commit log (pg_clog or system table). Locks held by the transaction are released. The COMMIT acknowledgement is sent to the client. The actual data pages may still be in memory — they will be written to disk later by the background writer.

The pipeline is the same across PostgreSQL, MySQL InnoDB, and Oracle, with minor differences in WAL format and commit log storage.

Isolation Levels — Where ACID Gets Complicated and Performance Gets Real

Isolation is the trickiest ACID property because it's not binary — it's a spectrum. 'Full' isolation (Serializable) means transactions behave as if they ran one after another, but it's expensive. Most databases default to something weaker, and that's where bugs hide.

Read Uncommitted — a transaction can read rows another transaction modified but hasn't committed yet (dirty reads). Almost never appropriate.

Read Committed — you only see committed data, but the same SELECT inside one transaction can return different rows if another transaction commits between them (non-repeatable reads). PostgreSQL and SQL Server's default.

Repeatable Read — the same SELECT always returns the same rows within a transaction, but new rows inserted by other transactions may appear (phantom reads). MySQL InnoDB's default.

Serializable — full isolation. No dirty reads, non-repeatable reads, or phantom reads. PostgreSQL uses Serializable Snapshot Isolation (SSI); MySQL InnoDB uses gap locks.

The key insight: lower isolation = more concurrency = more anomaly risk. Choose deliberately.

-- Demonstrating non-repeatable read at READ COMMITTED
-- and how REPEATABLE READ prevents it
-- Run Session A and Session B in two separate connections

-- SESSION A
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- First read: sees stock_count = 10
SELECT product_name, stock_count FROM product_inventory WHERE product_id = 1;

-- *** Switch to Session B and commit its update ***

-- Second read:
-- READ COMMITTED: returns 3 (Session B's commit is visible)
-- REPEATABLE READ: returns 10 (snapshot taken at transaction start)
SELECT product_name, stock_count FROM product_inventory WHERE product_id = 1;
COMMIT;

-- SESSION B (runs while Session A is open)
BEGIN;
UPDATE product_inventory SET stock_count = stock_count - 7 WHERE product_id = 1;
COMMIT;

-- Check your current isolation level
SHOW transaction_isolation;  -- PostgreSQL
SELECT @@transaction_isolation;  -- MySQL

Isolation Levels Comparison Matrix (ANSI Standards)

The SQL standard defines four isolation levels based on which concurrency phenomena they prevent. The matrix below maps each level to the three classic anomalies plus a fourth that some standards include:

Dirty Read: Reading a row that has been modified by another transaction that has not yet committed. If that transaction later rolls back, the reader has seen uncommitted (and now invalid) data.

Non-Repeatable Read: The same query returns different rows within the same transaction because another transaction committed an update or delete in between.

Phantom Read: A transaction re-executes a query with a WHERE condition and finds new rows that were inserted by another committed transaction. The set of rows changes (like a phantom appearing).

Serialization Anomaly: The result of concurrent transactions is not equivalent to any serial (one-after-another) execution. Serializable prevents all anomalies.

In practice, most databases default to READ COMMITTED (PostgreSQL, SQL Server) or REPEATABLE READ (MySQL InnoDB). Know your database's default and choose intentionally.

Concurrency Phenomena Quick-Reference Table

Beyond dirty reads, non-repeatable reads, and phantom reads, there are other phenomena that affect correctness under concurrent transactions. This table covers the full set a working developer needs to recognize:

Understanding these phenomena is critical when debugging production issues like phantom stock counts or double-booking. Each isolation level and concurrency control mechanism targets a subset of these.

Consistency, Savepoints and Long Transaction Dangers in Production

Consistency is the ACID property that's most misunderstood. It doesn't mean 'the data is correct' in a business logic sense — that's your application's job. What it means is that every transaction takes the database from one valid state to another, where 'valid' is defined by schema constraints: foreign keys, CHECK constraints, UNIQUE indexes, and NOT NULL columns.

SAVEPOINTs extend this by letting you create partial rollback points inside a long transaction. Instead of aborting everything on a recoverable error, you can roll back to a named savepoint and retry just that sub-operation. This is invaluable for bulk import scripts and multi-step wizards.

But long-running transactions are expensive regardless of what they're doing. In PostgreSQL, an open transaction holds back autovacuum from reclaiming dead rows, causing table bloat. In MySQL InnoDB, long transactions hold undo log space. The rule: keep transactions as short as possible. Do computation before opening BEGIN, not inside it.

-- Bulk import with per-row error recovery using SAVEPOINTs
BEGIN;

SAVEPOINT before_emp_1;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Priya Sharma', 1, 95000.00);  -- valid, dept_id 1 exists

SAVEPOINT before_emp_2;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Carlos Mendez', 999, 80000.00);  -- FK violation: dept_id 999 missing
-- Caught at application layer, roll back only this insert
ROLLBACK TO SAVEPOINT before_emp_2;
INSERT INTO import_error_log (attempted_name, reason, logged_at)
VALUES ('Carlos Mendez', 'dept_id 999 not found', NOW());

SAVEPOINT before_emp_3;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Aisha Okonkwo', 2, 72000.00);  -- valid

SAVEPOINT before_emp_4;
INSERT INTO employees (full_name, dept_id, salary)
VALUES ('Tom Briggs', 1, -5000.00);  -- CHECK violation: salary > 0
ROLLBACK TO SAVEPOINT before_emp_4;
INSERT INTO import_error_log (attempted_name, reason, logged_at)
VALUES ('Tom Briggs', 'salary must be positive', NOW());

COMMIT;  -- Priya and Aisha inserted; Carlos and Tom logged as errors

SAVEPOINTs for Complex Business Logic — Nested Error Recovery Patterns

When business logic requires multiple steps that each have their own recovery path, SAVEPOINTs enable a nested error handling pattern that preserves the entire transaction's progress. This is especially useful in scenarios like:

• Batch processing with per-record error handling: Process all records in one transaction; if one record fails (uniqueness, FK violation), log the error and continue with the next record. Without SAVEPOINTs, the entire batch would roll back.
• Multi-step financial workflows: A payment capture, then a loyalty points update, then an inventory deduction. If the points update fails (e.g., connection timeout to a remote service), you want to roll back only the points step and retry, not the entire payment.

• Compound validation: Validate a set of business rules one by one, rolling back each invalid step without losing earlier valid work. At the end, commit only if all sub-validations passed.

A typical pattern in Java/JDBC or Python/psycopg2:

``java try { connection.setAutoCommit(false); // Step 1: create order Savepoint sp1 = connection.setSavepoint("order_created"); insertOrder(); // Step 2: add line items Savepoint sp2 = connection.setSavepoint("items_added"); insertLineItems(); // If line items fail, rollback to sp2 and log } catch (SQLException e) { connection.rollback(sp2); // rollback only items logError(e); // retry step 2 or continue } finally { connection.releaseSavepoint(sp2); } ``

Note: SAVEPOINTs have a memory cost — each savepoint retains a snapshot of the transaction state. For large batch sizes, release savepoints as soon as their work is finalized.

Atomicity Failure: The Partial Write That Burns You at 2 AM

Every dev thinks they understand atomicity until a power loss hits mid-insert and they're staring at orphaned invoice headers with no line items. Atomicity isn't a feature — it's a contract. The database promises that your transaction either fully commits or fully rolls back. No half-state. Ever. The mechanism? Write-ahead logs and undo segments. Before the engine touches a data page, it logs the 'before' image. If the transaction aborts — whether from a CHECK constraint violation or a dropped network packet — the engine replays those images to restore the original state. Here's what most junior devs miss: atomicity costs you. Every write generates log traffic. Every rollback requires reading those logs back. Watch your log buffer sizes and disk I/O latency. If you're pushing 10k row updates in a single transaction, you're betting the hardware won't blink. And in production, hardware blinks.

// io.thecodeforge — database tutorial

-- Partial insert that violates FK mid-transaction
BEGIN TRANSACTION;

INSERT INTO invoice_headers (id, customer_id, total)
VALUES (5001, 2047, 1500.00);

-- This insert fails: customer 2047 doesn't exist as supplier
INSERT INTO invoice_line_items (invoice_id, sku, qty, price)
VALUES (5001, 'WIDGET-A', 10, 150.00);

-- ERROR: insert or update on table violates foreign key constraint

-- Transaction rolls back automatically on error
-- Both inserts are discarded. Zero partial writes.

Durability: The Lie of fsync and What Actually Survives a Power Pull

Durability sounds simple: once COMMIT returns, data stays. That's marketing. In reality, durability depends on the write-ahead log flushing to persistent storage before the commit acknowledgment reaches your client. PostgreSQL does this by default — fsync on every commit. MySQL with InnoDB flushes the redo log. But here's the catch: hardware lies. Disk controllers cache writes in volatile RAM. RAID cards lie about flushing. Cloud SSDs throttle under load and report success before bits hit silicon. Saw a production incident where a RAID controller battery failed silently. Commits returned OK. Server crashed. Lost four minutes of transaction data. The fix? Disable disk write cache, enable write-back barriers, and for critical systems, use synchronous replication to at least two nodes. Test your durability by pulling the plug on a staging server. Read the survivor's guide: synchronous_commit = on in Postgres, innodb_flush_log_at_trx_commit = 1 in MySQL.

// io.thecodeforge — database tutorial

-- How to verify your durability settings in PostgreSQL
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
    'fsync',
    'synchronous_commit',
    'wal_sync_method',
    'full_page_writes'
);

-- Expected output for guaranteed durability:
-- fsync                 | on
-- synchronous_commit    | on
-- wal_sync_method       | open_datasync | fdatasync
-- full_page_writes      | on

Transaction Isolation Levels in Practice: Repeatable Read vs Serializable

While the ANSI SQL standard defines four isolation levels, the practical differences between Repeatable Read and Serializable often cause confusion. Repeatable Read ensures that within a transaction, all reads see a consistent snapshot from the start of the transaction. However, it does not prevent phantom reads—new rows inserted by other transactions that match the WHERE clause of a query. Serializable, the strictest level, prevents phantoms by acquiring range locks or using predicate locking, effectively serializing transactions. In PostgreSQL, Repeatable Read is implemented using snapshot isolation (MVCC), which actually prevents phantoms for most operations, making it functionally equivalent to Serializable in many cases. However, PostgreSQL's Serializable uses Serializable Snapshot Isolation (SSI), which detects serialization anomalies via conflict detection and aborts transactions if needed. In practice, Repeatable Read is sufficient for most applications where phantom reads are acceptable, but for financial systems or inventory management, Serializable is recommended to avoid anomalies like write skew. Example: Consider two transactions that read the count of items in stock and then update it. Under Repeatable Read, both may see the same initial count, leading to lost updates. Serializable would detect the conflict and abort one transaction.

-- Session A (Repeatable Read)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 1; -- returns 10
-- Session B concurrently inserts a new row for product_id=1 with quantity=5
-- Session A re-runs the query
SELECT quantity FROM inventory WHERE product_id = 1; -- still returns 10 (no phantom)
COMMIT;

-- Serializable example (PostgreSQL)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 1; -- returns 10
-- If another transaction modifies the same range, this transaction may abort
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
COMMIT; -- may fail with serialization error

Distributed Transactions: XA, Sagas, Two-Phase Commit

Distributed transactions coordinate multiple databases or services to ensure atomicity across a network. The classic approach is the Two-Phase Commit (2PC) protocol, often implemented via XA (eXtended Architecture). In 2PC, a coordinator asks all participants to prepare (phase 1), and if all agree, commits (phase 2). While 2PC guarantees atomicity, it suffers from blocking: if the coordinator crashes after prepare, participants may hold locks indefinitely. XA is supported by many databases (e.g., Oracle, MySQL, PostgreSQL via pg_xa) but is often avoided in microservices due to performance and availability issues. An alternative is the Saga pattern, which breaks a distributed transaction into a sequence of local transactions with compensating actions. Sagas are eventually consistent and non-blocking, but require careful design of rollback logic. For example, an order saga might: reserve inventory, charge payment, and ship. If payment fails, the saga triggers a compensation to release inventory. Sagas can be orchestrated (central coordinator) or choreographed (each service emits events). In practice, 2PC is suitable for short-lived, low-latency operations within a trust boundary, while Sagas are preferred for long-running business processes across heterogeneous systems.

-- Saga: Order creation with compensating transactions
-- Step 1: Reserve inventory (local transaction)
BEGIN;
UPDATE inventory SET reserved = reserved + 1 WHERE product_id = 1;
INSERT INTO order_items (order_id, product_id, status) VALUES (100, 1, 'reserved');
COMMIT;

-- Step 2: Process payment (if fails, run compensation)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;
-- If payment fails:
-- Compensation: release inventory
BEGIN;
UPDATE inventory SET reserved = reserved - 1 WHERE product_id = 1;
UPDATE order_items SET status = 'cancelled' WHERE order_id = 100;
COMMIT;

Snapshot Isolation: PostgreSQL SSI vs SQL Server Snapshot

Snapshot Isolation (SI) is a concurrency control mechanism that gives each transaction a consistent snapshot of the database at its start time. Both PostgreSQL and SQL Server implement SI, but with key differences. PostgreSQL uses Multiversion Concurrency Control (MVCC) to provide SI at the Repeatable Read isolation level, but it does not prevent all serialization anomalies (e.g., write skew). To achieve true serializability, PostgreSQL offers Serializable Snapshot Isolation (SSI), which tracks read-write conflicts and aborts transactions that would cause non-serializable behavior. SQL Server's Snapshot Isolation level is similar to PostgreSQL's Repeatable Read: it prevents dirty reads and non-repeatable reads, but allows phantoms and write skew. SQL Server also offers Serializable isolation, which uses locking rather than SI. The practical difference: PostgreSQL's SSI is more optimistic—it allows more concurrency but may abort transactions under contention. SQL Server's Serializable uses pessimistic locking, which can reduce concurrency but avoids aborts. For example, a write skew scenario: two transactions read the same set of rows and then update disjoint subsets. Under SI, both commit, violating serializability. PostgreSQL SSI detects this and aborts one; SQL Server Snapshot allows it, but Serializable would block one transaction.

-- PostgreSQL SSI (write skew detection)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM doctors WHERE on_call = true; -- sees two doctors
-- Another transaction sets both to off_call
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT; -- may fail with 'could not serialize access'

-- SQL Server Snapshot (allows write skew)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN;
SELECT * FROM doctors WHERE on_call = true; -- sees two doctors
-- Another transaction sets both to off_call
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT; -- succeeds, but now no doctors on call