Transactions, Deadlocks & Log Based Recovery

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

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

SELECT * FROM Accounts;

Step-1: Atomicity & Rollback

Step-2: Deadlock Simulation
Session 1
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
Session 2
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

One of the sessions will show:

ORA-00060: deadlock detected while waiting for resource
Step-3: Log-Based Recovery