Transactions & ACID Interview Questions & Answers

A transaction is a sequence of one or more SQL statements that the database treats as a single unit of work — either all succeed or none take effect. Transactions ensure that partial failures never leave the database in an inconsistent state.

-- Transfer $100 from account 1 to account 2 atomically
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If either UPDATE fails, ROLLBACK is triggered and neither change persists.

Without a transaction, a crash between the two UPDATEs would subtract $100 from account 1 but never add it to account 2 — money disappears.

Rule of thumb: wrap any sequence of writes that must succeed or fail together in a single transaction. A transaction that touches only one row is still valid — it gives you the crash-recovery guarantee.

ACID is the set of guarantees a database must provide for transactions to be reliable:

• Atomicity — the transaction is all-or-nothing. A failure at any point rolls back every change made so far.
• Consistency — a transaction can only bring the database from one valid state to another. All constraints, triggers, and rules still hold after the commit.
• Isolation — concurrent transactions do not see each other's uncommitted changes. The degree of isolation is configurable (see isolation levels).
• Durability — once committed, the changes survive crashes. The database writes them to persistent storage (WAL / redo log) before acknowledging the commit.

-- Atomicity: if the second UPDATE fails, the first is rolled back
BEGIN;
  UPDATE orders SET status = 'shipped' WHERE id = 42;
  INSERT INTO shipments (order_id, shipped_at) VALUES (42, now()); -- fails?
COMMIT; -- only reaches here if both statements succeed

Rule of thumb: when someone asks "how does your database prevent X?" the answer maps to one of the four ACID letters. Know which letter covers which class of problem.

• BEGIN (or START TRANSACTION) opens a new transaction. All subsequent statements are part of this transaction until it is ended.
• COMMIT permanently saves all changes made in the transaction and releases any locks held.
• ROLLBACK discards all changes made since BEGIN and releases locks. The database returns to the state it was in before the transaction started.

BEGIN;
  INSERT INTO invoices (customer_id, total) VALUES (7, 299.99);
  UPDATE accounts SET balance = balance - 299.99 WHERE customer_id = 7;
COMMIT;   -- both rows persist

-- Error path
BEGIN;
  DELETE FROM orders WHERE id = 99;
ROLLBACK; -- deletion is undone

In Postgres, a failed statement inside a transaction automatically puts the transaction into an error state — further statements are rejected until you issue ROLLBACK (or ROLLBACK TO SAVEPOINT).

Rule of thumb: always pair every BEGIN with either a COMMIT or a ROLLBACK. An open transaction that is never closed holds locks and blocks other sessions.

In autocommit mode (the default in most databases), every SQL statement that is not inside an explicit BEGIN block is automatically wrapped in its own single-statement transaction and committed immediately.

-- Autocommit ON (default): each statement is its own transaction
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- ^ committed immediately, cannot be rolled back

-- Explicit transaction: autocommit suspended until COMMIT/ROLLBACK
BEGIN;
  UPDATE users SET email = 'new@example.com' WHERE id = 1;
  -- still uncommitted, can still ROLLBACK
COMMIT;

• Postgres: autocommit is on by default; BEGIN suspends it.
• MySQL: autocommit is on by default; SET autocommit = 0 or BEGIN turns it off.
• SQL Server: autocommit is on by default; BEGIN TRANSACTION starts an explicit one.

Rule of thumb: never rely on autocommit for multi-statement operations. Always open an explicit transaction when two or more writes must be atomic.

A SAVEPOINT marks a point within a transaction that you can roll back to without aborting the entire transaction. This lets you recover from a partial failure while keeping the work done before the savepoint.

BEGIN;
  INSERT INTO orders (customer_id, total) VALUES (5, 100.00);

  SAVEPOINT after_order;

  INSERT INTO order_items (order_id, product_id, qty) VALUES (99, 1, 2);
  -- ^ suppose this fails (e.g. FK violation)

  ROLLBACK TO SAVEPOINT after_order;
  -- the orders INSERT is still intact; only order_items is undone

  -- Try a different fix or log the error, then commit what we have
COMMIT;

Savepoints are especially useful in ORMs and application frameworks that wrap nested operations in sub-transactions.

Rule of thumb: use savepoints for "nested transaction" patterns — when a library or service call may fail but you want to keep the outer transaction alive. Don't overuse them; a simpler design often avoids the need.

