Transactions & Concurrency: Transaction States and ACID Properties

The Story That Explains a Transaction

A transaction is a logical unit of work that accesses and possibly modifies the contents of a database through a sequence of read and write operations. Crucially, a transaction is all-or-nothing: the database must never be left in a half-finished state where some operations applied and others did not.

Transaction Concepts & Operations

Every transaction is built from a small set of low-level operations. Understanding these is the foundation for every practice problem that follows.

The Classic Fund-Transfer Transaction

Transaction T transfers ₹5,000 from account A to account B. Here it is written in SQL:

BEGIN TRANSACTION;

-- Step 1: take money out of A
UPDATE accounts SET balance = balance - 5000
WHERE acc_id = 'A';

-- Step 2: put money into B
UPDATE accounts SET balance = balance + 5000
WHERE acc_id = 'B';

-- Both succeeded -> make it permanent
COMMIT;

Expressed as abstract read/write operations, the same transaction looks like this:

Transaction States & The State Diagram

During its lifetime, a transaction passes through a fixed set of states. Knowing the exact order — and which transitions are legal — is one of the most commonly tested topics in exams.

The ACID Properties

ACID is the set of four guarantees a DBMS provides so that transactions remain reliable even with concurrency and system failures. Memorise the acronym: Atomicity, Consistency, Isolation, Durability.

Which Property Fails? — Before / After

The fastest way to truly understand ACID is to see what breaks when a property is missing. Using the fund-transfer (A→B of ₹5,000, starting A=10000, B=2000):

On the left, ₹5,000 vanished (total dropped from 12000 to 7000) — both Atomicity and Consistency were violated. On the right, rollback restored the original consistent state. Total stays 12000 either way it should.

Practice Problems (Fully Solved)

Problem 1 — Identify the Violated Property

A transaction successfully commits and the system confirms it. Five minutes later the server loses power. On reboot, the committed changes are gone. Which ACID property was violated?

Problem 2 — The Lost Update (Isolation)

Two transactions run concurrently on the same item X = 100. Both read X, add 10, and write back, with operations interleaved as shown:

Expected final value: 120 (two increments of 10). Actual: 110. What problem is this, and which property prevents it?

Problem 3 — Legal or Illegal State Transition?

For each transition, decide whether it is a valid transaction state change:

Problem 4 — Why Does This Transaction Need Atomicity AND Durability?

An e-commerce checkout (1) deducts stock, (2) charges the card, (3) creates an order. The card charge at step 2 is rejected. What should happen, and which properties are at work?

Rapid-Fire Concept Check

Cover the right column and test yourself. These match the most frequently asked one-liners.

Golden Rules