Transaction management is where DBMS becomes reliable under many users and failures. PSC questions frequently test ACID, serializability, locking, deadlock, timestamp protocols, logging, recovery and query optimization using indexes.

Concept Teaching

A DBMS must allow concurrency without corrupting data. Isolation controls interleaving, recovery handles crashes, and optimization makes declarative SQL efficient. These are linked: indexes speed queries but affect updates and locking behavior.

ACID Properties

ACID is a must-know definition.

Concurrency Problems

These anomalies motivate concurrency control.

• Lost update: one update overwrites another.
• Dirty read: read uncommitted data.
• Non-repeatable read: same row read twice gives different values.
• Phantom read: repeated predicate query returns new/deleted rows.
• Inconsistent analysis: aggregate reads mixed old/new values.

Concurrency Control

Protocols enforce isolation.

• Two-phase locking has growing phase then shrinking phase.
• Strict 2PL holds write locks until commit/abort.
• Shared lock allows read; exclusive lock allows write.
• Timestamp ordering orders transactions by timestamps.
• Optimistic control validates before commit.
• Deadlock can be prevented, detected or timed out.

Recovery

Recovery restores database after crash.

• Write-ahead logging requires log before data page update reaches disk.
• Undo reverses uncommitted changes.
• Redo reapplies committed changes not yet on disk.
• Checkpoint limits recovery work.
• Shadow paging uses alternate pages instead of log-style updates.

Optimization and Indexing

SQL is declarative; DBMS chooses physical plan.

• Selection pushdown reduces intermediate rows.
• Projection pushdown reduces columns.
• Join order strongly affects cost.
• Index scan can avoid full table scan.
• B+ tree supports equality and range search.
• Hash index supports equality lookup but not range efficiently.

Engineering Mechanism

• Transaction begins and reads/writes data items.
• Concurrency control grants locks or validates timestamps.
• Log records are written for update/commit/abort.
• Commit makes transaction durable.
• Crash recovery uses undo/redo from log and checkpoint.
• Optimizer estimates costs and chooses access paths/join order.

Diagrams / Models To Draw

• Draw ACID summary box.
• Draw schedule showing dirty read.
• Draw two-phase locking timeline.
• Draw write-ahead logging flow.
• Draw query optimizer pipeline: parse, rewrite, plan, execute.

Formulas, Algorithms and Rules

• ACID = Atomicity + Consistency + Isolation + Durability.
• WAL rule: log record must reach stable storage before corresponding data page.
• Conflict serializability uses precedence graph acyclicity.
• Cost roughly depends on I/O pages read, index selectivity and join method.

Worked Example

In bank transfer, debit A and credit B must be one transaction. If crash occurs after debit but before credit, atomicity and log recovery ensure the partial debit is undone or completed consistently.

Subjective Answer Pattern

• Define transaction and ACID.
• Explain concurrency anomalies.
• Describe locking/serializability.
• Explain recovery using log and checkpoint.
• Discuss query optimization and indexing.

Common Engineering Mistakes

• Saying consistency means same as isolation.
• Ignoring dirty reads.
• Confusing undo and redo.
• Assuming all indexes help all queries.
• Forgetting deadlock possibility in locking.

MCQ Revision

• What does durability mean?
• What is dirty read?
• What are phases of 2PL?
• What is WAL?
• Which index supports range query?
• What does optimizer choose?

Final Summary

• Transactions provide reliable units of work.
• Concurrency control preserves isolation.
• Recovery protects committed data and removes partial work.
• Optimization makes SQL efficient.
• Indexes are powerful but workload-dependent.