DEV Community

Ramya
Ramya

Posted on

SQL Essentials: Transactions, Deadlocks, and Recovery Explained

Databases are designed to remain consistent and reliable, even during complex operations. Mastering concepts like transactions, deadlocks, and log-based recovery is essential for effective database management.

In this tutorial, we’ll use a simple Accounts table to demonstrate these concepts.

Step 1: Create the Accounts Table

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

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

1️⃣ Transaction – Atomicity & Rollback

Atomicity ensures that a transaction is executed entirely or not at all.

Example: transferring money from Alice to Bob and rolling it back:

START TRANSACTION;

-- Transfer 500 from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After the rollback, balances remain unchanged, demonstrating no partial updates occur.


2️⃣ Deadlock Simulation

Deadlocks occur when two transactions wait indefinitely for each other’s resources.

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1; -- Locks Alice
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2; -- Waits for Bob

Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 2; -- Locks Bob
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 1; -- Waits for Alice

⏳ Both sessions wait on each other, causing a deadlock. Most DBMS automatically detect and abort one transaction to resolve it.


3️⃣ Log-Based Recovery

Databases maintain logs (binary log in MySQL, WAL in PostgreSQL) to recover from failures.

START TRANSACTION;

UPDATE Accounts SET balance = balance + 300 WHERE acc_no = 3;

-- Rollback instead of commit
ROLLBACK;

✅ The rollback is recorded in the log. If a crash occurs, the system can undo uncommitted changes and restore consistency.

Step 1: Create the Accounts Table

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

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000


1️⃣ Transaction – Atomicity & Rollback

Atomicity ensures that a transaction is executed entirely or not at all.

Example: transferring money from Alice to Bob and rolling it back:

START TRANSACTION;

-- Transfer 500 from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After the rollback, balances remain unchanged, demonstrating no partial updates occur.


2️⃣ Deadlock Simulation

Deadlocks occur when two transactions wait indefinitely for each other’s resources.

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1; -- Locks Alice
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2; -- Waits for Bob

Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 2; -- Locks Bob
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 1; -- Waits for Alice

⏳ Both sessions wait on each other, causing a deadlock. Most DBMS automatically detect and abort one transaction to resolve it.


3️⃣ Log-Based Recovery

Databases maintain logs (binary log in MySQL, WAL in PostgreSQL) to recover from failures.

START TRANSACTION;

UPDATE Accounts SET balance = balance + 300 WHERE acc_no = 3;

-- Rollback instead of commit
ROLLBACK;

✅ The rollback is recorded in the log. If a crash occurs, the system can undo uncommitted changes and restore consistency.

Top comments (0)