DBMS – Transactions, Deadlocks & Log-Based Recovery

Introduction to Transactions, Deadlocks & Log-Based Recovery in Databases

Databases handle lots of operations all at once, and keeping everything running smoothly is no easy task. That’s why concepts like transactions, deadlocks, and log-based recovery are so important in database management systems (DBMS).

A transaction is a group of actions that happen together as a single unit, ensuring data stays accurate and consistent.

Sometimes, transactions can get stuck waiting for each other, causing a deadlock think of it like two people blocking each other in a hallway.

To keep data safe even if things go wrong, log-based recovery keeps a detailed record of changes so the database can bounce back quickly after crashes or errors.

Understanding these concepts helps make sure your data stays reliable, even when multiple users and complex operations are happening at the same time.

Step 1: Create Table & Insert Data
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);

SELECT * FROM Accounts;

Transaction – Atomicity & Rollback

Task: Transfer 500 from Alice to Bob, but rollback before committing.

Deduct 500 from Alice

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

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

Rollback transaction

ROLLBACK;

Check balances

SELECT * FROM Accounts;

Deadlock Simulation (Conceptual)

Session 1 (conceptual)

Lock Alice
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
Do NOT commit

Session 2 (conceptual)
Lock Bob
UPDATE Accounts SET balance = balance - 200 WHERE name = 'Bob';
Do NOT commit

Continuing Session 1
Try updating Bob (held by Session 2)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

Continuing Session 2
Try updating Alice (held by Session 1)
UPDATE Accounts SET balance = balance + 200 WHERE name = 'Alice';

Log-Based Recovery

Task: Show rollback and explain internal logs (undo/redo).

Update Charlie
UPDATE Accounts SET balance = balance + 300 WHERE name = 'Charlie';

Rollback
ROLLBACK;

Verify balances
SELECT * FROM Accounts;

conclusion
Transactions, deadlocks, and log-based recovery are the backbone of reliable and efficient database management. They work together to keep your data accurate, prevent system freezes, and recover smoothly from unexpected problems. By understanding these key concepts, you can appreciate how databases handle complex operations behind the scenes — ensuring your information is always safe and accessible.