DBMS & SQL · Lesson 11 of 13 · Updated ·
Transactions and ACID — COMMIT, ROLLBACK, Isolation Levels and Locks
A transaction is a group of SQL statements that must succeed or fail as one unit. The classic example: transferring ₹500 from A to B is two updates — and you never want only one of them to happen.
1. COMMIT and ROLLBACK
CREATE TABLE accounts (id INT PRIMARY KEY, holder VARCHAR(20), balance INT CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ravi', 300);
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
SELECT holder, balance FROM accounts ORDER BY id;
holder | balance
Asha | 500
Ravi | 800
If something goes wrong in the middle, ROLLBACK undoes everything since BEGIN:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK;
SELECT holder, balance FROM accounts WHERE id = 1;
holder | balance
Asha | 500
SAVEPOINT name marks a point you can roll back to without undoing the whole transaction.
2. ACID
| Property | Meaning | In the transfer |
|---|---|---|
| Atomicity | all statements happen, or none | never debit without credit |
| Consistency | data moves from one valid state to another; rules (constraints) hold | balance never negative; total money unchanged |
| Isolation | concurrent transactions don't see each other's half-done work | another query never sees ₹500 "in flight" |
| Durability | once committed, it survives crashes | the transfer is still there after a power cut |
How databases achieve it: a write-ahead log (for atomicity and durability), constraints (consistency) and locks / MVCC (isolation).
3. Concurrency problems
When many transactions run at once, without isolation you get:
| Problem | What happens |
|---|---|
| Dirty read | T1 reads data T2 changed but hasn't committed; T2 rolls back → T1 used fake data |
| Non-repeatable read | T1 reads a row twice and gets different values because T2 updated it in between |
| Phantom read | T1 runs the same query twice and gets new rows because T2 inserted them |
| Lost update | T1 and T2 both read balance 500, both add 100, both write 600 — one update is lost |
4. Isolation levels
| Level | Dirty read | Non-repeatable read | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible |
| READ COMMITTED (PostgreSQL, Oracle default) | prevented | possible | possible |
| REPEATABLE READ (MySQL InnoDB default) | prevented | prevented | possible* |
| SERIALIZABLE | prevented | prevented | prevented |
*InnoDB also blocks most phantoms using gap locks. Higher isolation = safer but slower (more waiting).
5. Locks and deadlocks
- Shared (read) lock — many can read together.
- Exclusive (write) lock — one writer, nobody else.
- Deadlock — T1 locks row A and waits for B; T2 locks B and waits for A. The database detects this and kills one transaction (which you should retry).
Prevent deadlocks: access tables/rows in the same order everywhere, keep transactions short.
Common mistakes
- Running multi-step money/stock updates without a transaction.
- Keeping a transaction open while waiting for user input (locks are held the whole time).
- Assuming the default isolation level is the same in every database.
Quick recap
- Transaction = all-or-nothing group: BEGIN … COMMIT / ROLLBACK (SAVEPOINT for partial).
- ACID: Atomicity, Consistency, Isolation, Durability.
- Problems: dirty read, non-repeatable read, phantom read, lost update.
- Isolation levels trade safety for speed; deadlocks → one transaction is rolled back.