Failure Classification

DSA, Database System & Operating System — Transaction Processing, Concurrency Control & Recovery, NEC licence examination syllabus (Nepal Engineering Council).

Failure Classification

What can go wrong, and which failures the recovery manager is expected to survive.

🌍 Where this lives: this classification determines what your backup strategy must cover. A crash needs only the write-ahead log — recovery is automatic and takes seconds. A disk failure needs a restored backup plus archived logs, which is minutes to hours. A "logical" failure — someone ran DELETE without a WHERE and committed — needs point-in-time recovery to a moment before the mistake, and that only works if you were archiving logs continuously. The three failure classes map onto three completely different recovery procedures, and teams discover which ones they prepared for at the worst possible moment. Search "point in time recovery PITR WAL archiving".

The three classes

1. TRANSACTION FAILURE — one transaction cannot complete The rest of the system is healthy. (a) LOGICAL ERROR the transaction cannot proceed because of its own conditions: a constraint violation, bad input, division by zero, an explicit ROLLBACK → the transaction is aborted; nothing else is affected (b) SYSTEM ERROR the system aborts the transaction because of an undesirable state: DEADLOCK, serialization failure, lock timeout, resource exhaustion → the transaction may be RETRIED and will often succeed RECOVERY: UNDO this transaction's writes using the log. Cost: milliseconds. Automatic. No other transaction affected. 2. SYSTEM CRASH — the whole DBMS or machine stops Power failure, OS panic, hardware fault, process kill. THE FAIL-STOP ASSUMPTION: the contents of VOLATILE storage (RAM, buffer pool) are lost, but NON-VOLATILE storage (disk) is NOT corrupted. Recovery depends entirely on this assumption — the log on disk must be intact and trustworthy. RECOVERY: restart, read the log, REDO committed transactions and UNDO uncommitted ones. Cost: seconds to minutes, proportional to how much log accumulated since the last checkpoint. Automatic. 3. DISK FAILURE — non-volatile storage is destroyed A head crash, a failed SSD, a corrupted filesystem. RECOVERY: restore the most recent BACKUP, then replay the ARCHIVED LOG forward to bring it up to date. Cost: minutes to hours. NOT automatic — requires a human and a backup that exists. STORAGE HIERARCHY — the reason the classes differ: VOLATILE RAM, CPU cache, buffer pool lost on power failure NON-VOLATILE disk, SSD, tape survives a crash; can still fail STABLE a theoretical ideal: never lost approximated by replication — write the log to two or more independent disks "Stable storage" does not exist. It is approximated well enough that the probability of losing all copies is acceptable, which is what RAID, replicas and off-site backups buy you.

What each failure requires

TRANSACTION SYSTEM DISK FAILURE CRASH FAILURE ──────────────────────────────────────────────────────── volatile storage intact LOST LOST disk contents intact intact DESTROYED the log intact intact destroyed (need archive) scope one txn all active everything txns recovery needs undo log log + backup + checkpoint archived log automatic? yes yes NO typical time ms seconds minutes-hours data loss? none none since last backup+archive THE CRITICAL DEPENDENCY: system-crash recovery works only because the LOG survived. That is why write-ahead logging insists the log record reach stable storage before the corresponding data page, and why fsync is not optional. A FOURTH CATEGORY worth knowing, though textbooks omit it: 4. LOGICAL / HUMAN FAILURE The data is intact, the system is healthy, and the contents are wrong: a DELETE with no WHERE, a bad migration, a deployment that corrupted rows. No amount of ACID helps — the transaction was atomic, durable, and disastrous. RECOVERY: POINT-IN-TIME RECOVERY. Restore a backup from before the mistake, then replay archived logs up to the instant BEFORE the bad transaction: restore_command = 'cp /archive/%f %p' recovery_target_time = '2026-08-05 14:32:00' This is the ONLY defence, and it requires continuous log archiving to have been switched on BEFORE the incident.

Worked example — the same table, three failures

failures.sql

