Transactions, Deadlocks & Log-Based Recovery

Transactions (Atomicity & Rollback)

Deadlock Simulation

Log-Based Recovery

Schema Setup
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);

Initial state of table:

acc_no name balance
1 Alice 1000
2 Bob 1500
3 Charlie 2000
Transaction – Atomicity & Rollback

We want to transfer 500 from Alice → Bob inside a transaction but rollback before commit.

-- Start transaction
START TRANSACTION;

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

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

-- Rollback instead of commit
ROLLBACK;

Verification
SELECT * FROM Accounts;

Balances remain unchanged (no partial update):

acc_no name balance
1 Alice 1000
2 Bob 1500
3 Charlie 2000

This proves Atomicity: either all updates happen, or none.

Deadlock Simulation

We’ll open two SQL sessions to simulate a deadlock.

Session 1
START TRANSACTION;
-- Lock Alice’s account
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';

-- Now try to update Bob (this will wait if Session 2 has Bob locked)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

Session 2
START TRANSACTION;
-- Lock Bob’s account
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Bob';

-- Now try to update Alice (this will wait if Session 1 has Alice locked)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

At this point:

Session 1 is waiting for Bob (locked by Session 2).

Session 2 is waiting for Alice (locked by Session 1).

This is a deadlock.

Most databases (Oracle, MySQL, PostgreSQL, SQL Server) will detect the deadlock and automatically kill one transaction, rolling it back.

You’ll see an error like:

ERROR 1213 (40001): Deadlock found when trying to get lock

Log-Based Recovery

Databases maintain logs (MySQL = Binary Log, PostgreSQL = WAL, Oracle = Redo Logs). These logs allow recovery during crashes or rollbacks.

Example – Transaction with rollback
START TRANSACTION;

UPDATE Accounts
SET balance = balance + 200
WHERE name = 'Charlie';

ROLLBACK;

The update is written to the log.

The rollback adds a corresponding undo entry.

If the system crashes, the recovery process replays the log and ensures Charlie’s balance remains unchanged.