In this tutorial, we’ll explore how to work with transactions, simulate deadlocks, and understand log-based recovery using SQL (MySQL/PostgreSQL).
We’ll use a simple 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);
Screenshot:
1️⃣Transaction – Atomicity & Rollback
Goal: Ensure all-or-nothing transactions.
-Start a transaction to transfer 500 from Alice to Bob.
-Rollback before committing.
-- Start transaction
START TRANSACTION;
-- Deduct 500 from Alice
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
-- Add 500 to Bob
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
-- Rollback the transaction
ROLLBACK;
-- Check balances
SELECT * FROM Accounts;
Expected Output (balances unchanged):
+--------+---------+---------+
| acc_no | name | balance |
+--------+---------+---------+
| 1 | Alice | 1000 |
| 2 | Bob | 1500 |
| 3 | Charlie | 2000 |
+--------+---------+---------+
The rollback ensures no partial update occurs.
screenshot:
2️⃣ Deadlock Simulation
Goal: Observe deadlock when two sessions try to access the same resources in conflicting order.
Step 1: Open two sessions
Session 1:
START TRANSACTION;
-- Lock Alice’s account
SELECT * FROM Accounts WHERE acc_no = 1 FOR UPDATE;
-- Try to update Bob (this will wait if Session 2 locks it)
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 2;
Session 2:
START TRANSACTION;
-- Lock Bob’s account
SELECT * FROM Accounts WHERE acc_no = 2 FOR UPDATE;
-- Try to update Alice (this will wait if Session 1 locks it)
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 1;
Expected Outcome:
Both sessions are waiting for each other → deadlock detected.
MySQL/PostgreSQL will automatically roll back one transaction to resolve the deadlock.
Screenshot:
3️⃣ Log-Based Recovery
Goal: Verify undo operations are recorded in logs for crash recovery.
Ensure logging is enabled (MySQL: Binary log; PostgreSQL: WAL).
-- Start transaction
START TRANSACTION;
-- Update a record
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
-- Rollback
ROLLBACK;
Check the log:
MySQL: SHOW BINLOG EVENTS; or inspect binary log.
PostgreSQL: WAL automatically records undo info.
Expected Result:
The update is rolled back, and the log contains the undo operation.
Screenshot:
✅ Full SQL Script
You can save the following as transactions_deadlocks.sql and run in MySQL CLI:
CREATE DATABASE IF NOT EXISTS TransactionsDemo;
USE TransactionsDemo;
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);
-- Transaction and rollback demo
START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
ROLLBACK;
SELECT * FROM Accounts;
-- Deadlock simulation: run in two sessions using SELECT ... FOR UPDATE
-- Session 1 locks Alice, Session 2 locks Bob
-- Attempt conflicting updates to observe deadlock
-- Log-based recovery demo
START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
ROLLBACK;
Troubleshooting Tips
1.Rollback not working? Make sure you start the transaction with START TRANSACTION;.
2.Deadlock not detected? Ensure both sessions are using SELECT ... FOR UPDATE or update statements in conflicting order.
3.Logs not visible? Check that binary logging is enabled in MySQL (SHOW VARIABLES LIKE 'log_bin';).
Top comments (0)