SQL Transactions Interview Questions
What Interviewers Are Testing
Transaction interview questions test your understanding of ACID properties, isolation levels, concurrency control, and how databases ensure data consistency under concurrent access.
Interview Questions & Model Answers
Explain ACID properties with an example.
ACID ensures reliable transaction processing: Atomicity — all operations in a transaction succeed or all are rolled back (a bank transfer either fully completes or fully reverts). Consistency — a transaction brings the database from one valid state to another (account balances can't go negative). Isolation — concurrent transactions don't see each other's intermediate states. Durability — once committed, data survives power failures and crashes (written to disk).
BEGIN; -- Atomicity: both updates succeed or both rollback UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE accounts SET balance = balance + 500 WHERE id = 2; COMMIT; -- Durability: permanently persisted
Potential Follow-up Questions:
- Which ACID property is hardest to achieve in distributed systems?
What are database isolation levels and what problems do they prevent?
Isolation levels control how much transactions see each other's uncommitted changes. Four levels (weakest to strongest): READ UNCOMMITTED — can read uncommitted data (dirty reads). READ COMMITTED (default in most DBs) — only reads committed data. REPEATABLE READ — same query returns same data within a transaction (prevents non-repeatable reads). SERIALIZABLE — highest isolation, transactions behave as if sequential (prevents phantom reads). Higher isolation = fewer anomalies but more contention and lower throughput.
Potential Follow-up Questions:
- What isolation level does PostgreSQL use by default?
- What is a phantom read?
What is a deadlock and how do you prevent it?
A deadlock occurs when Transaction A holds a lock that Transaction B needs, and Transaction B holds a lock that Transaction A needs — creating a circular wait that neither can break. The database detects this and kills one transaction (the victim). Prevention strategies: always acquire locks in the same order across all transactions, keep transactions short, use appropriate isolation levels, add covering indexes to reduce lock scope.
Potential Follow-up Questions:
- How does a database detect deadlocks?
- What happens to the deadlock victim transaction?
What is the difference between optimistic and pessimistic locking?
Pessimistic locking: acquires a lock on a record before reading it, preventing other transactions from modifying it until released. Use when conflicts are frequent. High contention. Example: SELECT ... FOR UPDATE. Optimistic locking: no locks acquired during reads. At commit time, checks if the record was modified by another transaction (using a version number or timestamp). Use when conflicts are rare. Better concurrency. Example: UPDATE table SET col = v WHERE id = 1 AND version = 5 (fails if version changed).
-- Pessimistic locking SELECT * FROM products WHERE id = 1 FOR UPDATE; -- ... make changes ... UPDATE products SET stock = 99 WHERE id = 1; COMMIT; -- Optimistic locking SELECT id, stock, version FROM products WHERE id = 1; -- version = 5 UPDATE products SET stock = 99, version = version + 1 WHERE id = 1 AND version = 5; -- fails if someone else updated
Potential Follow-up Questions:
- When would you choose optimistic over pessimistic locking?
Frequently Asked Questions
What ACID means in database transactions?
What are the four transaction isolation levels in SQL?
Related Interview Topics
Practice Answering Live
Use our Interview Arena to practice SQL challenges under real interview conditions. Free.