Database Transactions

When people talk about transactions, ACID usually comes up: Atomicity, Consistency, Isolation, and Durability. In this guide, we will define what a transaction is, walk through a real transfer example, and then map that example to each ACID property.

What is a Transaction?

According to Fundamentals of Database Systems by Elmasri, a transaction is an executing program that forms a logical unit of database processing. It can include one or more operations: reads, inserts, updates, or deletes.

A transaction is an atomic unit of work that should either be completed in its entirety or not done at all.

A transaction is a sequence of database operations treated as one unit of work.

Why treat multiple operations as a single unit? Because many real actions cannot be expressed as one query. Banking transfers, ticket reservations, and online purchases all require multiple steps that must succeed together.

Setting the Stage

Let's set up a simple accounts table to use throughout this post:

CREATE TABLE accounts (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
);
 
INSERT INTO accounts (name, balance) VALUES
    ('You',  6900.00),
    ('Mom',   420.00);

Notice the CHECK (balance >= 0) constraint — it prevents any account from going negative. This becomes important later.

The Transfer in SQL

You want to send $1000 to your mom for the holiday. You open your phone, enter the amount, and tap send. Here's what the database does:

BEGIN;
 
-- Step 1: Verify sufficient funds
SELECT balance FROM accounts WHERE name = 'You';
-- Returns: 6900.00  (6900 >= 1000, proceed)
 
-- Step 2: Debit sender
UPDATE accounts SET balance = balance - 1000 WHERE name = 'You';
 
-- Step 3: Credit receiver
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Mom';
 
COMMIT;

-- Click play or step forward to begin

From the flow above, two points matter most:

1. BEGIN and COMMIT mark the transaction boundary (its lifespan).
2. Transactions are atomic — if both UPDATE operations do not succeed, we roll back.

Transactions do not have to write data. Read-only transactions also exist.

Try it yourself — step through the transfer and see how each operation changes the balances:

Now let's connect this example to each ACID property.

Atomicity

Atomicity means all-or-nothing. A transaction either completes fully or has no effect.

If a transaction fails to complete (for example, due to a crash or query error), every earlier change inside that transaction must be undone through rollback.

Consider what happens without atomicity. Your account is debited $1000, then the database crashes before the credit:

BEGIN;
 
UPDATE accounts SET balance = balance - 1000 WHERE name = 'You';
-- Balance is now 5900...
 
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Mom';
-- ⚠ CRASH HERE — this never executes
 
-- No COMMIT was recorded
ROLLBACK;  -- database automatically rolls back on recovery
-- Both balances return to their original values

You would lose $1000 and your mom would receive nothing. Fortunately, the database rolls everything back.

The Transaction Log

How does the database know what to undo? Through a transaction log that records every operation:

-- What the transaction log records:
-- LSN 101: BEGIN TXN #42
-- LSN 102: UPDATE accounts SET balance=5900 WHERE id=1  (old: 6900)
-- LSN 103: *** CRASH — no COMMIT recorded ***
--
-- Recovery: scan log → TXN #42 has no COMMIT
--           UNDO: restore accounts.id=1.balance to 6900

The DBMS records enough information to undo incomplete work or complete committed work during recovery. Database engines handle that mechanism, but application code must still define correct transaction boundaries.

Consistency

Consistency means moving the database from one valid state to another valid state. A successful transaction must respect constraints and rules — checks, foreign keys, triggers, and application invariants.

Back to our transfer: what if you try to send $10,000 when you only have $6,900?

BEGIN;
 
UPDATE accounts SET balance = balance - 10000 WHERE name = 'You';
-- ERROR: new row for relation "accounts" violates
--        check constraint "accounts_balance_check"
-- Detail: Failing row contains (1, 'You', -3100.00)
 
ROLLBACK;  -- constraint violation forces rollback

The CHECK (balance >= 0) constraint catches this. The balance would become -$3,100, which violates the rule, so the entire transaction fails.

Total Money is Conserved

A valid transfer should never create or destroy money:

-- Before transfer:
SELECT SUM(balance) FROM accounts;
-- Returns: 7320.00
 
-- After a valid $1000 transfer:
SELECT SUM(balance) FROM accounts;
-- Returns: 7320.00  (unchanged!)
 
-- If this sum ever changes, something is broken.

This is an invariant — a property that must always hold true. Consistency guarantees that every committed transaction preserves all invariants.

Consistency extends beyond data integrity. With replicas, reading from a replica before it catches up can return stale data — an inconsistency at the system level.

Isolation

Isolation means each transaction should behave as if it were running alone, even when many transactions execute at the same time.

A transaction should appear as though it is being executed in isolation from other transactions, even though many transactions are executing concurrently.

Atomicity, Consistency, and Durability describe a single transaction. Isolation describes how concurrent transactions interact.

In SQL, isolation is controlled through four levels, each allowing or preventing specific anomalies:

Dirty Read

A dirty read happens when one transaction reads uncommitted data from another:

