Transactions and ACID Interview Questions
Last updated 2026-11-19
Transactions are where DBMS interviews become precise. You are expected to define ACID, name the isolation anomalies, and then test a schedule for conflict serializability by drawing a precedence graph. This page works through those exercises with small schedules you can reproduce on paper.
It assumes the basics from DBMS interview questions. Locking protocols and deadlocks continue on the concurrency control page, and the preparation plan is in how to crack a DBMS interview.
| Topics | ACID, isolation levels, schedules, recovery |
|---|---|
| Worked examples | Bank transfer, precedence graphs, isolation anomalies |
| Level | Medium to hard |
Notation used in schedule questions
R1(A) means transaction T1 reads item A, W2(B) means T2 writes B. Two operations conflict if they belong to different transactions, touch the same item and at least one is a write. Conflict serializability asks whether swapping adjacent non-conflicting operations can turn the schedule into a serial one; the precedence graph test answers this: draw an edge Ti to Tj for each conflicting pair where Ti acts first, and the schedule is conflict serializable if and only if the graph has no cycle.
Isolation levels at a glance
- READ UNCOMMITTED allows dirty reads.
- READ COMMITTED prevents dirty reads but allows non-repeatable reads.
- REPEATABLE READ prevents non-repeatable reads; the SQL standard still allows phantoms.
- SERIALIZABLE gives the effect of some serial order.
- Real engines deviate: PostgreSQL REPEATABLE READ is snapshot isolation, and InnoDB REPEATABLE READ uses gap locks that block most phantoms.
Transactions and ACID Interview Questions: questions with worked answers
1 What is a transaction, and what do the letters in ACID stand for?
easyA transaction is a logical unit of work made of one or more operations that must succeed or fail together. Atomicity: all of it happens or none of it, enforced by undo logging and rollback. Consistency: a transaction takes the database from one valid state to another, respecting constraints; the application defines the rules and the DBMS enforces declared ones. Isolation: concurrent transactions behave as if they ran alone, within the chosen isolation level. Durability: once committed, changes survive crashes, enforced by write-ahead logging flushed to disk.
2 Show a bank transfer as a transaction and explain what goes wrong without atomicity.
easyMoving 500 from account 1 to account 2 requires two updates. If the system crashes after the debit but before the credit, 500 vanishes. Wrapped in a transaction, a crash before COMMIT makes recovery undo the debit. Add a CHECK or a conditional update so the balance cannot go negative, and the consistency rule holds too.
sqlBEGIN; UPDATE account SET balance = balance - 500 WHERE id = 1 AND balance >= 500; -- check the affected row count is 1, else ROLLBACK UPDATE account SET balance = balance + 500 WHERE id = 2; COMMIT;3 What are the states of a transaction?
easyActive (executing), partially committed (final statement executed, results possibly still in memory), committed (changes made durable), failed (normal execution cannot continue) and aborted (rolled back, database restored to its prior state). From aborted the system may restart the transaction if the failure was transient, or kill it if the cause was a logic error.
4 Explain dirty read, non-repeatable read and phantom read.
mediumDirty read: T2 reads a value T1 wrote but has not committed; if T1 rolls back, T2 used data that never existed. Non-repeatable read: T1 reads a row twice and sees different values because T2 updated and committed between the reads. Phantom read: T1 runs the same range query twice and the second run returns extra rows because T2 inserted and committed matching rows. The first two concern existing rows; phantoms concern the set of rows matching a predicate.
5 Map the four SQL isolation levels to the anomalies they allow.
mediumREAD UNCOMMITTED allows dirty reads, non-repeatable reads and phantoms. READ COMMITTED blocks dirty reads only. REPEATABLE READ additionally blocks non-repeatable reads. SERIALIZABLE blocks all three. Higher isolation costs concurrency through more locking or more aborts. Be ready to add that PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, and that snapshot isolation still allows write skew, which only true serializable mode prevents.
6 Is the schedule R1(A) R2(A) W1(A) W2(A) conflict serializable?
hardFind conflicting pairs on A. R2(A) comes before W1(A), giving edge T2 to T1. R1(A) comes before W2(A), giving T1 to T2. W1(A) before W2(A) also gives T1 to T2. The precedence graph has the cycle T1 to T2 to T1, so the schedule is not conflict serializable. In practice this is the lost update problem: both read the same old value and the last write overwrites the other. Neither serial order T1 then T2 nor T2 then T1 produces that outcome.
7 Test R1(A) W1(A) R2(A) W2(A) R1(B) W1(B) R2(B) W2(B) for serializability.
hardOn A, T1 does all of its operations before T2, so every conflicting pair gives T1 to T2. On B likewise, T1 operates before T2. The graph has the single edge T1 to T2 and no cycle, so the schedule is conflict serializable and equivalent to the serial schedule T1 then T2. A topological sort of the graph gives the equivalent serial order. With n transactions there are n factorial serial schedules, and a serializable schedule matches at least one.
8 What is view serializability and how does it differ from conflict serializability?
hardTwo schedules are view equivalent if each transaction reads the same initial values, reads from the same writers, and the same transaction performs the final write of each item. Every conflict-serializable schedule is view serializable, but not the reverse: schedules with blind writes (writes without a prior read) can be view serializable yet fail the precedence test. Testing view serializability is NP-complete, which is why real systems use conflict-based protocols such as two-phase locking.
9 What are recoverable, cascadeless and strict schedules?
mediumA schedule is recoverable if no transaction commits before every transaction it read from has committed. A cascadeless (ACA) schedule goes further: transactions read only committed values, so one abort never forces others to abort. A strict schedule additionally forbids reading or overwriting an item written by an uncommitted transaction. Strict implies cascadeless, which implies recoverable. Strict two-phase locking, which holds write locks until commit, produces strict schedules.
10 How does write-ahead logging give atomicity and durability?
mediumBefore a changed data page may be written to disk, the log record describing the change must be flushed first (write-ahead rule). A transaction is committed once its commit record is on stable storage, even if the data pages are not yet. After a crash, recovery redoes committed changes missing from disk and undoes changes of uncommitted transactions using the before-images in the log. This lets the buffer manager use a steal, no-force policy for speed while keeping both guarantees.
11 Describe the three phases of ARIES recovery.
hardAnalysis scans the log from the last checkpoint to rebuild the dirty page table and the list of transactions active at the crash. Redo repeats history: it replays all logged updates from the earliest needed LSN, including those of transactions that later lose, so the database reaches its exact pre-crash state. Undo then rolls back the loser transactions in reverse order, writing compensation log records so that a crash during recovery does not repeat work.
12 What is a checkpoint and why is it needed?
mediumA checkpoint is a log record, plus flushed state, that marks a point from which recovery need not look further back. Without it, recovery would scan the entire log. The system writes the list of active transactions and dirty pages (a fuzzy checkpoint does this without pausing work). After a crash, recovery starts from the last checkpoint, which bounds restart time.
13 What is the difference between pessimistic and optimistic concurrency control?
mediumPessimistic control assumes conflicts are likely and locks data up front, as in two-phase locking. Optimistic control lets transactions run on private copies and validates at commit time, aborting if another transaction changed data they read, usually via version numbers. Optimistic suits read-heavy workloads with few conflicts; under heavy contention it wastes work on retries. See concurrency control questions for locking and timestamp details.
14 What is write skew, and which isolation level prevents it?
hardWrite skew occurs when two transactions read overlapping data, make disjoint writes based on what they read, and together violate an invariant. Example: a hospital rule says at least one doctor must be on call; two doctors each check that the other is on call, then both go off call. Snapshot isolation allows it because the writes do not conflict. SERIALIZABLE prevents it, either by predicate or range locks or, in PostgreSQL, by serializable snapshot isolation that aborts one transaction.
Frequently asked questions
Does every database default to SERIALIZABLE?
No. PostgreSQL, Oracle and SQL Server default to READ COMMITTED, and MySQL InnoDB defaults to REPEATABLE READ, because stronger isolation costs throughput.
Is consistency in ACID the same as consistency in CAP?
No. In ACID it means constraints hold after a transaction. In CAP it means all nodes return the latest write. See distributed systems questions.
How do I show a schedule is not serializable quickly?
Draw the precedence graph from conflicting pairs only and look for a cycle. One cycle is enough.
Do NoSQL databases support transactions?
Many do within limits: MongoDB supports multi-document transactions on replica sets and sharded clusters, while key-value stores often guarantee atomicity only per key.
Related guides
More in CS Fundamentals Interview Questions
- What Happens When You Type a URL in the Browser: The Complete Answer
- Abstraction and Encapsulation Interview Questions
- Cache Memory and Computer Organization Interview Questions with Numericals
- Caching Interview Questions and Answers
- Cloud Computing Basics Interview Questions with Answers
- Computer Architecture Interview Questions with Numericals
- Computer Networks Interview Questions with Worked Answers and Numericals
- CPU Scheduling Interview Questions with Gantt Chart Numericals
- Database Indexing Interview Questions
- DBMS Concurrency Control Interview Questions
- DBMS Interview Questions and Answers
- DBMS Keys Interview Questions