DBMS-Transaction, Deadlocks & Log-Based Recovery

STEP 1: Create table and Insert data

CREATE TABLE CustomerAccounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);

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

SELECT * FROM CustomerAccounts;

Step 2: Transaction – Atomicity & Rollback

START TRANSACTION;

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

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

SELECT * FROM Accounts;

ROLLBACK;

SELECT * FROM Accounts;

Step 3: Deadlock Simulation (Conceptual)

Session 1 (conceptual)

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

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

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

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

Expected in a real multi-session DB:

ORA-00060: deadlock detected while waiting for resource.

Step 4: Log-Based Recovery

UPDATE CustomerAccounts
SET balance = balance + 200;
WHERE name = 'Charlie';

ROLLBACK;

SELECT * FROM CustomerAccounts;