This content originally appeared on DEV Community and was authored by Boopathy.S
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!!!
This content originally appeared on DEV Community and was authored by Boopathy.S
Boopathy.S | Sciencx (2025-10-04T02:24:32+00:00) Transactions, Deadlocks & Log-Based Recovery in MySQL. Retrieved from https://www.scien.cx/2025/10/04/transactions-deadlocks-log-based-recovery-in-mysql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.