Transactions, Deadlocks & Log Based Recovery

Transaction

A transaction is a single logical unit of work that consists of one or more SQL operations.

It must follow the ACID properties:

• A – Atomicity: Either all operations succeed or none.
• C – Consistency: Database remains in a valid state before & after the transaction.
• I – Isolation: Transactions don’t interfere with each other.
• D – Durability: Once committed, changes are permanent.

Deadlock

A deadlock occurs when two or more transactions hold locks on resources and each waits for the other to release a lock, causing the system to freeze until one transaction is rolled back.

Example:

• Transaction 1 locks Alice’s account and waits for Bob’s.
• Transaction 2 locks Bob’s account and waits for Alice’s → Deadlock.

Log-Based Recovery

Databases maintain logs (Write-Ahead Logs / Binary Logs) to record every transaction.

• If a crash occurs before commit → use logs to undo incomplete transactions.
• If after commit → logs are used to redo committed transactions.

Schema
Use a single table Accounts:

`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);`

1. Transaction – Atomicity & Rollback

Balances remain unchanged (Alice: 1000, Bob: 1500) — proves Atomicity (no partial update).

2. Deadlock Simulation

Session 1

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

Session 2

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

Result: Deadlock occurs

Database automatically detects and terminates one transaction:

ERROR 1213 (40001): Deadlock found; try restarting transaction

Log-Based Recovery

You’ll find entries for:

• UPDATE (before rollback)
• ROLLBACK (undo recorded in log)

Confirms Undo/Redo logging works for recovery.