DEV Community

Cover image for DBMS – Transactions, Deadlocks & Log-Based Recovery
Jaswant Karun
Jaswant Karun

Posted on

DBMS – Transactions, Deadlocks & Log-Based Recovery

We will explore Transactions, Deadlocks, and Log-Based Recovery using SQL.
We use the Accounts table as our base schema and demonstrate ACID concepts with rollback, simulate a deadlock, and discuss 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, 'Alice', 1000);
INSERT INTO CustomerAccounts VALUES (2, 'Bob', 1500);
INSERT INTO CustomerAccounts VALUES (3, 'Charlie', 2000);

SELECT * FROM CustomerAccounts;

Transaction – Atomicity & Rollback

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

Deduct 500 from Alice

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

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

Rollback transaction

ROLLBACK;

Check balances

SELECT * FROM CustomerAccounts;

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

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;

Conclusion:-

Atomicity: Rollback prevents partial updates.

Deadlock: Occurs when two sessions wait for each other (conceptual in LiveSQL).

Log-Based Recovery: Oracle maintains undo/redo logs for safe recovery.

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

dbms #oracle #sql #transactions #deadlock #recovery #assignment

Top comments (0)