ACID & transactions
DBMS · 10 interview questions
A transaction is a group of operations treated as one unit: either all of them take effect or none do. ACID names the four guarantees a transactional database provides.
Atomicity is all-or-nothing, implemented with an undo log so a failed transaction can be rolled back. Consistency means a transaction moves the database from one valid state to another, where valid means satisfying declared constraints. Isolation means concurrent transactions don't observe each other's partial work. Durability means once commit returns, the change survives a crash, usually guaranteed by writing to a log before the data pages.
Consistency is the letter worth being precise about. It is not something the database invents — it's the preservation of constraints you declared. The database enforces them; deciding what 'valid' means is the schema designer's job.
ACID & transactions interview questions
- What does ACID stand for?
- Atomicity, Consistency, Isolation, Durability.
- What does atomicity guarantee, and how is it implemented?
- That a transaction either fully commits or leaves no trace. Implemented with an undo log: before changing data, the old value is recorded so a rollback can restore it.
- What does the C in ACID actually mean?
- That a transaction takes the database from one valid state to another, where validity means the constraints you declared — keys, foreign keys, checks. The database enforces them; it does not decide what valid means.
- Why they ask: Frequently confused with the C in CAP, which is a completely different property.
- Is the C in ACID the same as the C in CAP?
- No. ACID's consistency is constraint preservation within a transaction. CAP's consistency is about distributed replicas — whether every node returns the most recent write. Different problems entirely.
- How is durability achieved without writing data pages on every commit?
- Write-ahead logging. The change is appended to a sequential log and flushed before commit returns; the data pages can be written later. Sequential log writes are far cheaper than random page writes, and the log can replay anything lost in a crash.
- What do COMMIT and ROLLBACK do?
- COMMIT makes the transaction's changes permanent and visible to others. ROLLBACK discards them, restoring the state from before the transaction began.
- What is a savepoint?
- A marker inside a transaction you can roll back to without abandoning the whole transaction — useful when one optional step fails but the rest of the work should stand.
- What is autocommit?
- A mode where each statement is its own transaction, committed immediately. It's the default in many clients, which is why people are surprised that a failed multi-statement script leaves partial work behind.
- What is two-phase commit?
- A protocol for committing a transaction across several systems. A coordinator asks every participant to prepare; if all agree, it tells them to commit. It's blocking — participants that have voted must wait for the coordinator, so a coordinator failure can stall them.
- Why are long-running transactions a problem?
- They hold locks or old row versions for their whole life, blocking other writers or forcing the database to retain versions it would otherwise clean up. They also make deadlock more likely and rollback more expensive.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start free for 7 days