Transactions and ACID Properties

The $500 Nightmare

Imagine you transfer $500 from your checking account to your savings account. The bank's system debits $500 from checking — then the server crashes. The debit happened. The credit never did. Your $500 simply vanished from the database.

This is not a hypothetical. Before transactions became standard, this exact scenario destroyed trust in digital banking. A transaction exists to prevent this nightmare. It bundles a series of database operations into a single, indivisible unit — either everything happens, or nothing does.

What Is a Transaction?

A transaction is a sequence of one or more SQL operations treated as a single logical unit of work. The database guarantees that the entire sequence either completes successfully or leaves the database exactly as it was before the transaction began.

Definition: A transaction is an atomic unit of database operations that transforms the database from one consistent state to another.

ACID Properties

Every reliable database engine enforces four guarantees, collectively known as ACID:

Atomicity — All or Nothing

Every operation in a transaction must succeed. If any single operation fails, the entire transaction is rolled back as if it never happened.

Analogy: A light switch. It is either ON or OFF — never halfway.

Consistency — Valid State to Valid State

A transaction must bring the database from one valid state to another valid state. All defined rules, constraints, and triggers must hold before and after the transaction.

Analogy: A chess game. After each move, the board must represent a legal position. You cannot leave pieces floating off the board.

Isolation — Transactions Don't Interfere

Concurrent transactions execute as if they were running serially — one after the other. An in-progress transaction's intermediate state is invisible to other transactions.

Analogy: Two people editing different chapters of the same book simultaneously. Neither sees the other's draft until it is published.

Durability — Committed Data Survives Crashes

Once a transaction is committed, the changes are permanent — even if the server loses power a millisecond later.

Analogy: Signing a contract. The ink doesn't fade if the lights go out.

SQL Transaction Syntax

-- Start a transaction
BEGIN;

-- Perform operations
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';

-- Save a checkpoint within the transaction
SAVEPOINT after_debit;

-- Commit all changes permanently
COMMIT;

-- Or undo everything if something goes wrong
ROLLBACK;

-- Roll back to a savepoint (partial undo)
ROLLBACK TO SAVEPOINT after_debit;

Bank Transfer: Full Working Example

BEGIN;

-- Debit account A
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 'A' AND balance >= 500;

-- Check if the debit actually happened
-- (Simulated check: in application code you verify rows affected = 1)

-- Credit account B
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 'B';

COMMIT;

If an error occurs anywhere:

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
-- Simulate a crash or constraint violation here

ROLLBACK;  -- Balance in A is restored; nothing was debited

Sample output after a successful transfer:

accounts table BEFORE:
 account_id | balance
------------+---------
 A          | 1000.00
 B          |  200.00

accounts table AFTER COMMIT:
 account_id | balance
------------+---------
 A          |  500.00
 B          |  700.00

How Durability Works: Write-Ahead Logging (WAL)

The database does not write changes directly to the data files on disk when you issue a statement. Instead, it first records the intent to change in a sequential Write-Ahead Log (WAL).

[ Transaction begins ]
    → WAL entry written: "Debit A by 500"
    → WAL entry written: "Credit B by 500"
[ COMMIT issued ]
    → WAL marked as committed (fast, sequential write)
[ Later: WAL replayed into actual data files ]

Why this matters for durability: If the server crashes after COMMIT but before the data files are updated, the database replays the WAL on restart and applies the changes. Your committed data is never lost.

ASCII: Transaction State Diagram

   [BEGIN]
      |
      v
  [ACTIVE] ---------> [FAILED]
      |                   |
      | (all ops ok)       | (error/crash)
      v                   v
 [PARTIALLY           [ABORTED]
  COMMITTED]         (rolled back)
      |
      | (write to WAL + disk)
      v
 [COMMITTED]
 (permanent)

ACID Properties Reference Table

Common Pitfalls

• Forgetting COMMIT: In many clients, a transaction stays open until explicitly committed or rolled back. Leaving it open holds locks and blocks other users.
• Long transactions: The longer a transaction runs, the more locks it holds. Keep transactions short and focused.
• Ignoring return values: Your application must check whether UPDATE/DELETE affected any rows before committing.

Key Takeaways

• A transaction bundles multiple operations into one atomic unit.
• ACID is the four-property guarantee that makes databases trustworthy.
• Atomicity prevents partial updates; Consistency enforces rules; Isolation separates concurrent work; Durability survives crashes.
• The Write-Ahead Log is the primary mechanism that delivers durability in production databases like PostgreSQL, MySQL, and SQL Server.
• Use BEGIN, COMMIT, ROLLBACK, and SAVEPOINT to manage transactions in SQL.

Remember: Every financial system, every e-commerce checkout, every reservation system in the world depends on ACID transactions. Mastering this concept is foundational to building reliable software.