A transaction is a sequence of operations treated as a single logical unit — a money transfer that debits one account and credits another must either fully happen or not at all. The DBMS guarantees this through the ACID properties, and it controls simultaneous transactions with isolation levels and locking.
ACID
Atomicity: all operations succeed or none do. Consistency: the database moves from one valid state to another. Isolation: concurrent transactions don't see each other's partial work. Durability: once committed, changes survive crashes.
Concurrency problems & isolation levels
| Problem | What happens |
|---|---|
| Dirty read | reading another transaction's uncommitted change |
| Non-repeatable read | a re-read returns a different value (it was updated) |
| Phantom read | a re-run query returns new rows (inserted by another) |
⚡ The edge
- Remember ACID by what each protects: Atomicity = all-or-nothing, Consistency = valid states, Isolation = no interference, Durability = survives crashes.
- Higher isolation levels (up to Serializable) prevent more anomalies but reduce concurrency. The order of strength: Read Uncommitted < Read Committed < Repeatable Read < Serializable.
Worked example
'Explain the ACID properties with the bank-transfer example.'
- Atomicity: debit and credit both happen or neither does — money is never lost mid-way.
- Consistency: total balance across accounts is preserved; Isolation: a concurrent transfer doesn't see the half-done state.
- Durability: once the transfer commits, it stays even if the server crashes a second later.
Answer: ACID = atomic all-or-nothing, consistent valid states, isolated from others, durable after commit.
Worked example
'What is a deadlock in a database and how is it handled?'
- Two transactions each hold a lock the other needs, so both wait forever — a deadlock.
- DBMSs typically detect deadlocks (a wait-for cycle) and abort one transaction (the victim) to break it.
- Prevention options include acquiring locks in a consistent order or using timeouts.
Answer: A cyclic lock wait; the DBMS detects it and rolls back one transaction to break the cycle.
⚠ Watch out
- Don't confuse consistency (valid states / constraints) with isolation (not seeing others' partial work).
- Higher isolation = fewer anomalies but lower concurrency/throughput — it's a trade-off.
- A dirty read (uncommitted data) is different from a non-repeatable read (committed update between reads).