RELEASE SAVEPOINT name destroys the savepoint but does not commit or roll back the work done since it. The changes remain part of the enclosing transaction and will be committed or rolled back with it.

BEGIN;
  INSERT INTO audit_log (event) VALUES ('start');

  SAVEPOINT sp1;
  UPDATE config SET value = 'new' WHERE key = 'theme';
  RELEASE SAVEPOINT sp1;   -- sp1 is gone; UPDATE is still pending

  -- Cannot ROLLBACK TO SAVEPOINT sp1 anymore
COMMIT; -- both the INSERT and UPDATE persist

After RELEASE, you can no longer roll back to that savepoint name. RELEASE is useful to free memory when you are certain you will not need to roll back to a particular point.

Rule of thumb: release savepoints once you are confident the work they cover is correct. This is a minor housekeeping step; most applications omit it since savepoints are released automatically on COMMIT or ROLLBACK.

SQL Server supports an implicit transaction mode (SET IMPLICIT_TRANSACTIONS ON) where the database automatically begins a transaction before each DML or DDL statement without an explicit BEGIN TRANSACTION. The user must still issue COMMIT or ROLLBACK to end it.

-- SQL Server with implicit transactions enabled
SET IMPLICIT_TRANSACTIONS ON;

UPDATE products SET price = price * 1.1; -- transaction auto-started
-- still uncommitted! Must explicitly end it:
COMMIT;

This differs from autocommit (where each statement commits automatically) and from explicit transactions (where you write BEGIN TRANSACTION). Implicit transactions are an easy source of long-running uncommitted transactions and should be used carefully.

Rule of thumb: avoid IMPLICIT_TRANSACTIONS ON in SQL Server — it surprises developers who expect autocommit behavior. Prefer explicit BEGIN TRANSACTION … COMMIT for clarity.

The transaction log (called WAL — Write-Ahead Log — in Postgres) records every change before it is written to the main data files. On a crash, the database replays the log to bring data back to a consistent state (redo) and removes uncommitted changes (undo).

Timeline of a COMMIT:
1. Changes are written to the WAL on disk  ← durability guaranteed here
2. Database acknowledges COMMIT to the client
3. Changes are eventually flushed from buffer pool to data files
(crash between steps 2 and 3 is safe — WAL replay restores the data)

The WAL also powers replication (streaming the log to replicas) and point-in-time recovery (replaying the log up to a specific timestamp).

Rule of thumb: understand that COMMIT does NOT mean "data is in the table file" — it means "data is in the WAL and therefore durable." The actual table file update is asynchronous.

A long-running transaction holds row/page locks that block other writers, accumulates undo/rollback data that inflates the database's version store or transaction log, and in Postgres prevents VACUUM from reclaiming dead row versions (causing table bloat).

-- Postgres: find long-running transactions
SELECT pid,
       now() - pg_stat_activity.xact_start AS duration,
       query,
       state
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
  AND  now() - xact_start > INTERVAL '5 minutes'
ORDER  BY duration DESC;

-- Terminate if necessary
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE  now() - xact_start > INTERVAL '1 hour';

Rule of thumb: keep transactions as short as possible. Do not hold an open transaction while waiting for user input, making HTTP calls, or doing slow computation. Open the transaction, write, commit — then do anything slow.

A deadlock occurs when two (or more) transactions each hold a lock that the other needs, so neither can proceed.

Session A: locks row 1, waits for row 2
Session B: locks row 2, waits for row 1
→ circular wait → deadlock

Databases automatically detect deadlocks via a cycle-detection algorithm and resolve them by choosing a victim (typically the transaction with the least work done) and rolling it back with an error.

-- Avoid deadlocks by always locking rows in the same order
-- BAD: Session A locks user 1 then order 5; Session B locks order 5 then user 1
-- GOOD: both sessions always lock by (user_id, order_id) order

-- Postgres: SELECT FOR UPDATE to acquire locks explicitly in order
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM orders WHERE id = 5 FOR UPDATE;

Rule of thumb: prevent deadlocks by always acquiring locks in a consistent order across all transactions. Keep transactions short. Handle deadlock errors in application code with a retry loop.

Pessimistic locking acquires a lock before reading the data and holds it until the transaction commits — preventing any other writer from touching the row during that window.

Optimistic locking reads the data without a lock, does work, then checks at write time whether another writer has changed the data since it was read. If yes, it retries rather than committing stale data.

