Skip to content
Tech Interview Prep
Technical interview guide

Transactions & Isolation Levels

ACID guarantees, and the isolation-level trade-off between correctness and concurrent throughput.

Read
24 min
Practice MCQs
25
Interview Q&A
25
Edition
v3
Editorial status
Reviewed

Scope: SQL principles with PostgreSQL 18 examples; vendor-specific behavior must be verified.

Curated: · Written: · Reviewed:

25 questions

MCQ-2Session A begins a transaction at read committed and runs `SELECT balance FROM accounts WHERE id = 1`, which returns 100. Session B then commits `UPDATE accounts SET balance = 250 WHERE id = 1`. Session A re-runs the identical SELECT before committing. What does session A's second read return?
MCQ-20Session A runs under read committed: ``` BEGIN; SELECT balance FROM accounts WHERE id = 1; -- returns 100 -- session B commits: UPDATE accounts SET balance = 120 WHERE id = 1 -- session C commits: UPDATE accounts SET balance = 150 WHERE id = 1 SELECT balance FROM accounts WHERE id = 1; -- returns 150 COMMIT; ``` Which statement about the snapshots behind those two reads is correct?
MCQ-18A transaction runs `SELECT id FROM bookings WHERE room = 7 AND day BETWEEN '2026-03-01' AND '2026-03-31'` and gets 9 rows. Before it commits it runs the identical query again and gets 11 rows, because another transaction committed two inserts into that date range in between. Which statement correctly names this anomaly and the isolation level the SQL standard requires to prevent it?
MCQ-3Postgres 16, default isolation level. `accounts` holds one row: `id = 1, balance = 500`. Two sessions run this interleaving: ``` A: BEGIN; A: SELECT balance FROM accounts WHERE id = 1; -- 500 B: BEGIN; B: SELECT balance FROM accounts WHERE id = 1; -- 500 A: UPDATE accounts SET balance = 600 WHERE id = 1; -- app computed 500 + 100 A: COMMIT; B: UPDATE accounts SET balance = 600 WHERE id = 1; -- app computed 500 + 100 B: COMMIT; ``` What is `balance` after B commits?
MCQ-12On PostgreSQL, a REPEATABLE READ transaction runs an UPDATE on a row that another transaction modified and committed after this transaction took its snapshot. What happens?
MCQ-19A nightly reconciliation job borrows a session from a PostgreSQL connection pool whose default isolation level is READ COMMITTED. That one transaction must run at REPEATABLE READ, and no later transaction on the same pooled session may inherit the change. Which action achieves that?
MCQ-1A service moves $100 from account A to account B in a single PostgreSQL 16 transaction: ```sql BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 'A'; SAVEPOINT after_debit; UPDATE accounts SET balance = balance + 100 WHERE id = 'B'; INSERT INTO audit_log (actor, amount) VALUES (NULL, 100); -- audit_log.actor is NOT NULL ``` The INSERT fails. The application's error handler then runs `ROLLBACK TO SAVEPOINT after_debit;` followed by `COMMIT;`. What does the database hold afterwards?
MCQ-14A key-value store appends every write to a write-ahead log and acknowledges it as soon as `write()` on the log file returns; a background thread calls `fsync` on that file every 5 ms. The team runs two failure tests against the same build: (A) `kill -9` the server process, (B) pull the machine's power cord. Which acknowledged writes are lost in each test?
MCQ-22A payments service runs this on PostgreSQL 16: ```sql BEGIN; -- account 1 currently has balance 300 UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- would leave -200 UPDATE accounts SET balance = balance + 500 WHERE id = 2; COMMIT; ``` `accounts.balance` carries `CHECK (balance >= 0)`. The first UPDATE raises a check-constraint violation and the transaction aborts. The service also requires a second approver for transfers above $10,000, enforced only in application code. A teammate concludes: "So the database guarantees our business rules. That's what ACID's C means." Which statement about what the abort proves is correct?
MCQ-6A Postgres 16 `counters` table holds one row per counter. Forty workers each increment the same counter once per event by running `SELECT value FROM counters WHERE id = $1`, adding 1 in application code, then `UPDATE counters SET value = $2 WHERE id = $1`; after 10,000 events the row reads 9,970. The workers run at the default READ COMMITTED isolation, and the team will not add retry logic or explicit locks. Which change makes every increment count?
MCQ-7Postgres 16, default `READ COMMITTED`. `accounts(id int primary key, balance int)` holds one row: `(1, 100)`. Two sessions, each on its own connection, run this at the same time: ```sql BEGIN; SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- application computes new_balance = balance + <delta> UPDATE accounts SET balance = <new_balance> WHERE id = 1; COMMIT; ``` Session A uses delta 50 and its `SELECT ... FOR UPDATE` acquires the row lock first; session B uses delta 30. What is the final balance?
MCQ-11A PostgreSQL 16 database runs at its default isolation level. Table `accounts(id int PRIMARY KEY, balance int)` holds one row `(1, 100)`. Two sessions run concurrently: ``` Session B: BEGIN; Session B: UPDATE accounts SET balance = balance - 80 WHERE id = 1; -- holds the row lock, balance now 20 uncommitted Session A: BEGIN; Session A: UPDATE accounts SET balance = balance - 30 WHERE id = 1 AND balance >= 100; -- blocks on B's lock Session B: COMMIT; ``` Session A's UPDATE then unblocks. What does A observe, and what is the balance afterwards?
MCQ-8A Postgres 16 `jobs` table is claimed by 32 workers, each running `SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR <clause>`. Two workers must never process the same job, and no worker should wait on a row another worker already holds. Which clause satisfies both?
MCQ-9A payments service runs each transfer as one transaction: `UPDATE accounts SET balance = balance - :amt WHERE id = :from`, then `UPDATE accounts SET balance = balance + :amt WHERE id = :to`. The audit log requires the debit to be written before the credit, so the two statements cannot be reordered. Under PostgreSQL's default READ COMMITTED, T1 (moving $100 from account 1 to account 2) and T2 (moving $50 from account 2 to account 1) reach their first UPDATE within the same millisecond, and PostgreSQL aborts one with `ERROR: deadlock detected`. Which change removes the deadlock without serializing all transfers?
MCQ-15A payments service on PostgreSQL 15 moves money between two accounts in a single transaction, updating both rows; transfers run in both directions between the same account pairs. Deadlock errors (SQLSTATE 40P01) hit 0.4% of transfers, and retries with exponential backoff already recover them. Which change stops the deadlocks from forming?
MCQ-16PostgreSQL 16, default settings. Table `on_call(doctor_id int primary key, on_duty boolean)` holds two rows: (1, true) and (2, true). Two sessions execute these statements in the order shown: ``` A: BEGIN ISOLATION LEVEL REPEATABLE READ; B: BEGIN ISOLATION LEVEL REPEATABLE READ; A: SELECT count(*) FROM on_call WHERE on_duty; -- 2 B: SELECT count(*) FROM on_call WHERE on_duty; -- 2 A: UPDATE on_call SET on_duty = false WHERE doctor_id = 1; COMMIT; B: UPDATE on_call SET on_duty = false WHERE doctor_id = 2; COMMIT; ``` Which statement describes the final table and the reason?
MCQ-4Two sessions run at PostgreSQL's REPEATABLE READ (snapshot isolation). Each runs `SELECT count(*) FROM on_call WHERE on_duty` and sees 2, so each assumes the other doctor will cover and runs `UPDATE on_call SET on_duty = false` for its own row. Both commit, and the rota is now empty. Which anomaly is this?
MCQ-21Which situation genuinely requires serializable rather than a row lock?
MCQ-5A booking service runs each reservation in a PostgreSQL SERIALIZABLE transaction: it counts the active bookings for a room, and if the count is below the room's capacity it inserts a new booking. Under load, transactions abort with SQLSTATE 40001 even though no two of them insert the same booking. What must the application do when it catches that error?
MCQ-24A service catches SQLSTATE 40001 (serialization_failure) from PostgreSQL and re-runs the entire transaction body. Which of these transactions is safe to retry that way?
MCQ-25A PostgreSQL 16 service runs at SERIALIZABLE. Each transaction reads one account row and writes one ledger row. Under a flash sale, 64 clients contend on the same account row and the database saturates at 5,000 transaction executions per second in total: first attempts plus retries. 25% of first attempts abort on an SSI conflict and are retried once; every retry commits. What committed throughput does the service sustain?
MCQ-23A checkout service runs `BEGIN; SELECT ... FOR UPDATE` on an order row, calls the payment gateway over HTTP with no client timeout, then `UPDATE`s and `COMMIT`s. The gateway's p99 jumps from 200 ms to 8 s. During the spike, p99 for updates to those orders rises to 8.2 s and `pg_stat_activity` shows the checkout sessions `idle in transaction` for the full 8 s. Which mechanism explains the update latency?
MCQ-13An order service calls the payment provider's charge endpoint inside the same database transaction that inserts the order row. The framework retries the whole block on a serialization failure and gives up after three attempts; the provider has no idempotency-key support. Under load, some customers are charged more than once for a single order. Which change removes the extra charges?
MCQ-10A Postgres 16 primary runs autovacuum with default settings. The `orders` table holds about 150 million rows in 40 GB; autovacuum runs on it every few minutes and completes each pass, yet `pg_stat_user_tables.n_dead_tup` for `orders` climbs from 2 million to 90 million over three days and the table grows to 64 GB. `pg_stat_activity` shows one session `idle in transaction` with `xact_start` 71 hours ago and `backend_xmin` = 480,000,000; the next XID to be assigned is 900,000,000. That session holds only an AccessShareLock on `orders`. What is causing the growth, and what clears it?
MCQ-17On a PostgreSQL 16 primary, table events holds 20,000,000 rows in 2.0 GB, and a nightly job runs UPDATE events SET payload = payload across the whole table. Three hours ago that job rewrote all 20,000,000 rows and committed with transaction id 74,205,119. pg_stat_all_tables now reports n_live_tup = 20,043,918, n_dead_tup = 20,431,117, 4.1 GB on disk, last_autovacuum 22 minutes ago, and vacuum_count = 14 over the last 24 hours; n_dead_tup has not fallen across any of those runs. pg_stat_activity shows one old session with state = idle in transaction, xact_start 09:41 ago, its last statement a SELECT, and backend_xmin = 74,112,003. pg_replication_slots is empty, age(datfrozenxid) = 41,000,000, and autovacuum_freeze_max_age is left at its default. Which explanation accounts for the 20.4 million dead tuples surviving fourteen autovacuum runs?