Transactions, Deadlocks & Log Based Recovery in SQL

#acid #deadlock #logging #sql

Intro

In this blog, let’s explore three very important database concepts: Transactions, Deadlocks, and Log-Based Recovery. We’ll be using a simple Accounts table and running SQL queries to demonstrate these in action.

Schema Setup

First, let’s create a table Accounts and insert some sample data.

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

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

Transaction – Atomicity & Rollback

Transactions are all-or-nothing operations. Either all changes are applied (commit) or none (rollback).

Let’s try transferring 500 from Alice to Bob but roll it back before committing.

-- Start transaction
START TRANSACTION;

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

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

-- Check balances before rollback
SELECT * FROM Accounts;

-- Rollback transaction
ROLLBACK;

-- Check balances after rollback
SELECT * FROM Accounts;

Result: Alice still has 1000, Bob has 1500.
No partial update happened – this is Atomicity.

Deadlock Simulation

Deadlocks happen when two transactions are waiting for each other’s resources.

We’ll simulate this using two sessions:

Session 1

START TRANSACTION;
-- Lock Bob
UPDATE Accounts SET balance = balance - 50 WHERE name = 'Bob';

-- Now try updating Alice (but Alice is locked by Session 1)
UPDATE Accounts SET balance = balance + 50 WHERE name = 'Alice';

At this point, both sessions are waiting for each other → Deadlock occurs.
The database will detect this automatically and kill one transaction, rolling it back.

Log-Based Recovery

Databases maintain logs (like binary log in MySQL or WAL in PostgreSQL) to ensure durability and recovery.

Let’s test this:

-- Start transaction
START TRANSACTION;

-- Update a record
UPDATE Accounts SET balance = balance + 200 WHERE name = 'Charlie';

-- Rollback
ROLLBACK;

Even though the update was rolled back, the log will record both the update and the undo operation.
This ensures that if the system crashes, recovery can undo uncommitted transactions and redo committed ones.

Key Takeaways