Data consistency
ACID transactions: interview questions and how to answer them
ACID describes atomicity, consistency, isolation, and durability: a transaction is a boundary around state changes, not a magic guarantee that every business workflow is safe.
Written and reviewed by Sahil Srivastav
What it actually is
Atomicity makes committed changes appear as one unit; consistency means declared database constraints hold after commit; isolation controls what concurrent transactions may observe; durability means committed data survives the failure model the database promises.
ACID does not mean serialisable execution, does not include remote API calls, and does not repair an incorrect invariant. The transaction must contain the rows and constraints needed to enforce the business rule.
Why it matters in production
A transaction is the smallest reliable unit for changing related rows. Without it, a process crash after debiting one account but before crediting another leaves a partial transfer.
The hard production question is boundary selection: a database transaction cannot roll back an email, payment provider call, or message already sent. Those effects need an outbox, compensation, or an idempotent retry contract.
How it works
Atomicity
Commit makes all writes visible together; rollback removes the transaction’s writes. Failure handling must include the client losing the connection after commit.
Consistency
Constraints, triggers, and application checks define valid states. ACID consistency is not the same as CAP linearizability.
Isolation
Isolation levels choose which concurrent effects are visible and which anomalies the engine prevents. Stronger isolation can abort or block more work.
Durability
A successful commit normally means redo information is durable according to the configured synchronous replication and storage policy, not that every replica has applied it.
Detailed boundary
the database boundary
Operational consequence
remote side effects and durability
Implementing it
Keep transactions short and database-only. Put network calls outside and persist intent before dispatching them.
Name constraints in the database so concurrent callers share one enforcement point.
Define retry behaviour for deadlocks and serialisation failures; retry only an operation whose effects are idempotent.
Use a two-sided test for this boundary: drive the normal path and the failure path concurrently, then inspect the state that survives the race. For acid transactions, the useful assertion is the invariant after recovery, not merely a successful response from one caller.
Document the limit and the signal that tells an operator to change it. A production review of acid transactions should name the protected resource, the caller deadline, the expected overload decision, and the evidence that would distinguish a local bug from downstream saturation.
A focused review of acid transactions should separate the mechanism from its policy. Reproduce one normal request, one boundary case, and one concurrent failure; record the state transition, the resource consumed, and the signal an operator would see. Then state what the caller is allowed to retry and what must be reconciled manually. This makes acid transactions testable in a repository rather than a vocabulary answer.
Interview questions and how to answer them
Does ACID make a payment and email one atomic action?
No. The database can atomically record the payment, but it cannot roll back an email provider. Record an outbox event and make sending idempotent.
What does consistency mean in ACID?
The transaction preserves declared database invariants such as foreign keys and checks. It is not a promise of globally latest reads.
Can a committed transaction be lost?
It should survive the configured failure model, but asynchronous replicas, unsafe storage, or acknowledged writes before fsync weaken that promise.
Why keep transactions short?
Locks, snapshots, connections, and retained versions last longer, increasing contention and resource pressure.
What evidence would you inspect for acid transactions?
Measure the boundary named in the design, compare it with the caller deadline and resource budget, and reproduce the contention or failure with more than one concurrent worker.
What is the tempting fix for this problem?
Changing a timeout, pool, or retry count alone usually moves the queue. First establish the invariant, then make the bounded mechanism and its failure outcome explicit.
Answers that lose the round
- Calling a multi-step workflow atomic because each step uses a transaction
- Assuming ACID implies serialisable isolation
- Relying on application checks without unique or check constraints
- Holding transactions across HTTP calls
- Treating a lost response as proof that the transaction rolled back
- Ignoring replica durability settings
- Treating the local mechanism as a complete production guarantee
- Changing the limit without measuring the resource it protects
FAQ
Are NoSQL databases non-ACID?
Many provide atomic operations or multi-record transactions with different scope and costs; the label depends on the product and operation.
Is a transaction automatically thread-safe?
No. It isolates database operations; shared in-memory state still needs its own coordination.
Should every request be one transaction?
Only the database changes that must commit together. Long reads and remote work often belong outside the write transaction.