What is a transaction and what does ACID mean?
Quick Answer
A transaction is a unit of work that either fully completes or has no effect. ACID is the set of guarantees: Atomicity (all-or-nothing), Consistency (constraints hold before and after), Isolation (concurrent transactions don't corrupt each other's view — tuned by isolation level), and Durability (once committed, it survives a crash).
A transaction is a group of operations that has to succeed or fail as a whole. ACID describes the guarantees. Atomicity is all-or-nothing — if one statement fails, the whole thing rolls back using the transaction log. Consistency means all the constraints and rules still hold afterwards. Isolation means concurrent transactions don't see each other's half-finished work, and you tune exactly how strict that is with the isolation level — read committed is the default, snapshot uses row versioning so readers don't block writers. Durability means once it's committed it's on disk and survives a crash, because the log is written ahead of the commit. In practice the main things I worry about are keeping transactions short and avoiding deadlocks.
You wrap statements in BEGIN TRAN ... COMMIT (or ROLLBACK). SQL Server also treats each single statement as an implicit transaction.
- Atomicity — if the second
UPDATEfails, the first is undone. Implemented with the transaction log. - Consistency — a transaction moves the database from one valid state to another; constraints, triggers and FKs are enforced.
- Isolation — controlled by the isolation level:
- Read Uncommitted — can read dirty (uncommitted) data.
- Read Committed (default) — only committed data; still allows non-repeatable reads.
- Repeatable Read — rows you read stay stable; phantoms still possible.
- Serializable — full isolation via range locks.
- Snapshot / RCSI — row-versioning: readers see a consistent snapshot without blocking writers.
- Durability — the log record is hardened to disk before
COMMITreturns (write-ahead logging).
Related interview topics: locking vs blocking, deadlocks (two transactions each holding what the other needs — SQL Server picks a victim), and keeping transactions short to reduce contention.
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION; -- both, or neither
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;