Comprehensive DBMS Notes: Transaction
Management, Concurrency Control, and Recovery
1. Transaction Management
A transaction is a logical unit of work that contains one or more database operations (such as
insert, update, delete, or retrieve). Transaction management ensures that the database remains
consistent and reliable, even in the presence of failures. To guarantee this, DBMS follows the ACID
properties.
ACID Properties
Atomicity: A transaction is treated as a single indivisible unit. Either all operations within it are
executed, or none are. For example, in a banking transaction transferring money from Account A to
Account B, both debit and credit must succeed; if one fails, the other is rolled back.
Consistency: The database must always move from one valid state to another valid state.
Constraints, triggers, and business rules ensure that after a transaction, the integrity of the
database is preserved.
Isolation: Concurrent execution of transactions should not affect each other. Each transaction must
execute as if it were the only one in the system. For instance, if two people book the last ticket at
the same time, isolation ensures only one succeeds.
Durability: Once a transaction is committed, its results are permanent and survive system crashes
or failures. This is usually guaranteed by writing changes to stable storage.
Transaction States and Lifecycle
A transaction passes through several states during its execution:
Active: Transaction is currently executing instructions.
Partially Committed: All operations have been executed but changes are still in volatile memory.
Committed: Transaction completed successfully, and changes are made permanent.
Failed: An error occurs during execution, preventing completion.
Aborted: The system rolls back the transaction, undoing all partial changes.
2. Concurrency Control
Concurrency control ensures that when multiple transactions execute simultaneously, the integrity
of the database is maintained. Without proper control, anomalies can occur.
Problems in Concurrent Transactions
Lost Update: Occurs when two transactions update the same data, and one update overwrites the
other. Example: Two cashiers update the same account balance without seeing each other’s
changes.
Uncommitted Data (Dirty Read): Happens when one transaction reads data written by another
transaction that has not yet committed. If the writing transaction is rolled back, the reader has used
invalid data.
Inconsistent Analysis: Arises when a transaction reads data while another transaction updates it.
For example, while calculating the total balance, one transaction may be modifying some accounts.
Lock-Based Protocols
Shared Lock (S): Allows multiple transactions to read a data item but prevents writing.
Exclusive Lock (X): Allows only one transaction to both read and write a data item.
Two-Phase Locking (2PL): Ensures serializability by splitting a transaction into two phases – the
growing phase (acquiring locks) and the shrinking phase (releasing locks). Once a lock is released,
no new locks can be acquired.
Deadlock Handling in DBMS
Deadlock occurs when two or more transactions wait indefinitely for each other’s resources.
Handling techniques include:
Prevention: Ensure that circular wait conditions never occur by resource ordering.
Avoidance: Use algorithms such as Wait-Die or Wound-Wait to avoid unsafe states.
Detection and Recovery: Allow deadlocks, then detect them using wait-for graphs and recover by
aborting or rolling back transactions.
3. Database Recovery
Database recovery restores the database to a consistent state after a failure. Failures may result
from hardware issues, power loss, software bugs, or human errors.
Types of Failures
System Crash: Failures due to power outage, hardware fault, or operating system crash that result
in loss of data in volatile memory.
Media Failure: Failures caused by permanent damage to storage devices such as disk crashes,
making stored data inaccessible.
Recovery Techniques
Log-Based Recovery: Every transaction’s operations are recorded in a log. In case of failure, the
log is used for redo (reapply committed changes) and undo (rollback uncommitted transactions).
Shadow Paging: Maintains two page tables – the current and the shadow. During a transaction,
updates are made to the current table, while the shadow remains unchanged. If the transaction
commits, the current table replaces the shadow; otherwise, the shadow is retained.
Checkpointing and its Significance
Checkpointing is the process of saving the database state periodically. It helps reduce recovery
time by ensuring that, in case of a crash, only the transactions after the last checkpoint need to be
reapplied. This improves efficiency and minimizes system downtime.