DSA, Database System & Operating System — Transaction Processing, Concurrency Control & Recovery, NEC licence examination syllabus (Nepal Engineering Council).
ACID Properties
The four guarantees that let you treat a multi-step operation as if it were one indivisible act.
🌍 Where this lives: ACID is why you can transfer money without the bank losing it. Debit one account, credit another — two writes that must either both happen or neither. Without atomicity a crash between them destroys money; without durability a power cut after the confirmation screen loses it. This is also why NoSQL databases that dropped ACID in 2010 have spent the years since adding it back: MongoDB got multi-document transactions in 4.0, DynamoDB in 2018, Cassandra is adding them now. It turns out application developers cannot reliably hand-roll atomicity. Search "why NoSQL databases added ACID transactions back".
The four properties
A — ATOMICITY "all or nothing"
Either every operation in the transaction takes effect, or
none does. There is no partial outcome visible to anyone.
Enforced by: the recovery manager, using the UNDO log.
C — CONSISTENCY "valid state to valid state"
A transaction moves the database from one state satisfying
every constraint to another such state.
Enforced by: constraints (the DBMS) + correct application
logic (the programmer). This is the ONLY one of the four
that is partly the programmer's responsibility.
I — ISOLATION "as if alone"
Concurrent transactions do not see each other's
intermediate states. The result is as if they had run one
after another in some order.
Enforced by: the concurrency-control manager (locks or
MVCC).
D — DURABILITY "survives a crash"
Once COMMIT returns, the changes persist even if the
machine loses power immediately afterwards.
Enforced by: the REDO log written to stable storage before
commit is acknowledged (write-ahead logging).
WHO ENFORCES WHAT — the exam-critical split:
Atomicity recovery manager (undo log)
Consistency constraints + the programmer
Isolation concurrency control (locks / MVCC)
Durability recovery manager (redo log, fsync)
TRANSACTION STATES:
ACTIVE ──► PARTIALLY COMMITTED ──► COMMITTED
│ │
│ ▼
└────────► FAILED ──► ABORTED ──► (restart or kill)
· PARTIALLY COMMITTED = the last statement has executed but
the commit record is not yet on stable storage
· that gap is exactly where durability is won or lost: if
the log record reaches disk, the transaction is committed
and recovery will REDO it; if not, recovery will UNDO it
Atomicity, demonstrated
atomicity.sql
-- verified on PostgreSQL 18
CREATE TABLE account (
id INT PRIMARY KEY,
holder TEXT,
balance NUMERIC(12,2) CHECK (balance >= 0)
);
INSERT INTO account VALUES (1,'Ram',10000), (2,'Sita',5000);
-- A transfer is TWO writes that must be atomic.
-- Here the second one violates the CHECK constraint.BEGIN;
UPDATE account SET balance = balance - 3000 WHERE id = 1;
UPDATE account SET balance = balance - 99999 WHERE id = 2;
COMMIT;-- ERROR: new row for relation "account" violates check
-- constraint "account_balance_check"
-- DETAIL: Failing row contains (2, Sita, -94999.00).
SELECT id, holder, balance FROM account ORDER BY id;
-- id | holder | balance
-- ----+--------+----------
-- 1 | Ram | 10000.00 <- the −3000 was UNDONE
-- 2 | Sita | 5000.00
--
-- The FIRST update succeeded and was then rolled back
-- automatically when the second failed. Ram's balance is
-- exactly what it was. That is atomicity: no partial state
-- survived.-- ===== SAVEPOINT: atomicity at a finer grain =====
BEGIN;
UPDATE account SET balance = balance + 100 WHERE id = 1;
SAVEPOINT sp1;
UPDATE account SET balance = balance + 500 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1; -- discards only +500
COMMIT;
SELECT id, balance FROM account WHERE id = 1;
-- id | balance
-- ----+----------
-- 1 | 10100.00 <- the +100 kept, the +500 discarded ✔
--
-- A SAVEPOINT is a named point you can partially unwind to.
-- The transaction remains open and can still COMMIT.
Notice what the first example proves. PostgreSQL had ALREADY
applied the −3000 when the second statement failed. It then
undid it, using the undo information in the log. Nothing
outside the transaction ever saw Ram at 7000.
WHY ATOMICITY IS HARD: the DBMS must be able to undo work it
has already written to data pages, possibly after those pages
were flushed to disk. That is what the log is for, and it is
why the log write must happen BEFORE the data write —
covered in the log-based recovery topic.
THE THREE WAYS A TRANSACTION ABORTS:
1. explicit ROLLBACK by the application
2. a constraint violation or runtime error (as above)
3. the system aborts it — deadlock victim, serialization
failure, or a crash
In all three cases the guarantee is identical: no effect
survives.
Consistency — the property that is partly yours
CONSISTENCY has two halves, and confusing them is the most
common error in exam answers.
DATABASE consistency (the DBMS's job)
every declared constraint holds after each transaction:
primary keys, foreign keys, CHECKs, NOT NULL, UNIQUE.
APPLICATION consistency (YOUR job)
the transaction's logic is correct. The DBMS cannot know
that a transfer must debit one account and credit
another by the SAME amount.
BEGIN;
UPDATE account SET balance = balance - 3000 WHERE id = 1;
UPDATE account SET balance = balance + 2000 WHERE id = 2;
COMMIT;
Every constraint holds. Both balances are non-negative. The
DBMS is perfectly happy — and 1000 rupees has vanished from
the system. The transaction is ATOMIC, ISOLATED and DURABLE,
and still WRONG.
No amount of ACID compliance protects you from a bug in the
business logic. The DBMS guarantees that your (possibly
wrong) logic executes atomically; it does not guarantee the
logic is right.
CONSISTENCY CONSTRAINTS CAN BE DEFERRED to the end of the
transaction, which is essential for rules that are
temporarily violated mid-transaction:
SET CONSTRAINTS ALL DEFERRED;
BEGIN;
-- an intermediate state that breaks a FK is allowed here
COMMIT; -- everything checked at this instant
That distinction — consistency required at transaction
BOUNDARIES, not at every statement — is the precise
formulation.
The 3000-out / 2000-in example is the sharpest thing to remember about consistency. ACID is a guarantee about execution, not about correctness. The database will faithfully and durably destroy your money if you tell it to.
Isolation — measured, not asserted
isolation.sql
-- Two real concurrent sessions on PostgreSQL 18.
-- Session A reads twice; session B commits a change between
-- those reads.
-- ============ READ COMMITTED (PostgreSQL's default) =======
-- SESSION A SESSION B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM account WHERE id=1; -- 10000.00-- UPDATE account SET
-- balance=7777 WHERE id=1;
-- (auto-committed)
SELECT balance FROM account WHERE id=1; -- 7777.00 (!)
COMMIT;
-- MEASURED RESULT:
-- RC read1 = 10000.00
-- RC read2 = 7777.00 <- the SAME query, TWO answers
--
-- That is a NON-REPEATABLE READ. Legal at this level.-- ============ REPEATABLE READ, identical experiment =======BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE id=1; -- 10000.00
-- B commits balance=8888
SELECT balance FROM account WHERE id=1; -- 10000.00 ✔
COMMIT;-- MEASURED RESULT:
-- RR read1 = 10000.00
-- RR read2 = 10000.00 <- STABLE ✔
--
-- The transaction sees a consistent SNAPSHOT taken at its
-- first statement. B's committed change is invisible to it
-- until it ends.
Those four measured lines are the entire isolation concept:
RC read1 = 10000.00
RC read2 = 7777.00 ← same query, different answer
RR read1 = 10000.00
RR read2 = 10000.00 ← same query, same answer
Nothing changed except the isolation level. This is why
isolation is a SETTING with a cost, not a property you either
have or lack. Higher isolation means more consistency and
less concurrency.
HOW POSTGRESQL ACHIEVES IT — MVCC (multiversion concurrency
control):
· every row version carries the transaction id that created
it and the one that deleted it
· each transaction has a SNAPSHOT: the set of transactions
it considers committed
· a reader sees the row version visible in its snapshot
· therefore READERS NEVER BLOCK WRITERS and writers never
block readers
READ COMMITTED takes a new snapshot per STATEMENT
REPEATABLE READ takes ONE snapshot per TRANSACTION
SERIALIZABLE same snapshot, plus dependency tracking to
detect non-serializable patterns
That single design decision — one snapshot per statement
versus per transaction — is the whole difference between the
two levels.
Durability
Once COMMIT returns successfully, the change survives:
· a process crash
· an operating-system crash
· a power failure
It does NOT automatically survive: disk failure, fire, or
someone dropping the table. Those need replication and
backups, which are a separate concern from durability.
THE MECHANISM — WRITE-AHEAD LOGGING (WAL):
1. the transaction modifies pages in the buffer pool
(in memory, not yet on disk)
2. before COMMIT can return, the LOG RECORDS describing
those changes are written to the log file AND fsync'd
3. only then is the client told "committed"
4. the DATA pages may be written much later, lazily
Why the log and not the data? The log is written
SEQUENTIALLY — one append at the end of a file. Data pages
are scattered, so writing them would be random I/O. One
sequential fsync is orders of magnitude cheaper than
flushing dozens of random pages.
THE COST OF DURABILITY, and the knob:
synchronous_commit = on (default)
COMMIT waits for the log fsync. Guarantees durability.
Limits throughput to roughly the disk's fsync rate —
historically a few hundred per second on a spinning
disk, tens of thousands on an SSD with a
battery-backed cache.
synchronous_commit = off
COMMIT returns BEFORE the fsync. Much faster, and you
may lose the last fraction of a second of committed
transactions on a power failure.
Note: this risks losing recent COMMITS; it does NOT
risk corruption or partial transactions. Atomicity is
preserved either way.
That is a legitimate engineering trade for, say, a metrics
ingestion table, and unacceptable for a ledger.
GROUP COMMIT is the standard optimisation: batch the fsyncs
of several concurrent commits into one, so N transactions pay
for one disk flush.
🌍 Go further: ACID's counterpart in distributed systems is BASE (Basically Available, Soft state, Eventually consistent), and the reason the trade exists is the CAP theorem: under a network partition you must choose between consistency and availability. But the modern picture is more nuanced than "SQL is ACID, NoSQL is BASE" — Google Spanner provides distributed ACID transactions using synchronised atomic clocks, and CockroachDB and YugabyteDB do the same with consensus protocols. Search "Spanner TrueTime external consistency" and "PACELC theorem", the latter being CAP extended to cover latency in the normal no-partition case.
💡 Exam angle: define all four properties precisely and — the discriminating detail — state which component enforces each: recovery manager for atomicity and durability, concurrency control for isolation, constraints plus the programmer for consistency. Give the bank transfer as the standard example, and be ready to explain that a transaction can satisfy all four and still be logically wrong. Draw the transaction state diagram (active → partially committed → committed, with failed → aborted). Mention that durability comes from logging before commit, not from writing data pages.
Syllabus points
Atomicity, Consistency, Isolation, Durability
Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.
Related topics in Transaction Processing, Concurrency Control & Recovery