Understanding Database Transactions and Isolation Through a Real Banking Example

1. The Account Transfer Scenario

Consider a classic banking example: transferring $100 from Alice's account (Balance: $500) to Bob's account (Balance: $200).

In an ideal world, the operation consists of two updates enclosed in a transaction:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
COMMIT;

If the server experiences a power loss after the first UPDATE but before the second, the database rolls back the transaction upon recovery. The money does not vanish. This is Atomicity.

However, what happens when Charlie simultaneously attempts to withdraw $450 from Alice's account while this transfer is executing? This is where Transaction Isolation comes into play.

2. The Four Standard ANSI SQL Isolation Levels

The SQL-92 standard defines four isolation levels based on three concurrency phenomena:

1. Dirty Read: Transaction B reads uncommitted modifications made by Transaction A.

2. Non-Repeatable Read: Transaction A re-reads a row and finds that values changed because Transaction B committed an update.

3. Phantom Read: Transaction A executes a range query (e.g., `WHERE balance > 100`), and Transaction B inserts a new matching row before Transaction A finishes.

+------------------+-------------+---------------------+--------------+
| Isolation Level  | Dirty Read  | Non-Repeatable Read | Phantom Read |
+------------------+-------------+---------------------+--------------+
| Read Uncommitted | Allowed     | Allowed             | Allowed      |
| Read Committed   | Prevented   | Allowed             | Allowed      |
| Repeatable Read  | Prevented   | Prevented           | Allowed*     |
| Serializable     | Prevented   | Prevented           | Prevented    |
+------------------+-------------+---------------------+--------------+
* Note: Modern PostgreSQL prevents phantom reads even at Repeatable Read via MVCC.

3. The Danger of Lost Updates in Default 'Read Committed'

Most developers assume that wrapping statements in `BEGIN ... COMMIT` guarantees safe concurrent execution. In PostgreSQL and Oracle, the default level is 'Read Committed'. Under this level, each statement sees a snapshot taken at the start of that statement, not at the start of the transaction.

This causes the infamous Lost Update bug when applications read a balance, compute the new balance in application memory, and write it back:

-- Thread 1 (Web Request A)        -- Thread 2 (Web Request B)
BEGIN;                             BEGIN;
SELECT balance FROM accounts;      SELECT balance FROM accounts;
-- Both see $500!                  -- Both see $500!

-- App computes 500 - 100 = 400    -- App computes 500 - 50 = 450
UPDATE accounts SET balance = 400; 
COMMIT;

                                   UPDATE accounts SET balance = 450;
                                   COMMIT;
-- Thread 2 overwrote Thread 1's deduction! Alice only lost $50 instead of $150.

4. The Architecture Choice for Real Products

For high-volume financial or inventory systems, relying solely on highest-level Serializable isolation can cause high abort rates due to lock contention. Conversely, leaving everything at default Read Committed without row-locking leads to data inconsistencies.

Careful engineering means choosing the right tool for the job: atomic SQL queries and row-level locks for hot accounts, and strict transaction isolation for critical multi-table balance audits.