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)