Transactions, Deadlocks & Log Based Recovery

Managing database transactions safely is crucial to ensure data integrity and prevent issues like partial updates or deadlocks. In this tutorial, we’ll explore transactions, deadlocks, and log-based recovery using a simple Accounts table.

First, let’s create a sample 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);

*Transaction – Atomicity & Rollback
*
Suppose we want to transfer 500 from Alice to Bob. Using a transaction ensures that either both operations succeed or none.

START TRANSACTION;

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

ROLLBACK;

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

Deadlock Simulation

Deadlocks occur when two transactions block each other waiting for resources.

START TRANSACTION;
SELECT * FROM Accounts WHERE name='Alice' FOR UPDATE;
UPDATE Accounts SET balance = balance + 100 WHERE name='Bob';

START TRANSACTION;
SELECT * FROM Accounts WHERE name='Bob' FOR UPDATE;
UPDATE Accounts SET balance = balance - 100 WHERE name='Alice';

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

Log-Based Recovery

Modern DBMS (MySQL/PostgreSQL) automatically maintain transaction logs. These logs help undo changes if a transaction fails.

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

SELECT * FROM Accounts;

Log-based recovery ensures that undo operations are possible. Even if the database crashes, the system can restore a consistent state.

Summary

Transactions guarantee atomicity; either all operations succeed or none.

Deadlocks occur when transactions block each other; they must be handled with care.

Log-based recovery ensures durability and recoverability.