DBMS – Transactions, Deadlocks & Log-Based Recovery

Step 1: Create Table & Insert Data
CREATE TABLE CustomerAccounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);
INSERT INTO CustomerAccounts VALUES (1, ‘EMILY, 1000);
INSERT INTO CustomerAccounts VALUES (2, 'BOBBY, 1500);
INSERT INTO CustomerAccounts VALUES (3, 'CALEB, 2000);
SELECT * FROM CustomerAccounts;

Transaction – Atomicity & Rollback

UPDATE CustomerAccounts
SET balance = balance - 500
WHERE name = ‘Emily;

UPDATE CustomerAccounts
SET balance = balance + 500
WHERE name = 'Bobby’;

Rollback transaction

SELECT * FROM CustomerAccounts;

DEADLOCK SIMULATION

-- Lock Emily
UPDATE CustomerAccounts SET balance = balance - 100 WHERE name = ‘emily’;
-- Do NOT commit
Session 2 (conceptual)
-- Lock Bobby
UPDATE CustomerAccounts SET balance = balance - 200 WHERE name = 'Bobby’;
-- Do NOT commit
Continuing Session 1
-- Try updating Bobby (held by Session 2)
UPDATE CustomerAccounts SET balance = balance + 100 WHERE name = 'Bobby’;
Continuing Session 2
-- Try updating Emily (held by Session 1)
UPDATE CustomerAccounts SET balance = balance + 200 WHERE name = ‘Emily’;

LOG-BASED RECOVERY

Task: Show rollback and explain internal logs (undo/redo).
-- Update Charlie
UPDATE CustomerAccounts SET balance = balance + 300 WHERE name = 'Charlie';
-- Rollback
ROLLBACK;
-- Verify balances
SELECT * FROM CustomerAccounts;