Transactions,Deadlocks and Log Based Recovery

💡 Objective:
To gain hands-on experience with ACID transactions, simulate deadlocks, and observe log-based recovery in a relational database system.

We’ll use a simple Accounts table to demonstrate these core database concept
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);
Step 2: Transaction – Atomicity & Rollback

A transaction is a group of SQL operations executed as a single logical unit.
Let’s test Atomicity — “all or nothing” execution.

Example: Transfer ₹500 from Alice → Bob, then rollback.
-- Start a transaction
START TRANSACTION;

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

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

-- Rollback before committing
ROLLBACK;

-- Check balances
SELECT * FROM Accounts;

Step 3: Deadlock Simulation

A deadlock occurs when two transactions hold locks that the other needs.
Let’s simulate this using two SQL sessions.

🧩 Session 1
START TRANSACTION;

-- Lock Alice’s account
SELECT * FROM Accounts WHERE name='Alice' FOR UPDATE;

-- Try to update Bob’s balance (will wait if Session 2 has locked Bob)
UPDATE Accounts SET balance = balance + 100 WHERE name='Bob';

🧩 Session 2
START TRANSACTION;

-- Lock Bob’s account
SELECT * FROM Accounts WHERE name='Bob' FOR UPDATE;

-- Try to update Alice’s balance (will wait if Session 1 has locked Alice)
UPDATE Accounts SET balance = balance + 200 WHERE name='Alice';
🧨 Result:
Database detects a deadlock automatically and aborts one transaction.

Example Error (MySQL):

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

💬 Explanation:
Each session holds one lock and waits for the other.
To prevent deadlocks → access resources in the same order and keep transactions short.

🧾 Step 4: Log-Based Recovery

Modern databases maintain logs to record every transaction’s activity.
These logs allow the system to undo uncommitted changes and redo committed ones after a crash.

Example: Observe undo logging via rollback
-- Start a transaction
START TRANSACTION;

-- Update Charlie’s balance
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Charlie';

-- Rollback
ROLLBACK;

-- Verify balance restored
SELECT * FROM Accounts WHERE name = 'Charlie';
✅ Result:
Charlie’s balance remains unchanged (2000).

💬 Explanation:
The database writes the undo log entry when you start the transaction.
When rollback is issued, the DB uses that log to restore previous values