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 a deliberately simplified failure scenario. Transaction processing is the mechanism that prevents this kind of partial database change. 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. A correctly implemented transaction lets you commit all its changes together or roll back uncommitted changes together. The application must still check business conditions, constraint failures, and retryable concurrency errors.

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

Isolation governs how concurrent transactions can observe and affect one another. The exact guarantee depends on the isolation level: for example, READ COMMITTED and SERIALIZABLE do not permit the same behaviours. SERIALIZABLE aims for an outcome equivalent to some serial ordering, but applications must be ready to retry an aborted transaction.

Analogy: Two checkout counters selling the last concert ticket. Without careful coordination, both might decide that the same seat is still available. Transaction isolation and proper constraints prevent invalid outcomes.

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.

Visual walkthrough: two accounts, one outcome

Original vector illustration by AiTechWorlds for this lesson. The diagram assumes no transfer fees or other concurrent changes.

Start with account A at $1,000 and account B at $200. Our invariant is that the two balances must still sum to $1,200 after a simple internal transfer. Think like an engineer: the goal is not merely to execute two SQL statements; the goal is to preserve the invariant even when something fails.

Build the laboratory in PostgreSQL

CREATE TABLE accounts (
  account_id TEXT PRIMARY KEY,
  balance NUMERIC(12, 2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts(account_id, balance)
VALUES ('A', 1000.00), ('B', 200.00);

Why use NUMERIC instead of a floating-point type? Financial quantities usually need exact decimal representation. Why a CHECK constraint? It makes the database reject negative balances even if an application forgets to validate.

Run the transfer: success path

BEGIN;

UPDATE accounts
SET balance = balance - 500.00
WHERE account_id = 'A' AND balance >= 500.00
RETURNING account_id, balance;

UPDATE accounts
SET balance = balance + 500.00
WHERE account_id = 'B'
RETURNING account_id, balance;

COMMIT;

Expected balances when both statements update exactly one row:

Crucial application rule: Successful execution of a conditional UPDATE does not prove that it changed a row. Your transaction handler must verify that the debit updated exactly one eligible account and the credit updated exactly one destination. If either affected-row check fails, issue ROLLBACK instead of COMMIT. A transfer service should also record a unique transfer ID for idempotent retries; wrapping statements in a transaction alone does not prevent processing the same request twice.

Failure path: watch the balance return

Reset the laboratory if you already committed the success example, or run the following as a new illustration with whatever current balance A holds.

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE account_id = 'A' AND balance >= 100.00;

-- Pretend payment validation later rejects the operation.
ROLLBACK;

SELECT account_id, balance FROM accounts ORDER BY account_id;

After the rollback, you see the same balances that existed immediately before this transaction began. The rejected operation did not permanently subtract $100.

A savepoint is not a second transaction

Imagine the destination was accidentally selected as B, then corrected to C within the same transaction. A savepoint can undo the later credit without undoing the earlier debit.

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE account_id = 'A';
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100.00 WHERE account_id = 'B';
ROLLBACK TO SAVEPOINT before_credit;
-- Only the credit to B is undone. Account C must already exist:
UPDATE accounts SET balance = balance + 100.00 WHERE account_id = 'C';
COMMIT;

This is a conceptual example requiring an existing C row and application-level validation of all affected-row counts. A savepoint is a partial undo point; COMMIT still decides the final outcome of the whole transaction.

Follow the concurrent checkout story

Two customers try to buy the last ticket. Session 1 reads one available seat; Session 2 reads the same value. If both applications make decisions based on stale reads, they can oversell. A transaction alone does not automatically fix every concurrency bug.

A typical PostgreSQL remedy is to acquire a row lock before checking and modifying shared inventory:

BEGIN;
SELECT available FROM event_inventory
WHERE event_id = 42 FOR UPDATE;
-- Application verifies available > 0 and then executes:
UPDATE event_inventory
SET available = available - 1
WHERE event_id = 42 AND available > 0
RETURNING available;
COMMIT;

The table is hypothetical, and the app must confirm that UPDATE returned a row. Alternative designs include atomic conditional UPDATE, database constraints, optimistic version checks, and SERIALIZABLE transactions with retries.

Choosing an isolation level

Note that isolation levels and observed behaviour vary across database engines. The SQL standard names the levels; specific MVCC and locking implementations differ.

Understand durability without memorising a misleading shortcut

When PostgreSQL commits, WAL is central to crash recovery. A committed record need not mean every modified data page is already rewritten. WAL must be made durable according to the engine's configuration. SQLite can instead use rollback-journal mode, or a different WAL mode. SQL Server and MySQL have their own logging and recovery details.

It helps to distinguish three questions: Did the transaction commit? Could concurrent work violate an invariant? Would committed data survive the specified crash? Those questions concern atomicity, isolation/consistency, and durability respectively.

How Durability Works: Write Ahead Logging (WAL)

In systems such as PostgreSQL, a write-ahead log (WAL) records enough change information durably before changed data pages are flushed. Other engines use different combinations of undo, redo, journal files, and logging. Do not assume every database engine has the same storage or crash-recovery design.

[ 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: With a correctly configured PostgreSQL instance, committed WAL records allow crash recovery even when updated data pages have not yet been flushed. Storage failures, unsafe settings, and missing backups are separate risks; a transaction is not a substitute for disaster recovery.

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.
• Crash recovery commonly relies on logs or journals, but the exact mechanism and durability guarantees depend on the database engine and its configuration.
• 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.

A second real-world story: placing an online order

You place an order for a laptop. A naive system first decrements stock, then creates the order record, then charges a payment provider. If the order insert fails after stock decreases, the product has disappeared from inventory with no valid order. A local database transaction can protect inventory + order row changes within one database. It cannot by itself atomically commit a separate third-party payment gateway call.

A robust design often uses an outbox pattern: in the same transaction, update the order and append a durable outbox event; a separate worker delivers the event to the payment service with idempotency safeguards. Now ask: what happens if delivery occurs twice? The recipient needs a stable event or transaction ID to deduplicate. This is why distributed transaction design involves more than writing BEGIN and COMMIT.

Practice laboratory: learn by predicting failure

1. Insert a third account C with $50. Transfer $25 from A to C, then use ROLLBACK. Predict all three balances before running SELECT.
2. Repeat the transfer, this time COMMIT. Show a before/after table and verify that the total balance is unchanged.
3. Replace the A debit predicate with a condition that matches zero rows. Why might COMMIT still succeed unless the application checks the number of updated rows?
4. Describe the race condition in the last-seat checkout story, and implement one possible solution with SELECT FOR UPDATE or a conditional UPDATE.
5. Write down a failure timeline for payment provider timeout after an order has been committed. Which part is an ACID transaction, and which part needs idempotency and recovery logic?

Check your understanding

Q1. A transfer debits account A, but the credit to B fails. What property requires the debit to be undone if the transaction is rolled back?

Answer: Atomicity. No partial transaction should become committed.

Q2. Does READ COMMITTED promise that the result of a SELECT will be unchanged if you repeat it later in the same transaction?

Answer: No. Another transaction can commit changes in between the two statements.

Q3. An UPDATE statement returns zero changed rows, but the SQL statement itself does not raise an error. Is the transfer safe to commit?

Answer: No. The application must verify the expected rows were changed.

Q4. Does a database transaction automatically make the charge at a separate card-payment provider atomic with the inventory update?

Answer: No. Cross-system workflows require explicit coordination, recovery, and idempotency design.

Authoritative references and visual attribution

• PostgreSQL 18 documentation: Transactions, BEGIN, COMMIT, ROLLBACK, and SAVEPOINT.
• PostgreSQL documentation: Transaction Isolation, SQL isolation levels and PostgreSQL-specific guarantees.
• SQLite documentation: Atomic Commit, an engine-specific explanation of atomicity and journaling.
• Diagram: AiTechWorlds original SVG, /course-visuals/dbms-transaction-flow.svg. No third-party illustration licence is required for this newly authored asset.

Review checklist: Can the learner explain the invariant, predict rollback results, distinguish SQL success from business success, diagnose a concurrency anomaly, and describe one distributed-system failure? If not, the lesson is not yet mastered.