Transactions, Deadlocks & Log Based Recovery

database transactions are more than just BEGIN and COMMIT. They’re the backbone of data integrity, especially when things go wrong. In this post, we’ll explore three powerful concepts:

Atomicity & Rollback
Deadlock Simulation
Log-Based Recovery

1. Atomicity & Rollback: All or Nothing Imagine Alice wants to transfer ₹500 to Bob. We start a transaction, update both accounts, and then rollback before committing.

SQL Example
sql

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

sql
SELECT name, balance FROM Accounts WHERE name IN ('Alice', 'Bob');
Result: No changes. That’s Atomicity—either all operations succeed or none do.

1. Deadlock Simulation: When Transactions Collide Deadlocks happen when two sessions wait on each other indefinitely.

Session 1
sql
BEGIN;
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Bob';
Session 2
sql
BEGIN;
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';

1. Log-Based Recovery: Most modern databases (MySQL, PostgreSQL, Oracle) use transaction logs to track changes.

Step-by-Step
sql
BEGIn;
UPDATE Accounts SET balance = balance + 1000 WHERE name = 'Alice';
ROLLBACK;
Final Thoughts
Transactions are the heartbeat of reliable databases. Whether you're building fintech apps or inventory systems, understanding how atomicity, deadlocks, and recovery work will make you a better engineer.

Thank you @santhoshnc sir