Transactions, Deadlocks & Log-Based Recovery in MySQL

Introduction

Databases are the backbone of most applications. To maintain data integrity and consistency Transactions, Deadlocks, and Log-based recovery are crucial. In this tutorial, we’ll explore these concepts using a simple Accounts table in MySQL..

Schema Setup

We start by creating a single table Accounts and insert some sample data.

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 for all examples below.

Transaction – Atomicity & Rollback

Transactions ensure atomicity, meaning a series of operations either all succeed or all fail.

We will transfer 500 from Alice to Bob but rollback the transaction to ensure no partial updates occur.

START TRANSACTION;

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

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

ROLLBACK;

SELECT * FROM Accounts;

✅ The rollback ensures atomicity—no partial updates happened.

Deadlock Simulation

A deadlock occurs when two transactions wait for each other indefinitely. Let’s simulate one using two sessions.

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

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

Log-Based Recovery

Most databases like MySQL use binary logs for recovery. Transactions are logged so that rollback operations can undo changes.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE name='Charlie';
ROLLBACK;
SELECT * FROM Accounts;

✅ The rollback restores, showing log-based recovery in action.

SUMMARY

Transactions ensure atomicity and consistency.
Deadlocks happen when two transactions wait on each other; the DBMS detects and resolves it.
Log-based recovery allows the database to undo changes after a rollback.