Transactions, Deadlocks & Log-Based Recovery in MySQL

In this blog, we’ll explore these concepts hands-on using MySQL with a simple Accounts table.

-- Create a new database
CREATE DATABASE IF NOT EXISTS BankDB;
USE BankDB;

-- Drop table if it already exists
DROP TABLE IF EXISTS Accounts;

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

-- Insert sample records
INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

-- View initial data
SELECT * FROM Accounts;

✅ Output:
acc_no name balance
1 Alice 1000
2 Bob 1500
3 Charlie 2000

1️⃣ Transaction – Atomicity & Rollback
Transactions should be atomic: either all operations succeed or none.
-- Start transaction
START TRANSACTION;

-- Transfer 500 from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';

-- Rollback the transaction
ROLLBACK;

-- Check balances (unchanged)
SELECT * FROM Accounts;

2️⃣ Deadlock Simulation
Deadlocks happen when two transactions wait indefinitely for each other’s locks.
Open two sessions and run:

Session 1:
START TRANSACTION;
-- Lock Alice
SELECT * FROM Accounts WHERE name='Alice' FOR UPDATE;
-- Try updating Bob
UPDATE Accounts SET balance = balance + 10 WHERE name='Bob';

Session 2:
START TRANSACTION;
-- Lock Bob
SELECT * FROM Accounts WHERE name='Bob' FOR UPDATE;
-- Try updating Alice
UPDATE Accounts SET balance = balance + 20 WHERE name='Alice';

MySQL detects the deadlock and forces one session to fail:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

3️⃣ Log-Based Recovery
MySQL uses binary logs (redo logs) and undo logs to maintain data consistency.
Let’s test rollback with logs:
-- Start transaction
START TRANSACTION;
-- Update Charlie’s balance
UPDATE Accounts SET balance = balance + 300 WHERE name = 'Charlie';
-- Rollback instead of commit
ROLLBACK;
-- Verify balance is unchanged
SELECT * FROM Accounts;
Charlie’s balance remains 2000 → MySQL’s undo logs restored the previous state.

Summary
Atomicity: Rollback ensures no partial updates.
Deadlocks: MySQL automatically detects and resolves them.
Log-Based Recovery: Undo/redo logs guarantee data consistency even after crashes.
With this, we’ve successfully demonstrated transactions, deadlock handling, and recovery in MySQL.

THANK YOU @santhoshnc sir for guiding me!!!