Solving a DBMS Assignment on Oracle Live SQL: Transactions, Deadlocks & Log-Based Recovery

For a recent database management systems (DBMS) assignment, I had to demonstrate key concepts like atomicity, deadlocks, and log-based recovery using Oracle Live SQL. The assignment used a simple Accounts table to illustrate these principles. Here’s a breakdown of the tasks and how I solved them, along with the SQL queries I used.

1. The Setup: A Simple Accounts Table First, I created the Accounts table and populated it with some initial data. This is the starting point for all the tasks.

Key Point: The CREATE TABLE and INSERT queries set up the initial schema and data. COMMIT makes the initial state permanent.

SQL

-- Create the Accounts table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

-- Insert the initial data
INSERT INTO Accounts VALUES (1, 'Alice', 1000);
INSERT INTO Accounts VALUES (2, 'Bob', 1500);
INSERT INTO Accounts VALUES (3, 'Charlie', 2000);

-- Commit the initial data to make it permanent
COMMIT;

-- View the initial state
SELECT * FROM Accounts;

1. Transaction – Atomicity & Rollback The goal of this task was to demonstrate atomicity, the "all or nothing" property of a transaction. I needed to perform a money transfer and then use ROLLBACK to undo it completely, proving that the transfer never partially completed.

Key Point: A transaction starts implicitly with the first UPDATE. ROLLBACK reverts both UPDATE statements simultaneously, showcasing atomicity.

Queries:

SQL

-- Perform a transfer from Alice to Bob
UPDATE Accounts
SET balance = balance - 500
WHERE acc_no = 1;
UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 2;

-- Check the balances. They are updated, but not committed yet.
SELECT * FROM Accounts;

-- Rollback the transaction
ROLLBACK;

-- Check the balances again. They are back to the original values.
SELECT * FROM Accounts;

1. Log-Based Recovery This task aimed to show that a ROLLBACK operation relies on the database’s logging system. In Oracle, this is handled by the undo tablespace, which stores the "before" image of the data. When I issue a ROLLBACK, the system uses this information to restore the original state.

Key Point: The database automatically uses the undo tablespace for recovery. ROLLBACK relies on this logged information to revert changes.

Queries:

SQL

-- Update Charlie's balance (uncommitted)
UPDATE Accounts
SET balance = 2500
WHERE acc_no = 3;

-- Check the updated balance
SELECT * FROM Accounts;

-- Rollback the change
ROLLBACK;

-- Check the balance again. It has been reverted.
SELECT * FROM Accounts;

1. Deadlock Simulation This was the most challenging part of the assignment because it cannot be performed with a single script in Oracle Live SQL. A deadlock occurs when two or more transactions are waiting for a resource locked by another. This requires at least two separate sessions (two distinct connections to the database).

Key Point: Deadlocks can't be simulated in a single-session environment like Oracle Live SQL. They require two separate sessions to create a circular dependency of locked resources. The DBMS detects this and automatically terminates one transaction.

How it works conceptually:

In Session 1, I would lock Alice’s account.

In Session 2, I would lock Bob’s account.

Session 1 would then try to update Bob’s account, and would wait.

Session 2 would then try to update Alice’s account, causing a circular wait and a deadlock.

This assignment was an excellent way to learn about the fundamental principles of transaction management that are critical for any database professional. I hope this helps anyone else working on a similar assignment!