This content originally appeared on DEV Community and was authored by Ramya
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.
This content originally appeared on DEV Community and was authored by Ramya
Ramya | Sciencx (2025-10-08T17:14:59+00:00) SQL Essentials: Transactions, Deadlocks, and Recovery Explained. Retrieved from https://www.scien.cx/2025/10/08/sql-essentials-transactions-deadlocks-and-recovery-explained/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.