💾 Transactions, Deadlocks & Log-Based Recovery in Oracle SQL

#dbms #programming #webdev

We will explore key DBMS concepts: Transactions, Deadlocks, and Log-Based Recovery using an Accounts table in Oracle SQL.

1️⃣ Schema Setup

CODE:

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Accounts';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/

CREATE TABLE Accounts (
acc_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
balance NUMBER
);

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

COMMIT;

Explanation:

NUMBER is used for numeric columns in Oracle.
VARCHAR2 is used for text columns.
COMMIT finalizes the inserts so other sessions can see the data.
After this, the Accounts table has three accounts with initial balances.

2️⃣ Transaction – Atomicity & Rollback

CODE:

UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
ROLLBACK;
SELECT * FROM Accounts;

Explanation

Oracle automatically starts a transaction with any DML statement.
Atomicity ensures that either all updates succeed or none do.
ROLLBACK cancels changes.
After rollback, balances of Alice and Bob remain unchanged, ensuring data consistency.

3️⃣ Deadlock Simulation

Session 1:

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

Session 2:

UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

Explanation

Session 1 locks Alice’s account.
Session 2 locks Bob’s account.
When each session tries to update the other’s locked row, Oracle detects a deadlock.
Oracle automatically rolls back one transaction to resolve the deadlock.

4️⃣ Log-Based Recovery

CODE:

UPDATE Accounts SET balance = 2500 WHERE name = 'Charlie';
ROLLBACK;
SELECT * FROM Accounts;