ACID Properties, Transactions, Locks, and Concurrency Control: 20 Real Interview Questions

ACID properties fundamentals define database reliability. ACID (Atomicity, Consistency, Isolation, Durability) ensures that transactions either complete fully or not at all, maintaining database integrity even during failures. Understanding ACID, transactions, locks, and concurrency control separates engineers who can handle reliability from those who cause production incidents.

Understanding ACID Properties Fundamentals

ACID properties fundamentals address a critical problem: how can a database guarantee correctness when thousands of simultaneous users modify data, and failures can occur at any moment? A transfer of $100 from Account A to B must be atomic: either both succeed or both fail. Partial completion would lose money.

Atomicity: All or Nothing

ACID fundamentals start with Atomicity: transactions are indivisible units. Either all statements execute and commit, or none execute. No partial updates. BEGIN TRANSACTION; UPDATE account SET balance = balance – 100; UPDATE account SET balance = balance + 100; COMMIT; Both updates succeed or both rollback. Databases log changes to ensure atomicity even if the system crashes mid-transaction.

Consistency: Valid States Only

Consistency ensures database moves from one valid state to another. Foreign key constraints, unique constraints, check constraints enforce consistency. A transfer that violates a constraint (negative balance) fails entirely. ACID fundamentals: the database never shows inconsistent data between transactions.

Isolation: Concurrent Transactions Dont Interfere

ACID fundamentals include isolation—transactions dont see partial results of other uncommitted transactions. Isolation levels control trade-offs: READ UNCOMMITTED (dirty reads allowed, fastest), READ COMMITTED (dirty reads prevented), REPEATABLE READ (phantom reads allowed), SERIALIZABLE (slowest, most isolated). Choose based on consistency vs performance needs.

Durability: Permanent Once Committed

Durability guarantees that committed data survives failures. Writes are flushed to disk (not just memory). Even if the server crashes, committed transactions persist. ACID fundamentals: durability requires write-ahead logging and disk I/O, which impacts performance.

Locks and Concurrency Control

ACID fundamentals include locks—mechanisms preventing concurrent conflicts. Shared locks (multiple readers), exclusive locks (single writer). Deadlocks occur when transactions wait for each others locks. Databases detect deadlocks and roll back one transaction. Optimistic locking (version checking) vs pessimistic locking (locks) represent different strategies.

5 Complete Interview Questions with Solutions

Question 1: Atomicity and Transaction Rollback

A bank transfer transaction fails halfway: debit succeeds, credit fails. How does atomicity prevent data loss? Explain rollback behavior.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If UPDATE 2 fails, or any constraint fails
ROLLBACK; -- Both updates reversed, no loss

-- Alternatively, if both succeed:
COMMIT; -- Both changes permanent

-- Log: Redo log records all changes
-- If crash: Redo log replays committed changes

Explanation: Atomicity ensures consistency. If UPDATE 2 fails, ROLLBACK reverses UPDATE 1. Database uses redo log to record all changes before committing. Crash recovery replays committed transactions. ACID fundamentals: the transaction is all-or-nothing, never partial.

Google – Senior Database EngineerAmazon – Data EngineerMicrosoft – Senior DBA

Question 2: Isolation Levels and Dirty Reads

With READ UNCOMMITTED isolation, transaction A reads a value that transaction B later rolls back. What is a dirty read, and why is it problematic?

Transaction A (READ UNCOMMITTED):
SELECT balance FROM accounts WHERE id = 1; -- Reads 500

Transaction B (concurrent):
UPDATE accounts SET balance = 0 WHERE id = 1; -- Balance now 0
ROLLBACK; -- Balance back to 500

Problem: A used balance=0 to calculate interest. 
Result: Incorrect calculation based on rolled-back data (dirty read).

Solution: Use READ COMMITTED or higher isolation.

Explanation: Dirty reads occur at READ UNCOMMITTED level. Transaction A sees uncommitted changes from B, which might rollback. ACID fundamentals: isolation prevents this by using locks. Higher isolation levels prevent dirty reads but reduce concurrency. Trade-off: consistency vs speed.

Meta – Senior Database EngineerOracle – Data ArchitectIBM – Senior DBA

Question 3: Deadlock Detection and Prevention

Transaction A locks row 1 then waits for row 2. Transaction B locks row 2 then waits for row 1. How do databases detect and resolve this deadlock?

Transaction A: LOCK row 1 → WAIT for row 2
Transaction B: LOCK row 2 → WAIT for row 1
(Circular wait → Deadlock)

Database detects:
- Timeout: If wait exceeds threshold, kill one
- Wait-for graph: Detects cycles, kills loser

Prevention:
- Lock rows in consistent order (1 before 2 always)
- Use optimistic locking (version numbers, no locks)
- Reduce transaction scope (less time holding locks)

Explanation: Deadlocks are circular waits for resources. Databases detect them and roll back one transaction (victim). ACID fundamentals: prevention is better than detection. Lock rows in consistent order across all transactions. Optimistic locking trades write conflicts for no locks.

Stripe – Senior Data EngineerLinkedIn – Database Architect

Question 4: Phantom Reads and REPEATABLE READ

Transaction A queries rows WHERE age > 50. Between queries, transaction B inserts new age=55 row. Why is this a phantom read, and when does REPEATABLE READ prevent it?

