This content originally appeared on DEV Community and was authored by Lohita M
We will explore key DBMS concepts: Transactions, Deadlocks, and Log-Based Recovery using an Accounts table in Oracle SQL.
1️⃣ Schema Setup
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
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
UPDATE Accounts SET balance = 2500 WHERE name = 'Charlie';
ROLLBACK;
SELECT * FROM Accounts;
Explanation
-Oracle writes all changes to redo logs.
-When we ROLLBACK, Oracle uses the logs to undo changes.
-This ensures the database stays consistent even if a failure occurs.
This content originally appeared on DEV Community and was authored by Lohita M