# Transactions and ACID — COMMIT, ROLLBACK, Isolation Levels and Locks

Source: https://skills101.in/learn/dbms-sql/transactions-and-acid — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

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.

![ACID: Atomicity — all or nothing; Consistency — rules always hold; Isolation — transactions don't disturb each other; Durability — committed data survives a crash. A ₹500 transfer is A − 500 and B + 500: both or neither](https://skills101.in/learn-img/dbms-sql/acid.svg)

## 1. COMMIT and ROLLBACK

```sql
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;
```

```text
holder | balance
Asha | 500
Ravi | 800
```

If something goes wrong in the middle, **ROLLBACK** undoes everything since BEGIN:

```sql
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK;
SELECT holder, balance FROM accounts WHERE id = 1;
```

```text
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.


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/transactions-and-acid