Database systems are designed to handle multiple operations efficiently and reliably.
In this post, we’ll explore three key concepts that keep databases safe and consistent: Transactions, Deadlocks, and Log-Based Recovery.
We’ll use a simple Accounts table to demonstrate these concepts in action.
Creating the Accounts Table
We start with a simple table to represent bank accounts and insert sample data.
Transactions — Atomicity & Rollback
Definition:
A transaction is a sequence of operations that must be executed completely or not at all.
Example: Transfer Money with Rollback
Objective: Transfer 700 from Diana to Ethan, but rollback before committing.
Result: No partial update occurs; balances remain unchanged.
Deadlock Simulation
Definition:
A deadlock occurs when two transactions block each other, waiting for resources the other holds.
Scenario
Session 1: Lock Diana's account & try to update Ethan's account
Session 2:Lock Ethan's account & Try to update Diana's account
- Both sessions wait indefinitely, creating a deadlock that the DBMS detects and resolves.
Log-Based Recovery
Definition:
Logging ensures that every database change is recorded, so the system can undo or redo transactions during failures.
Example: Transaction with Undo Logging
Result: The rollback operation is recorded in the log, ensuring data consistency.
Summary
- Transactions: Ensure atomicity — all or nothing.
- Deadlocks: Occur when transactions wait on each other; DBMS resolves them automatically.
- Log-Based Recovery: Maintains a history of changes to recover from failures.
These mechanisms are fundamental to robust, reliable, and fault-tolerant database systems.
Top comments (0)