-- Transaction A:                    Transaction B:
BEGIN;
UPDATE accounts SET balance = 5900
  WHERE name = 'You';
                                     BEGIN;
                                     SELECT balance FROM accounts
                                       WHERE name = 'You';
                                     -- Reads 5900 (uncommitted!)
ROLLBACK;
                                     -- B used 5900,
                                     -- but the real value is 6900
                                     COMMIT;

Transaction B read a value that was never committed. When A rolls back, B's data becomes invalid.

Non-Repeatable Read

A non-repeatable read occurs when the same row returns different values within one transaction:

-- Transaction A:                    Transaction B:
BEGIN;
SELECT balance FROM accounts
  WHERE name = 'You';
-- Returns: 6900
                                     BEGIN;
                                     UPDATE accounts SET balance = 5900
                                       WHERE name = 'You';
                                     COMMIT;
SELECT balance FROM accounts
  WHERE name = 'You';
-- Returns: 5900 (different!)
COMMIT;

Transaction A read the same row twice and got two different answers.

Phantom Read

A phantom read happens when a query returns different row counts because another transaction inserted or deleted rows:

-- Transaction A:                    Transaction B:
BEGIN;
SELECT COUNT(*) FROM accounts
  WHERE balance > 1000;
-- Returns: 1
                                     BEGIN;
                                     INSERT INTO accounts (name, balance)
                                       VALUES ('Dad', 3000);
                                     COMMIT;
SELECT COUNT(*) FROM accounts
  WHERE balance > 1000;
-- Returns: 2 (a phantom row appeared!)
COMMIT;

The row set changed between identical queries — a "phantom" appeared.

Isolation Levels

Each SQL isolation level prevents a different combination of these anomalies:

Watch these anomalies happen in real time — step through two concurrent transactions to see exactly where things go wrong:

Isolation is a deep topic. Different databases implement these levels differently (PostgreSQL uses MVCC, MySQL uses locking). If you want the formal model, read the serializability chapter in Elmasri (Chapter 21).

Durability

Changes applied by a committed transaction must persist. They must not be lost because of any failure. This often means writing to disk, which is slower, though some systems use in-memory storage with snapshots (for example, Redis).

Different databases use different mechanisms (WAL, AOF, snapshots). The most common foundation in relational systems is the Write-Ahead Log (WAL).

WAL in Action

With WAL, the DBMS writes a description of the changes to disk before updating the actual data files:

-- What happens during: UPDATE accounts SET balance = 5900 WHERE id = 1;
--
-- 1. Write to WAL (sequential, fast):
--    LSN 201: TXN #55 UPDATE accounts id=1 balance: 6900 → 5900
--
-- 2. Modify page in buffer pool (memory):
--    Page 42, slot 1: balance = 5900  [dirty page]
--
-- 3. On COMMIT:
--    LSN 202: TXN #55 COMMIT
--    fsync() WAL to disk  ← this is the durability guarantee
--
-- 4. Later (checkpoint):
--    Write dirty page 42 to data file on disk

The key insight: the WAL fsync is the moment durability is guaranteed. Everything before that is at risk. Everything after is safe.

Recovery: Roll-Back vs Roll-Forward

During recovery, the database scans the WAL and applies two operations:

-- After crash, recovery scans the WAL:
--
-- UNDO (roll back): Transactions WITHOUT a COMMIT record
--   Found: TXN #56 — BEGIN ... UPDATE ... (no COMMIT)
--   Action: Reverse the UPDATE, restore old value
--
-- REDO (roll forward): Transactions WITH a COMMIT but data not yet on disk
--   Found: TXN #55 — BEGIN ... UPDATE ... COMMIT
--   Action: Re-apply the UPDATE to the data file

See this in action — select a crash scenario and watch the database recover:

Backups remain essential. Durability is stronger when logging and backup strategy are both in place.

Try It Yourself

Now that you understand the theory, experiment with real SQL. This playground lets you run transactions against a sample database:

Conclusion

We covered the core transaction model and how ACID protects correctness under failure and concurrency:

• Atomicity: All or nothing. The transaction log enables rollback of incomplete work.
• Consistency: Valid state to valid state. Constraints and invariants are preserved.
• Isolation: Concurrent transactions don't interfere. Isolation levels trade safety for performance.
• Durability: Committed data survives crashes. WAL ensures this by writing to disk before acknowledging.

Databases provide the machinery, but good system behavior still depends on defining the right transaction boundaries in application code.

Resources

• Hussein Nasser's YouTube channel. This post was inspired by his database engineering videos. His deep dives into transactions, isolation levels, and internals are outstanding.
• Fundamentals of Database Systems, 7th edition by Elmasri and Navathe
• CMU Intro to Database Systems. Andy Pavlo's full lecture series covering transactions, concurrency control, and recovery in depth.
• PostgreSQL Documentation on Transactions. The official docs are surprisingly readable and cover isolation levels with practical examples.
• SQL Performance Explained. Markus Winand's free online guide to indexing and query performance, with a strong chapter on transaction isolation.