DEV Community

Cover image for Transactions, Deadlocks & Log-Based Recovery in SQL
Ilakkiya
Ilakkiya

Posted on

Transactions, Deadlocks & Log-Based Recovery in SQL

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

Top comments (0)