Understanding Transactions, Deadlocks & Log-Based Recovery in SQL

Database reliability is critical for any application. In this tutorial, we’ll explore Transactions, Deadlocks, and Log-Based Recovery using a simple Accounts table.

1. Setup: Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);

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

COMMIT;

This table will be used to simulate transactions, deadlocks, and recovery.

2. Transaction – Atomicity & Rollback

Goal: Transfer 500 from Alice to Bob, then rollback to demonstrate atomicity.

-- Start transaction
BEGIN
-- Deduct 500 from Alice
UPDATE Accounts
SET balance = balance - 500
WHERE acc_no = 1;

-- Add 500 to Bob
UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 2;

-- Simulate a rollback
ROLLBACK;

END;
/

✅ Check balances:

SELECT * FROM Accounts;

Result: Balances remain unchanged. No partial update occurred — atomicity is preserved.

3. Deadlock Simulation

Goal: Demonstrate a deadlock scenario with two sessions.

Session 1:

-- Lock Alice’s account
UPDATE Accounts
SET balance = balance + 100
WHERE acc_no = 1;

-- Pause before committing (wait)

Session 2 (simultaneously):

-- Lock Bob’s account
UPDATE Accounts
SET balance = balance - 50
WHERE acc_no = 2;

-- Now try to update Alice’s account
UPDATE Accounts
SET balance = balance - 100
WHERE acc_no = 1;

Both sessions are waiting for the other to release locks → deadlock occurs.

Most DBMS detect deadlocks and abort one transaction to resolve it.

Key takeaway: Always acquire locks in the same order to avoid deadlocks in multi-session environments.

4. Log-Based Recovery

Goal: Ensure database can undo changes after rollback using logs.

In Oracle, all transactions are automatically logged in the Redo Log.

In MySQL/PostgreSQL, binary logs / WAL (Write-Ahead Log) track all changes.

Example:

BEGIN
UPDATE Accounts
SET balance = balance + 200
WHERE acc_no = 3;

ROLLBACK; -- Undo the update

END;
/

Check logs:

• Oracle: V$LOGMNR_CONTENTS or redo logs capture the undo operation.
• MySQL: Binary log will record both the original update and rollback action .

Result: Log ensures database can recover from failures without losing consistency.

5. Summary
Concept Explanation

• Transaction (Atomicity) Either all operations succeed or none; rollback prevents partial updates.
• Deadlock Two sessions waiting for each other’s locks; resolved by DBMS aborting one transaction.
• Log-Based Recovery Logs record changes so rollback and crash recovery are possible.

Conclusion

By understanding transactions, deadlocks, and log-based recovery, you can ensure your database remains reliable, consistent, and recoverable. Always test transactions carefully, handle deadlocks gracefully, and leverage logging for safety.

💡 Tip: Simulate deadlocks in a controlled environment to learn how your DBMS resolves them. This is essential for multi-user applications.