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