ACID Properties and Transaction Management

Introduction

A transaction is a group of database operations that must either complete fully or not happen at all. It ensures that even if something goes wrong (like a crash or error), the database remains correct and reliable. This reliability is guaranteed by the ACID properties — Atomicity, Consistency, Isolation, and Durability. ACID is what makes databases trustworthy for financial systems, medical records, and any application where data integrity is non-negotiable.

Explanation

Transactions solve the problem of partial failure — if a bank transfer involves debiting one account and crediting another, a crash between the two operations cannot leave money vanished or duplicated. The ACID properties together guarantee this never happens.

Without transactions, systems can easily enter inconsistent states due to crashes, concurrency, or partial failures.Transactions prevent partial updates — ensuring data is never left in a broken or inconsistent state.

Real World Example

When you book a flight online: the system reserves your seat, charges your credit card, sends a confirmation email, and updates the flight manifest. If the credit card charge fails, the seat reservation must roll back. If the server crashes after charging your card but before sending the email, the charge must still be recorded. Transactions wrapped around the entire booking process guarantee either the full booking succeeds or nothing changes.

Technical Breakdown

The four ACID properties in detail:

• Atomicity(All or Nothing): A transaction is all-or-nothing. Either all operations succeed (COMMIT) or none do (ROLLBACK). Implemented via the transaction log — the DBMS can undo all changes made by a transaction that fails mid-way.

• Consistency(Valid State): A transaction must take the database from one valid state to another valid state. All defined constraints, triggers, and rules must hold after the transaction commits. If any constraint would be violated, the transaction rolls back.

• Isolation(No Interference): Concurrent transactions execute as if they were running serially. One transaction's intermediate (uncommitted) state is invisible to other concurrent transactions. Implemented through locking or MVCC (Multi-Version Concurrency Control).

• Durability(Permanent Changes): Once a transaction commits, its changes are permanent — even if the server crashes immediately after. Implemented via write-ahead logging (WAL): changes are written to a durable log before being applied to data files.

[ACID Transaction Flow]

Prompt: Clean educational diagram showing a bank transfer transaction flow. Two boxes at top: Account A (debit 500) and Account B (credit 500). Below: a decision diamond "All operations succeed?" with two paths — YES leads to COMMIT box (green), NO leads to ROLLBACK box (red) that reverses all changes back to initial state. Shows the atomicity concept clearly. White background, labeled arrows, minimal educational style.

Example

-- Bank transfer transaction
BEGIN;

-- Debit sender
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 'ACC001'
  AND balance >= 5000;  -- check sufficient funds

-- Check if debit succeeded
-- (Application-level check; in SQL use a condition)
IF ROW_COUNT() = 0 THEN
    ROLLBACK;  -- Insufficient funds
END IF;

-- Credit receiver
UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 'ACC002';

-- Record the transfer
INSERT INTO transfer_log (from_account, to_account, amount, transfer_date)
VALUES ('ACC001', 'ACC002', 5000, CURRENT_TIMESTAMP);

COMMIT;  -- All three operations succeed: make permanent

-- SAVEPOINT example for partial rollback
BEGIN;
    INSERT INTO orders (...) VALUES (...);
    SAVEPOINT after_order;

    INSERT INTO payments (...) VALUES (...);  -- might fail

    -- If payment fails, rollback only the payment part
    -- ROLLBACK TO SAVEPOINT after_order;
    -- (order is preserved, re-try payment)
COMMIT;

Common Mistakes

• Opening transactions but never committing — long-running open transactions hold locks, blocking other transactions and degrading performance.

• Using transactions for read-only queries — transactions are primarily for write operations that must be atomic; wrapping SELECTs adds overhead without benefit.

• Catching exceptions but not rolling back — if your application catches a database error during a transaction, it must explicitly ROLLBACK before the next operation.

Interview Tips

ACID is a guaranteed interview topic for any backend or database role. Be able to give a concrete example for each property (not just the definition), and explain how each is implemented technically (atomicity via undo log, durability via WAL/redo log). Also know that distributed systems often sacrifice C (consistency) or A (availability) for partition tolerance — this is the CAP theorem, which demonstrates deep understanding.