A transaction is a sequence of database operations executed as a single logical unit of work. Transactions must satisfy ACID: Atomicity, Consistency, Isolation, Durability. Concurrency control manages simultaneous transactions to prevent conflicts — using locking protocols (Two-Phase Locking), timestamps, and serialisability. GATE tests this with numerical questions on serialisability, lock conflicts, and deadlock detection.
Real-life analogy: Bank transfer
Transferring money requires two steps: deduct from A, add to B. If the system crashes after step 1 but before step 2, money vanishes — inconsistent state. A transaction groups both steps: either BOTH succeed (COMMIT) or NEITHER happens (ROLLBACK). This is Atomicity.
ACID Properties
| Property | Meaning | How enforced | Bank example |
|---|---|---|---|
| Atomicity | All or nothing | Undo logging, rollback | Both debit and credit happen, or neither |
| Consistency | DB goes from one valid state to another | Integrity constraints | Total money in system unchanged after transfer |
| Isolation | Concurrent transactions appear serial | Locking, MVCC | Another user sees either old or new balance, never partial |
| Durability | Committed changes survive failures | Write-ahead logging, disk persistence | Transfer is permanent even if server crashes after commit |
Bank transfer with proper transaction handling
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 1000
WHERE AccountID = 'A' AND Balance >= 1000;
-- Check if sufficient balance existed
-- If not: ROLLBACK TO SAVEPOINT and raise error
UPDATE Account SET Balance = Balance + 1000
WHERE AccountID = 'B';
COMMIT; -- Both updates committed atomically
-- On any error: ROLLBACK (reverts both updates)
-- Isolation levels:
-- READ UNCOMMITTED: Sees uncommitted changes (dirty reads allowed)
-- READ COMMITTED: Only sees committed data. PostgreSQL default.
-- REPEATABLE READ: Same query gives same result within transaction
-- SERIALIZABLE: Full isolation — as if ran one after anotherConcurrency anomalies and Two-Phase Locking
| Anomaly | What happens | Isolation level that prevents it |
|---|---|---|
| Dirty Read | T1 reads data modified by T2 which then rolls back | READ COMMITTED and above |
| Non-repeatable Read | T1 reads row, T2 updates it, T1 reads again — different result | REPEATABLE READ and above |
| Phantom Read | T1 queries rows, T2 inserts new matching rows, T1 re-queries — new rows appear | SERIALIZABLE only |
| Lost Update | T1 and T2 both read X, both modify — second overwrite loses first | Any proper locking |
Two-Phase Locking (2PL): Growing phase — acquire locks, never release. Shrinking phase — release locks, never acquire new ones. If all transactions follow 2PL, the schedule is guaranteed conflict-serialisable. Strict 2PL: Hold all exclusive locks until commit/abort — prevents cascading rollbacks.
Deadlock — GATE numerical question type
Deadlock: T1 holds lock on A, waits for B; T2 holds lock on B, waits for A — circular wait. Detection: Wait-for graph — cycle = deadlock. Resolution: abort the youngest transaction (victim). Prevention: Wait-Die or Wound-Wait protocols.
Serializability and precedence graph
Checking conflict serializability
from collections import defaultdict
def is_conflict_serializable(schedule):
"""
Schedule: list of (transaction_id, operation, data_item)
Conflicts: same data item, different transactions, at least one write.
Add edge Ti→Tj if Ti has conflicting op before Tj on same item.
Cycle in graph = NOT serializable.
"""
graph = defaultdict(set)
for i in range(len(schedule)):
ti, op_i, item_i = schedule[i]
for j in range(i+1, len(schedule)):
tj, op_j, item_j = schedule[j]
if ti != tj and item_i == item_j and (op_i == 'W' or op_j == 'W'):
graph[ti].add(tj)
# DFS cycle detection
visited, rec = set(), set()
def dfs(node):
visited.add(node); rec.add(node)
for nb in graph[node]:
if nb not in visited:
if dfs(nb): return True
elif nb in rec: return True
rec.discard(node); return False
txns = set(t for t,_,_ in schedule)
return not any(dfs(t) for t in txns if t not in visited), dict(graph)
schedule = [('T1','R','A'),('T2','R','A'),('T1','W','A'),('T2','W','A')]
ok, g = is_conflict_serializable(schedule)
print(f"Serializable: {ok}, Graph: {g}") # True, {T1:{T2}}Practice questions (GATE-style)
- Precedence graph has cycle T1→T2→T3→T1. Is it conflict-serializable? (Answer: No — a cycle in the precedence graph means NOT conflict-serializable.)
- Which ACID property is enforced by write-ahead logging (WAL)? (Answer: Durability — WAL writes changes to disk log before modifying actual data. Crashed system replays log on recovery.)
- T1 holds a Shared (S) lock on X. Can T2 acquire an Exclusive (X) lock on X? (Answer: No — S-X locks conflict. T2 must wait. S-S: compatible. X-X: incompatible. S-X: incompatible.)
- Strict 2PL vs regular 2PL: (Answer: Strict 2PL holds all exclusive locks until commit/abort. Regular 2PL can release locks during shrinking phase before commit. Strict 2PL prevents cascading rollbacks.)
- Difference between dirty read and non-repeatable read: (Answer: Dirty read = reading uncommitted data from another transaction. Non-repeatable read = reading same row twice in one transaction and getting different results because another COMMITTED transaction modified it.)
On LumiChats
LumiChats can draw precedence graphs, determine serialisability, identify deadlocks, and explain ACID violations for any scenario. Great for GATE numerical questions on transactions.
Try it free