Transactions and ACID Properties in DBMS Interview Questions

A transaction is a group of operations the database treats as one unit: all commit or none do. A transfer is two updates that must not be separated:

CREATE TABLE accounts (
    owner   TEXT PRIMARY KEY,
    balance INTEGER NOT NULL CHECK (balance >= 0)
);
BEGIN;
INSERT INTO accounts VALUES ('Asha', 1000), ('Ravi', 200);
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE owner = 'Asha';
UPDATE accounts SET balance = balance + 300 WHERE owner = 'Ravi';
COMMIT;

SELECT owner, balance FROM accounts ORDER BY owner;

Without BEGIN, MySQL, PostgreSQL, SQL Server and SQLite autocommit each statement; Oracle keeps a transaction open from the first DML until COMMIT. See transactions.

Atomicity: all or nothing (undo log). Consistency: each transaction moves between valid states (constraints, app logic). Isolation: no one sees another transaction's partial work (locks or MVCC). Durability: committed work survives a crash (write-ahead log). Two traps: most engines default to read committed, not serializable, and the database checks only declared rules, so a transfer that debits 300 and credits 30 commits happily.

It prints 50. ROLLBACK discards every change since BEGIN, so both updates vanish: atomicity. It never undoes committed work, and in MySQL and Oracle a DDL statement inside the transaction commits implicitly, so a rollback cannot reach past it.

It prints A,C. ROLLBACK TO before_b undoes only the work after the savepoint and keeps the transaction open, so C is inserted and both A and C commit. In PostgreSQL, where any error aborts the whole transaction, a savepoint is how you recover from one failed statement. See savepoints.

Active (running), partially committed (last statement done, changes not yet durable), committed (commit record in the log), failed (an error, deadlock or crash stopped it) and aborted (changes rolled back). The paths are active → partially committed → committed, or active → failed → aborted. After an abort the DBMS restarts the transaction (after a deadlock) or kills it (after a logic error).

It prints 0. SQLite runs DDL inside transactions, so the rollback removes the table (sqlite_master is the schema catalog). PostgreSQL and SQL Server do the same for most DDL; MySQL and Oracle commit implicitly. So a failed PostgreSQL migration rolls back cleanly, while a failed MySQL one leaves the schema half changed.

It depends on the engine. PostgreSQL aborts the whole transaction, and every later statement fails until you roll back. MySQL InnoDB rolls back only the statement on a duplicate key or lock wait timeout, but the whole transaction on a deadlock (see the InnoDB error handling docs). SQLite usually undoes only the statement, though a disk full or I/O error can roll back the transaction (SQLite transaction docs). SQL Server depends on the error, unless SET XACT_ABORT ON. So roll back explicitly on any error and decide whether to retry.

A schedule is serializable when its effect equals some serial order of its transactions. To test conflict serializability, draw a precedence graph with an edge Ti → Tj for each pair of operations on the same item, at least one a write, where Ti acts first. No cycle means serializable, and a topological order gives the equivalent serial order. R1(A) W2(A) W1(A) gives T1 → T2 → T1, a cycle: the lost update. View serializability accepts more schedules but is NP-complete to test.

With a write-ahead log: a change's log record reaches disk before the changed page, and a transaction commits once its commit record is flushed, which costs one sequential fsync. After a crash, the DBMS redoes committed changes missing from the data files (durability) and undoes uncommitted changes that already reached them (atomicity); engines may write uncommitted pages early, so they need both. ARIES does this in three passes (analysis, redo, undo), and checkpoints bound the replay.

A schedule is recoverable when a transaction commits only after every transaction it read from has committed. Otherwise T2 can read T1's uncommitted write and commit, then T1 aborts, leaving T2's result built on data that never existed.

A cascading rollback is one abort forcing others: every transaction that read T1's writes must abort too. A cascadeless schedule reads only committed data; a strict one also never overwrites uncommitted data, which strict two-phase locking guarantees.