What is a transaction in SQL and what is ACID?
A transaction is a group of SQL statements that must succeed or fail as a single unit. If any part fails, the whole transaction is rolled back so the database stays consistent. Transactions follow the ACID properties: atomicity, consistency, isolation, and durability, which guarantee reliable data even during errors or crashes.
- A transaction is a group of operations treated as one all or nothing unit.
- ACID stands for Atomicity, Consistency, Isolation, and Durability.
- Transactions keep data correct even during errors or concurrent access.
The classic example
Transferring money means subtracting from one account and adding to another. If the first step succeeds but the second fails, money would vanish. A transaction ensures both steps happen together or neither does.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both succeed, or ROLLBACK undoes everythingThe ACID properties
- Atomicity: all steps happen or none do.
- Consistency: the database moves from one valid state to another.
- Isolation: concurrent transactions do not corrupt each other.
- Durability: once committed, changes survive a crash.
Lead with the bank transfer example, then name the ACID letters. A concrete story followed by the four properties is far more convincing than reciting definitions alone.
Frequently asked questions
What does atomicity mean?
It means a transaction fully completes or fully fails. If any step fails, all earlier steps are rolled back so no partial change remains.
What are COMMIT and ROLLBACK?
COMMIT saves all changes in a transaction permanently, while ROLLBACK undoes them, returning the data to its state before the transaction began.
Common follow up questions
Related interview questions
Want the full SQL guide?
Read every SQL concept with notes, diagrams, and code in one place. Track your progress as you go.
Open the SQL guide All SQL questions