Transactions, Deadlocks & Log Based Recovery

Understanding Transactions, Deadlocks, and Log-Based Recovery in SQL
Working with databases involves more than just storing and retrieving data. To ensure your applications are reliable, consistent, and recoverable, you need to understand transactions, deadlocks, and log-based recovery. Let’s explore these concepts step by step using a simple Accounts table.

Setting Up the Database

We’ll start with a simple table to simulate bank accounts

Transaction – Atomicity & Rollback

Imagine transferring 500 from Alice to Bob. Using a transaction ensures that either both changes happen, or none happen

START TRANSACTION;

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

ROLLBACK;

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

Simulating Deadlocks

Deadlocks happen when two transactions block each other. Open two SQL sessions:
Session 1
START TRANSACTION;
SELECT * FROM Accounts WHERE name='Alice' FOR UPDATE;
UPDATE Accounts SET balance = balance + 100 WHERE name='Bob';

Session 2
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
Deadlocks occur when two transactions wait for each other indefinitely. Always design transactions to acquire locks in the same order to prevent them.

Log-Based Recovery

Most modern databases like MySQL (binary logs) or PostgreSQL (WAL) automatically log all transactions. This allows you to undo operations if needed.

Key Takeaways

• Transactions ensure that operations are atomic and consistent.

• Deadlocks can happen when multiple transactions compete for resources; detect and prevent carefully.

• Log-based recovery guarantees that your database can undo or redo transactions for durability and consistency.