This content originally appeared on DEV Community and was authored by Ilakkiya
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.
SQL #Database #Transactions #Deadlocks #Recovery #Learning #DevCommunity
This content originally appeared on DEV Community and was authored by Ilakkiya
Ilakkiya | Sciencx (2025-10-04T18:31:39+00:00) Transactions, Deadlocks & Log-Based Recovery in SQL. Retrieved from https://www.scien.cx/2025/10/04/transactions-deadlocks-log-based-recovery-in-sql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.