Transactions, Deadlocks & Log Based Recovery Schema

Introduction

Managing data integrity and concurrency is critical in any database system. In this blog, we are going to explore key database concepts ie transactions with atomicity and rollback, deadlock simulation, and log-based recovery

Schema used
Accounts table
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);

Transactions: Atomicity & Rollback
A transaction is a sequence of operations performed as a single logical unit of work. It ensures atomicity, meaning all operations succeed or none at all.

BEGIN;
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
ROLLBACK;

After applying rollback
The balances remain unchanged because the rollback undid the updates, demonstrating atomicity ie no partial updates happened.
SELECT * FROM Accounts;

Deadlock Simulation
A deadlock happens when two sessions wait for each other’s locked resources indefinitely.
Open two separate sessions (e.g., two terminal windows connected to the database).
Session 1
BEGIN;
UPDATE Accounts SET balance = balance + 0 WHERE name = 'Alice';

--(will wait if locked by Session 2)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

Session 2
BEGIN;
UPDATE Accounts SET balance = balance + 0 WHERE name = 'Bob';

-- (will wait if locked by Session 1)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

Both sessions wait for each other indefinitely ie a deadlock

Log-Based Recovery

[mysqld]
log_bin = mysql-bin
BEGIN;

UPDATE Accounts SET balance = balance - 300 WHERE name = 'Charlie';

ROLLBACK;

The update and rollback operations are recorded in the log.

During recovery, the database uses the log to undo changes from uncommitted transactions ensuring data consistency.

Conclusion

Understanding these concepts helps build reliable, concurrent database applications that maintain data integrity even under failure or heavy multi-user access.I extend my gratitude to @santhoshnc for the guidance through deadlocks.