Transactions, Deadlocks & Log-Based Recovery

Transactions, Deadlocks & Log-Based Recovery in SQL

In this tutorial, we’ll explore transactions, deadlocks, and log-based recovery in SQL using a simple BankAccounts table. These are key concepts in database management for ensuring data consistency, integrity, and recoverability.

Schema Setup
CREATE DATABASE BankDB;

USE BankDB;

CREATE TABLE BankAccounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

INSERT INTO BankAccounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

SELECT * FROM BankAccounts;

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

Transaction – Atomicity & Rollback
BEGIN;

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

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

ROLLBACK;

SELECT * FROM BankAccounts;

Expected Result:
Balances remain unchanged:

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

Deadlock Simulation

Deadlocks occur when two transactions wait for each other’s locks indefinitely. No SELECT queries are needed; updates are enough.

Session 1:

BEGIN;
UPDATE BankAccounts SET balance = balance + 100 WHERE name='Alice';

Session 2:

BEGIN;
UPDATE BankAccounts SET balance = balance + 200 WHERE name='Bob';

Then each session tries to update the other’s row:

Session 1:

UPDATE BankAccounts SET balance = balance - 50 WHERE name='Bob';

Session 2:

UPDATE BankAccounts SET balance = balance - 50 WHERE name='Alice';

At this point, both sessions wait for the other’s lock → deadlock occurs, and the database will abort one transaction.

Tip: Always acquire locks in the same order to prevent deadlocks.

Log-Based Recovery

Databases maintain transaction logs to recover from failures. Even if a transaction is rolled back, the log records the operations and their undo.

BEGIN;
UPDATE BankAccounts SET balance = balance + 500 WHERE name='Charlie';
ROLLBACK;

SELECT * FROM BankAccounts;

Result:

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

The rollback undid the changes. Internally, the database logs contain both the attempted update and its undo operation.

Conclusion

Atomicity: Ensures transactions are all-or-nothing.

Deadlocks: Can occur in concurrent transactions; careful lock ordering prevents them.

Log-Based Recovery: Guarantees durability and consistency even after rollbacks or crashes.