TRANSACTIONS , DEADLOCKS AND LOG BASED RECOVERY

Transactions, Deadlocks & Log-Based Recovery in SQL: A Practical Guide

Databases must ensure data integrity and reliability even under concurrent access, system crashes, or unexpected failures. Three critical concepts that make this possible are transactions, deadlocks, and log-based recovery.

In this post, we’ll explore these with practical SQL examples using a simple Accounts table.

1️⃣ Setting Up the Accounts Table

Let’s start by creating a sample table:

CREATE TABLE Accounts (
    acc_no INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);

INSERT INTO Accounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

This simple table will help us demonstrate atomic transactions, deadlocks, and logging.

2️⃣ Transactions – Atomicity & Rollback

A transaction is a sequence of operations that must either complete entirely or not at all.

Example: transferring 500 from Alice to Bob, then rolling back:

BEGIN TRANSACTION;

-- Deduct from Alice
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;

-- Add to Bob
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;

-- Rollback transaction
ROLLBACK;

-- Verify balances
SELECT * FROM Accounts;

Result: No partial update occurs; balances remain unchanged.
This demonstrates atomicity.

3️⃣ Deadlock Simulation

A deadlock happens when two transactions block each other by waiting for locks on resources.

You need two separate database sessions to simulate:

Session 1

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 1; -- locks Alice
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 2; -- waits for Bob

Session 2

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 2; -- locks Bob
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 1; -- waits for Alice

Expected: The database detects a deadlock and aborts one transaction, preventing indefinite blocking.

4️⃣ Log-Based Recovery

Databases maintain logs to undo or redo changes in case of crashes. Most modern databases use:

• MySQL (InnoDB) → undo logs and binary logs
• PostgreSQL → Write-Ahead Logging (WAL)

Example:

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;

ROLLBACK;

• The change is recorded in the log.
• Rollback ensures the balance of Charlie remains 2000, even if the system crashes.
• Logs can be inspected to verify undo/redo operations.

5️⃣ Key Takeaways

Concept	Description
Atomicity	Transactions are all-or-nothing; rollback cancels partial changes.
Deadlock	Occurs when transactions wait for each other’s locks; DB resolves automatically.
Log-Based Recovery	Logs allow recovery to a consistent state after crashes or rollbacks.

Conclusion

Understanding transactions, deadlocks, and logging is critical for building reliable, robust database applications.

• Transactions ensure consistency even if operations fail.
• Deadlocks are detected and resolved by the database engine.
• Logs provide durability and recovery mechanisms.

Tip for Developers:
Test deadlocks safely using two database sessions and simulate transactions with rollback to see ACID properties in action.