💾 Transactions, Deadlocks & Log-Based Recovery in SQL

Database management systems ensure data consistency and reliability even during failures. Three major concepts that demonstrate this reliability are Transactions, Deadlocks, and Log-Based Recovery. Let’s explore each one with practical SQ

🧱 Schema Setup

We’ll start with a simple Accounts 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 table will be used throughout the examples.

1️⃣ Transaction – Atomicity & Rollback

A transaction is a set of SQL operations that are executed as a single unit.
The key property here is Atomicity — meaning all operations must succeed or none at all.

Let’s simulate a money transfer between Alice and Bob.

START TRANSACTION;

UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';

UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';

SELECT * FROM Accounts;

2️⃣ Deadlock Simulation

A deadlock occurs when two transactions hold locks that the other needs.
Let’s simulate it using two sessions in MySQL.

🪜 Session 1

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

OUTPUT:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

✅ The DBMS automatically detects and resolves the deadlock by rolling back one transaction.

3️⃣ Log-Based Recovery

Databases maintain log files (Binary Logs or WAL) to record all changes.
Let’s see how rollback is logged internally.
START TRANSACTION;

UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Charlie';

SELECT * FROM Accounts;

Now rollback:

ROLLBACK;

SELECT * FROM Accounts;

✅ The rollback is recorded in MySQL’s binary log or PostgreSQL’s WAL, ensuring that even after a crash, the database can undo incomplete transactions and redo committed ones.