SQL Transactions — Missing ROLLBACK Causes Empty Orders

Every meaningful application eventually hits the same wall: multiple things need to happen together or not at all. A bank transfer, an e-commerce order, a seat reservation — all of them involve at least two writes that must succeed or fail as a unit. Without a formal guarantee, your database becomes a minefield where a crashed server at exactly the wrong millisecond can leave your data in a half-written, corrupted state that's incredibly hard to detect and even harder to fix.

SQL transactions solve this by wrapping a group of operations in a protective envelope. The database engine commits to four iron-clad guarantees — Atomicity, Consistency, Isolation, and Durability — collectively called ACID. These aren't just theoretical ideals. They're enforced by real mechanisms: write-ahead logs, lock managers, MVCC, and buffer pool management.

Understanding how the engine actually implements these guarantees is what separates developers who use transactions from engineers who design systems that stay correct under load, failure, and concurrency.

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;

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

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

Frequently Asked Questions