Transactions, Deadlocks & Log Based Recovery

1. Create the Table

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

2. Insert Initial Data

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

Transaction

Start the Transaction (implicitly starts with the first DML):

• No explicit START TRANSACTION needed; the transaction begins with the first UPDATE.

Debit from Alice

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

credit to Bob

UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';

Rollback the Transaction:

ROLLBACK;

Check Balances to Confirm No Changes

SELECT * FROM Accounts;

OUTPUT

Deadlock Simulation

• Oracle supports row-level locking, which we can use to simulate a deadlock. Open two SQL*Plus sessions. Session 1 (Window 1):

Lock Alice's account:

UPDATE Accounts SET balance = balance WHERE name = 'Alice';
-- Don't commit yet; leave it locked

Try to update Bob's account (run this after starting Session 2):

UPDATE Accounts SET balance = balance WHERE name = 'Bob';

Session 2 (Window 2):

Lock Bob's account:

UPDATE Accounts SET balance = balance WHERE name = 'Bob';
-- Don't commit yet; leave it locked

Try to update Alice's account:

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

• At this point, Session 1 waits for Session 2's lock on Bob, and Session 2 waits for Session 1's lock on Alice, causing a deadlock. Oracle will detect this and roll back one transaction, raising an error like:

Log-Based Recovery

Step 1: Enable Logging

• Logging is enabled by default in Oracle via redo logs and undo tablespaces. You don’t need to enable it manually unless it’s been disabled (unlikely in a standard setup). To confirm an undo tablespace exists (without DBA_TABLESPACES), try:

SELECT tablespace_name FROM USER_TABLESPACES WHERE tablespace_name LIKE '%UNDO%';

Step 2: Start a Transaction and Update a Record

UPDATE Accounts SET balance = balance + 100 WHERE name = 'Charlie';
-- Transaction starts implicitly

Step 3: Rollback the Transaction

ROLLBACK;

Step 4: Check the Log to Confirm Undo Operation

SELECT * FROM Accounts WHERE name = 'Charlie';