SQL · Queries that survive scale
What is a transaction and what does ACID mean?
Short answer
A transaction groups statements into one atomic unit that either commits fully or rolls back. ACID stands for atomicity, consistency, isolation, and durability.
A transaction protects a set of changes from partial completion. If a statement fails or the application decides to abort, ROLLBACK undoes the work. If it commits, the database guarantees the changes are durable according to its configuration.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;The ACID properties
- Atomicity: all changes apply or none do
- Consistency: constraints and invariants hold at commit
- Isolation: concurrent transactions do not see each other's uncommitted work at the chosen level
- Durability: committed changes survive a crash, subject to fsync and replication settings
Keep transactions short. A transaction that waits on a user, calls a slow external API, or scans a huge table holds locks and can block other work. Do the slow preparation outside the transaction and commit only the database changes.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1What happens if the application crashes before COMMIT?
- 2Why should transactions be short?
- 3What is the difference between COMMIT and ROLLBACK?
- 4Does durability require synchronous replication?
Say it in the interview
A transaction is an atomic unit of work that either commits or rolls back. ACID describes the guarantees: atomicity, consistency, isolation, and durability. I use explicit transactions for multi-statement changes such as a transfer, and I keep them short so locks are not held while waiting on external work. Durability depends on the database's fsync and replication settings, not just on COMMIT.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.