Transactions, Deadlocks & Log Based Recovery

Schema

Use a single table Accounts:

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

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

Transaction – Atomicity & Rollback

• Start a transaction to transfer 500 from Alice to Bob.
• Rollback the transaction before committing.
• Check balances to ensure no partial update happened.

BEGIN
  -- Start transaction
  UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1; -- Alice
  UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2; -- Bob

  -- Rollback before committing
  ROLLBACK;

  -- Check balances
  DECLARE
    v_alice INT;
    v_bob INT;
  BEGIN
    SELECT balance INTO v_alice FROM Accounts WHERE acc_no = 1;
    SELECT balance INTO v_bob FROM Accounts WHERE acc_no = 2;

    DBMS_OUTPUT.PUT_LINE('Alice balance: ' || v_alice);
    DBMS_OUTPUT.PUT_LINE('Bob balance: ' || v_bob);
  END;
END;

Deadlock Simulation (using two browser tabs in Live SQL)

• Open two sessions.
• Session 1: Lock Alice’s account and try to update Bob’s.
• Session 2: Lock Bob’s account and try to update Alice’s.
• Observe deadlock.

-- SESSION 1
-- Lock Alice
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1;

-- updating Bob (will wait if Bob is locked)
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2;

-- SESSION 2
-- Lock Bob
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 2;

-- updating Alice (will wait if Alice is locked)
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 1;

-- Checking Output
DECLARE
  v_alice INT;
  v_bob INT;
BEGIN
  SELECT balance INTO v_alice FROM Accounts WHERE acc_no = 1;
  SELECT balance INTO v_bob FROM Accounts WHERE acc_no = 2;

  DBMS_OUTPUT.PUT_LINE('Alice balance: ' || v_alice);
  DBMS_OUTPUT.PUT_LINE('Bob balance: ' || v_bob);
END;

Log-Based Recovery

• Enable logging (if using MySQL/PostgreSQL, binary log/WAL is default).
• Start a transaction to update a record and rollback.
• Check the log to confirm undo operation was recorded.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 3;
ROLLBACK;