-- Pessimistic: lock the row immediately on read
BEGIN;
  SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;
  -- no other transaction can UPDATE this row until we COMMIT
  UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
COMMIT;

-- Optimistic: use a version column to detect conflicts
-- Read phase (no lock):
SELECT stock, version FROM inventory WHERE product_id = 42;
-- → stock=10, version=7

-- Write phase: only update if version hasn't changed
UPDATE inventory
SET    stock = 9, version = 8
WHERE  product_id = 42 AND version = 7;
-- If 0 rows affected → conflict detected → retry

Rule of thumb: use pessimistic locking for high-contention resources (inventory, seat reservations) where conflicts are frequent. Use optimistic locking for low-contention resources where conflicts are rare — it scales better by avoiding lock waits.

SELECT FOR UPDATE reads rows and immediately acquires an exclusive row lock on each row returned, preventing other transactions from updating or locking those rows until the current transaction commits or rolls back.

BEGIN;
  -- Lock the row so no other session can change it before we write
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  -- → balance = 500

  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Variants:

• FOR SHARE — shared lock; others can read but not write.
• FOR UPDATE SKIP LOCKED (Postgres, MySQL 8+) — skip rows already locked; useful for job queues where workers should not compete for the same row.
• FOR UPDATE NOWAIT — fail immediately if the row is already locked.

Rule of thumb: use SELECT FOR UPDATE when you read a value and immediately use it to compute an update — it closes the time-of-check / time-of-use race condition that would exist if the read and write were unprotected.

SKIP LOCKED lets multiple workers pull jobs from a queue table without competing for the same row — each worker skips rows already locked by another worker.

-- Schema
CREATE TABLE job_queue (
  id         BIGSERIAL PRIMARY KEY,
  payload    JSONB NOT NULL,
  status     TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Worker: claim one job atomically
BEGIN;
  SELECT id, payload
  FROM   job_queue
  WHERE  status = 'pending'
  ORDER  BY created_at
  LIMIT  1
  FOR UPDATE SKIP LOCKED;   -- skip rows locked by other workers

  UPDATE job_queue SET status = 'processing' WHERE id = <claimed_id>;
COMMIT;

Each worker gets a different row. If a worker crashes, the transaction rolls back, returning the row to pending for another worker to claim.

Rule of thumb: SELECT … FOR UPDATE SKIP LOCKED is the correct pattern for building a reliable job queue in SQL. It is atomic, crash-safe, and scales to many concurrent workers without external coordination.

Two-phase commit is a distributed coordination protocol that ensures a transaction spanning multiple independent databases either commits on all of them or rolls back on all.

• Phase 1 (Prepare): the coordinator asks each participant to prepare (write to their WAL, acquire locks, but do not commit). Each replies "yes" or "no".
• Phase 2 (Commit/Abort): if all said "yes", the coordinator tells all to commit. If any said "no", the coordinator tells all to abort.

-- Postgres prepared transactions (the participant side of 2PC)
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'txn-xyz-001'; -- phase 1: prepared, not committed

-- Later, coordinator decides to commit or rollback:
COMMIT PREPARED 'txn-xyz-001';
-- or
ROLLBACK PREPARED 'txn-xyz-001';

Rule of thumb: 2PC solves cross-database atomicity but adds latency and coordinator failure risk. In modern systems, the Saga pattern (compensating transactions) is often preferred over 2PC for microservice architectures.

1. Keep transactions short — open, write, commit. Do not hold a transaction open while making network calls, waiting for user input, or running slow computations.
2. Access tables in a consistent order — prevents deadlocks across concurrent transactions that touch the same set of tables.
3. Handle errors explicitly — always ROLLBACK on any exception; never swallow errors and then commit.
4. Do not use transactions for reads alone — unless you need a consistent snapshot across multiple queries, a plain SELECT outside a transaction is cheaper.
5. Retry on transient failures — deadlocks and serialization failures are expected; build retry logic with exponential back-off.

-- Pattern: wrap in try/catch, always rollback on error (Python psycopg2)
try:
    cur.execute("BEGIN")
    cur.execute("UPDATE ...")
    cur.execute("INSERT ...")
    cur.execute("COMMIT")
except Exception:
    cur.execute("ROLLBACK")
    raise

Rule of thumb: a transaction should be as wide as necessary (all writes that must be atomic) and no wider. Every extra statement inside a transaction is a longer lock hold and a bigger rollback payload.