DBMS – Transactions, Deadlocks & Log-Based Recovery

In DBMS – Transactions, Deadlocks & Log-Based Recovery, you’ll study how transactions ensure data consistency, how deadlocks occur when multiple transactions block each other, and how log-based recovery helps the system restore data after crashes or failures.

Step 1: Create Table and Insert Data

CREATE TABLE Bank_Account (
Acc_No INT PRIMARY KEY,
Acc_Name VARCHAR(50),
Balance DECIMAL(10,2)
);

INSERT INTO Bank_Account (Acc_No, Acc_Name, Balance) VALUES
(101, 'Alice', 5000.00),
(102, 'Bob', 3000.00),
(103, 'Charlie', 7000.00),
(104, 'Diana', 4500.00);

Step 2: Transaction – Atomicity & Rollback

• Task: Start a transaction to transfer ₹1000 from Alice (Acc_No 101) to Bob (Acc_No 102).

• Deduct ₹1000 from Alice’s account.

Deduct 1000 from Alice

UPDATE Bank_Account
SET Balance = Balance - 1000
WHERE Acc_No = 101;

Add 1000 to Bob

UPDATE Bank_Account
SET Balance = Balance + 1000
WHERE Acc_No = 102;

RollBack Trtansaction:

ROLLBACK;

Deadlock Simulation:

Session 1

Lock Alice's row (Acc_No = 101)

UPDATE Bank_Account
SET Balance = Balance - 500
WHERE Acc_No = 101;

Now try to update Bob (but Bob is already locked by Session 2)

UPDATE Bank_Account
SET Balance = Balance + 500
WHERE Acc_No = 102;

Session 2

Lock Bob's row (Acc_No = 102)

UPDATE Bank_Account
SET Balance = Balance - 300
WHERE Acc_No = 102;

Now try to update Alice (but Alice is already locked by Session 1)

UPDATE Bank_Account
SET Balance = Balance + 300
WHERE Acc_No = 101;

Log-Based Recovery

Start the transaction
START TRANSACTION;

Deduct from Alice
UPDATE Bank_Account
SET Balance = Balance - 6000
WHERE Acc_No = 101;

Add to Bob
UPDATE Bank_Account
SET Balance = Balance + 6000
WHERE Acc_No = 102;

Commit if success
COMMIT;

Rollback if failure
ROLLBACK;

Conclusion:

Transactions: ensure the ACID properties (Atomicity, Consistency, Isolation, Durability).

Atomicity & Rollback: guarantee that either all operations of a transaction happen, or none happen.

Deadlocks: occur when two or more transactions wait for each other’s locked resources → DBMS resolves it by aborting one transaction.

Special thanks to @santhoshnc Sir for guidance throughout this assignment.