Transactions, Deadlocks & Log Based Recovery

Avatar photoByAdil

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';

Similar Posts