Exploring SQL Transactions, Deadlocks & Log-Based Recovery

Databases are designed to be reliable and consistent even in complex operations. Understanding transactions, deadlocks, and log-based recovery is key to mastering database management.

In this blog, we’ll explore these concepts using a simple Accounts table.

---

🧱 Step 1: Create the 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);

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

---

1️⃣ Transaction – Atomicity & Rollback

Atomicity ensures that a transaction is treated as a single unit — either fully executed or not executed at all.

Let’s simulate a money transfer from Alice to Bob and roll it back before committing.

START TRANSACTION;

-- Transfer 500 from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After rollback, balances remain unchanged, confirming no partial update occurs.

---

2️⃣ Deadlock Simulation

Deadlocks happen when two transactions wait indefinitely for each other’s locks.

Simulate using two sessions:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1; -- Locks Alice
-- Try to update Bob
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2;

Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 2; -- Locks Bob
-- Try to update Alice
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 1;

⏳ Both sessions wait on each other — a deadlock occurs.
Most DBMS detect this and abort one transaction automatically.

---

3️⃣ Log-Based Recovery

Databases maintain logs (binary log in MySQL, WAL in PostgreSQL) to recover from crashes.

Example:

START TRANSACTION;

UPDATE Accounts SET balance = balance + 300 WHERE acc_no = 3;

-- Rollback instead of commit
ROLLBACK;

✅ The rollback is recorded in the log. If the database crashes, the system can undo uncommitted changes and restore consistency.

💡 Final Thoughts

Understanding transactions, deadlocks, and recovery mechanisms is crucial for building robust and reliable database applications.

Experimenting with these SQL operations gives hands-on insight into how real-world DBMS maintain data integrity and availability.

Thank you @santhoshnc sir for guiding me..

SQL #Database #Transactions #Deadlocks #Recovery #DevCommunity #LearningByDoing