Transaction A (REPEATABLE READ):
SELECT COUNT(*) FROM users WHERE age > 50; -- Returns 10

Transaction B (concurrent):
INSERT INTO users VALUES (age=55);
COMMIT;

Transaction A (second query):
SELECT COUNT(*) FROM users WHERE age > 50; -- Returns 11 (phantom!)

REPEATABLE READ prevents this by locking the range.
SERIALIZABLE prevents phantom reads entirely.

Explanation: Phantom reads occur when rows appear or disappear between queries. REPEATABLE READ locks individual rows, not ranges. SERIALIZABLE locks the entire range, preventing inserts. ACID fundamentals: isolation level determines which anomalies are prevented.

Google – Senior Data EngineerAmazon – Database Architect

Question 5: Optimistic vs Pessimistic Locking

A document editing system uses optimistic locking (version numbers) instead of pessimistic locks. What are the trade-offs and when would each be appropriate?

-- Pessimistic: Lock immediately
BEGIN;
LOCK TABLE documents FOR UPDATE WHERE id = 1;
-- Other transactions wait
UPDATE documents SET content = ... WHERE id = 1;
COMMIT;

-- Optimistic: Use version
UPDATE documents SET content = ..., version = version + 1
WHERE id = 1 AND version = @old_version;
-- If rows affected = 0, conflict detected

Optimistic: Better concurrency, conflicts possible
Pessimistic: No conflicts, lower concurrency

Explanation: Optimistic locking assumes conflicts are rare, avoids locks. If update fails (version mismatch), retry. Pessimistic locking holds locks, preventing conflicts but reducing concurrency. ACID fundamentals: choice depends on contention. High read, low write conflicts → optimistic. High write conflicts → pessimistic.

Microsoft – Senior Database ArchitectMeta – Data Engineer

15 Practice Questions (Test Yourself)

Question 6: If a system requires that certain financial transactions never partially execute due to regulatory compliance requirements, which ACID property is most critical, and how would you implement it technically?

Google – Senior Data EngineerAmazon – Senior DBA

Question 7: When an application uses READ COMMITTED isolation and experiences inconsistent results across different queries within the same logical operation, how would upgrading to REPEATABLE READ isolation help resolve this issue?

Microsoft – Senior Database EngineerMeta – Data Architect

Question 8: How would you design a retry mechanism when a transaction fails due to deadlock detection, ensuring that the application doesnt enter an infinite retry loop?

Oracle – Senior Data ArchitectIBM – Database EngineerLinkedIn – Senior DBA

Question 9: When designing a system with multiple databases that need to execute transactions across both consistently, would a two-phase commit protocol be necessary, and what challenges does it introduce?

Stripe – Senior Data EngineerGoogle – Database Architect

Question 10: In a high-concurrency system where lock contention is reducing throughput, would switching to optimistic locking universally be beneficial, or are there scenarios where pessimistic locking is still preferable?

Amazon – Senior Database ArchitectMicrosoft – Data EngineerMeta – Senior DBA

Question 11: If durability requires flushing writes to disk, which is slower than keeping data in memory, how do databases balance the durability guarantee with performance requirements?

Flipkart – Senior Database EngineerLinkedIn – Data Architect

Question 12: When a stored procedure updates multiple tables as part of a single logical transaction, what mechanisms ensure that consistency constraints are not violated if the procedure fails partway through?

Google – Senior Data ArchitectOracle – Senior DBAIBM – Data Architect

Question 13: If two transactions concurrently modify the same row using optimistic locking with version numbers, how would you determine which transactions changes should take precedence in a conflict scenario?

Stripe – Database EngineerAmazon – Senior Data Engineer

Question 14: In a distributed system where transactions span multiple network calls, how would you implement the ACID properties when some calls may timeout or fail midway?

Microsoft – Senior Data ArchitectMeta – Database EngineerGoogle – BI Architect

Question 15: If a database supports both MVCC (multi-version concurrency control) and traditional locking, when would you choose one over the other to optimize for your applications read/write patterns?

LinkedIn – Senior Data EngineerFlipkart – Data Architect

Question 16: When a transaction is long-running and holds locks on many rows, how does this impact overall system concurrency, and what strategies would you use to minimize the lock-holding duration?

Stripe – Senior Data ArchitectAmazon – Database EngineerOracle – Senior DBA

Question 17: If an application experiences frequent transaction rollbacks due to conflicts under high concurrency, what are the root causes, and how would you diagnose whether to tune isolation levels or refactor the application logic?

Google – Senior Data EngineerIBM – Data Architect

Question 18: When implementing compensating transactions to handle failures in a distributed system, how would you ensure that your application remains consistent even if compensation itself fails?

Meta – Senior Data ArchitectMicrosoft – Database EngineerLinkedIn – Senior BI Engineer

Question 19: If a database migration requires changing isolation levels on a production system without downtime, what steps would you take to ensure ACID properties are maintained during and after the transition?

Stripe – Database EngineerAmazon – Senior Data Architect

Question 20: When designing a system that must meet both ACID requirements and high availability across geographically distributed datacenters, what fundamental trade-offs must you accept?

Google – Senior Database ArchitectOracle – Senior DBAIBM – Data Architect

Ready to Master ACID Properties & Concurrency Control?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.