Transactions and ACID interview questions, with answers

The questions, with answers

1. 1.What is a transaction?

   A transaction is a sequence of database operations executed as a single logical unit of work: either all of its effects are made permanent (COMMIT) or none of them are (ROLLBACK). It is the mechanism that lets a multi-step change — reduce stock, insert the order, charge the account — behave as one indivisible change to the outside world. Most databases run in autocommit mode by default, where each statement is its own transaction; you begin an explicit transaction when several statements must succeed or fail together.

   BEGIN;
   UPDATE Products SET stock = stock - 1 WHERE product_id = 42 AND stock > 0;
   INSERT INTO Orders (product_id, customer_id) VALUES (42, 7);
   COMMIT;   -- or ROLLBACK if the UPDATE touched zero rows

2. 2.What do the four ACID properties mean?

   Atomicity: all of the transaction's changes happen, or none do — a failure halfway leaves no partial result. Consistency: a transaction takes the database from one valid state to another, so every constraint (primary keys, foreign keys, checks) holds after commit; the database enforces the declared rules and the application supplies the rest. Isolation: concurrent transactions don't see each other's uncommitted, in-progress changes — each behaves as if it ran alone, to a degree set by the isolation level. Durability: once the database says committed, the change survives a crash or power loss. Give each one a concrete failure it prevents; the words alone don't score.

3. 3.How does the database achieve atomicity and durability?

   With a log. Before any data page is modified, the change is written to a write-ahead log (redo log in MySQL, WAL in PostgreSQL). Durability: a transaction is not reported committed until its log records are flushed to disk, so after a crash the database replays the log and every committed change is recovered even if the data files were never updated. Atomicity: the database keeps what it needs to reverse an incomplete transaction — InnoDB in separate undo logs, PostgreSQL by leaving the old row versions in place (MVCC) and simply never marking the new ones committed — so a transaction found half-done at recovery, or an explicit ROLLBACK, leaves no trace. The design insight to mention: appending to a log is sequential and fast, which is why databases can be both durable and quick.

4. 4.What are the isolation levels, and which anomalies does each allow?

   Three anomalies. Dirty read: reading another transaction's uncommitted change, which may later be rolled back. Non-repeatable read: reading the same row twice and getting different values because another transaction committed in between. Phantom read: re-running a query and seeing rows that weren't there before, because another transaction inserted them.

   Four levels, from weakest: READ UNCOMMITTED allows all three; READ COMMITTED prevents dirty reads; REPEATABLE READ also prevents non-repeatable reads; SERIALIZABLE prevents phantoms too, behaving as if transactions ran one after another. Defaults differ and are a favourite follow-up: PostgreSQL, SQL Server and Oracle default to READ COMMITTED; MySQL's InnoDB defaults to REPEATABLE READ, and its snapshot reads block most phantoms in practice even though the standard permits them at that level.

5. 5.What is a deadlock, and how do databases handle it?

   Two transactions each hold a lock the other needs: T1 locks row A and waits for row B, T2 locks row B and waits for row A, and neither can proceed. The database detects the cycle in its wait-for graph (or times out), picks a victim — usually the transaction that has done the least work — rolls it back, and returns a deadlock error to that client, which should retry. Applications prevent deadlocks mainly by acquiring locks in a consistent order everywhere (always update accounts in ascending id order), keeping transactions short, and not holding locks while waiting on user input or a network call.

6. 6.What is the difference between optimistic and pessimistic concurrency control?

   Pessimistic control assumes conflicts are likely and locks the rows up front — SELECT ... FOR UPDATE — so nobody else can change them until you commit; safe, but readers and writers queue. Optimistic control assumes conflicts are rare: read without locking, and at write time check that nothing changed, typically by comparing a version column and failing the update if it moved. It scales better for read-heavy workloads and the application handles the retry. Ticket booking with a hot inventory row wants pessimistic locking; editing a user's profile wants optimistic.

   -- optimistic: the UPDATE only succeeds if the row is still at the version we read
   UPDATE Accounts
   SET balance = balance - 500, version = version + 1
   WHERE id = 7 AND version = 12;
   -- 0 rows affected → someone else changed it; re-read and retry

7. 7.What is a savepoint?

   A named point inside a transaction that you can roll back to without abandoning the whole transaction. SAVEPOINT sp1 marks it; ROLLBACK TO SAVEPOINT sp1 undoes everything after it while keeping earlier work and the transaction open; RELEASE SAVEPOINT discards the marker. They are how you implement "try this optional step, and if it fails carry on with the rest" inside one atomic unit. Nested transactions in most databases are savepoints under another name; a COMMIT inside a nested block does not make anything durable until the outer transaction commits.

8. 8.Does consistency in ACID mean the same thing as consistency in the CAP theorem?

   No, and interviewers ask this to catch the conflation. ACID consistency is about integrity: every committed transaction leaves the database satisfying its constraints and rules. CAP consistency is about distributed reads: every read across a cluster sees the most recent write, as if there were a single copy. A single-node database can be fully ACID-consistent while the question of CAP consistency never arises; a distributed store can offer CAP-style strong consistency while providing no transactions at all. Keep the two words apart and say which you mean.

How the diagnostic asks it

One question from the DBMS bank, exactly as a sitting would show it. The bank has 3 on transactions & acid and 30 across DBMS.