-- ===== 1. TRANSACTION FAILURE (verified) =====
-- A constraint violation aborts one transaction and leaves
-- everything else untouched.
BEGIN;
  UPDATE account SET balance = balance - 3000 WHERE id = 1;
  UPDATE account SET balance = balance - 99999 WHERE id = 2;
COMMIT;
-- ERROR: violates check constraint "account_balance_check"

SELECT id, balance FROM account ORDER BY id;
--  id | balance
--  ----+----------
--   1 | 10000.00     <- the −3000 was UNDONE
--   2 |  5000.00
-- Recovery: automatic, instantaneous, scoped to one txn ✔

-- ===== 2. SYSTEM CRASH — what recovery does =====
-- Simulated: kill -9 the postmaster mid-workload, restart.
-- The startup log shows:
--
--   LOG:  database system was interrupted; last known up at
--         2026-08-05 14:20:11
--   LOG:  database system was not properly shut down;
--         automatic recovery in progress
--   LOG:  redo starts at 0/1A2B3C40
--   LOG:  invalid record length at 0/1A2F8890: wanted 24,
--         got 0
--   LOG:  redo done at 0/1A2F8868
--   LOG:  database system is ready to accept connections
--
-- "redo starts at" is the last CHECKPOINT position. Recovery
-- read forward from there, REDID every committed change and
-- UNDID every uncommitted one. No data loss, no human
-- involvement.

-- ===== 3. DISK FAILURE — the procedure =====
-- Not simulable in SQL. The sequence is:
--   a. restore the base backup
--        pg_basebackup output, or a filesystem snapshot
--   b. tell PostgreSQL where the archived logs are
--        restore_command = 'cp /archive/%f %p'
--   c. optionally set a target
--        recovery_target_time = '2026-08-05 14:32:00'
--   d. start the server; it replays the archive forward
--
-- WHAT DETERMINES YOUR DATA LOSS:
--   RPO (recovery point objective) = how much data you can
--        afford to lose = the gap since your last archived
--        log segment
--   RTO (recovery time objective) = how long you can be
--        down = restore time + replay time
--
-- Both are decided by configuration you set BEFORE the
-- failure, not by anything you can do after it.

The three recovery paths are genuinely different procedures, and the distinction is what the classification is FOR: transaction failure → the log's UNDO information, in memory or on disk. Nothing else needed. system crash → the log from the last CHECKPOINT forward. Bounded by checkpoint frequency, which is why checkpoints exist: they cap recovery time. disk failure → a BACKUP plus every log record since. You cannot recover what you never copied elsewhere. THE PLANNING QUESTION each class raises: transaction failure → does my application retry? system crash → how frequent are my checkpoints, and is fsync actually enabled? disk failure → do I have a tested restore, and is log archiving on? That last one is where real organisations fail. A backup you have never restored is a hypothesis, not a backup.

The fail-stop assumption is worth stating explicitly because everything else rests on it: crash recovery works only if the disk contents are intact and trustworthy. A disk that silently returns corrupted data — bit rot, a lying write cache, a firmware bug — breaks the assumption, and recovery may then "successfully" restore a corrupt database. That is why checksums (data_checksums = on) and battery-backed or flush-honouring write caches matter.

🌍 Go further: the fail-stop assumption is optimistic, and the research on how it breaks is sobering. Storage devices exhibit silent data corruption, and worse, some drives acknowledge an fsync before the data is durable — meaning a database can report a transaction committed that a power failure then loses. The 2018 paper "Protocol-Aware Recovery for Consensus-Based Storage" and the widely-read "Files are hard" and "fsync errors" discussions document real cases, including a PostgreSQL fsync-error handling bug fixed in 2018. Search "PostgreSQL fsync reliability 2018" and "silent data corruption storage".

💡 Exam angle: name the three classes — transaction failure (subdivided into logical and system errors), system crash, and disk failure — and state for each what survives and what recovery requires. The fail-stop assumption is the key concept: volatile storage is lost, non-volatile storage is intact. Know the storage hierarchy (volatile / non-volatile / stable) and that stable storage is approximated by replication. A strong answer adds that only disk failure needs a backup, and that system-crash recovery is bounded by checkpoint frequency.

Syllabus points

Types of failures

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.