1.Transaction, Atomicity, and Rollback
A transaction is a sequence of operations performed as a single logical unit of work.
Atomicity means either all operations complete successfully, or none do (if something fails or you rollback).
Rollback means to undo all operations in the transaction if it is not committed.
2.Deadlocks
- Deadlocks happen when two transactions each hold locks on resources the other needs, and neither can proceed, causing a standstill.
3.Log-Based Recovery
Databases keep logs of all transactions.
These logs help undo (rollback) or redo operations if needed, ensuring data consistency and recovery after crashes.
Step 1: Setup Your Environment with the Accounts Table
Create the table:
sql
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
Insert initial data:
sql
INSERT INTO Accounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);
initial table
Step 2: Transaction – Atomicity & Rollback
Start a transaction.
Transfer 500 from Alice (acc_no=1) to Bob (acc_no=2).
Before committing, rollback the transaction.
Check balances to confirm no partial update (Alice and Bob's balances unchanged).
BEGIN 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;
Step 3: Deadlock Simulation
You need two separate database sessions (tools like MySQL Workbench or psql can open multiple connections).
Session 1:
BEGIN;
SELECT * FROM Accounts WHERE acc_no = 1 FOR UPDATE; -- lock Alice's account
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 2; -- try to update Bob's account
-- wait or hold here before committing
BEGIN;
SELECT * FROM Accounts WHERE acc_no = 2 FOR UPDATE; -- lock Bob's account
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 1; -- try to update Alice's account
-- observe deadlock happens here or waits indefinitely
Deadlock occurs because each session waits for the other to release their lock.
Step 4: Log-Based Recovery
Ensure logging is enabled in your database (default in MySQL binary log or PostgreSQL WAL).
Start a transaction to update a record.
Rollback the transaction.
Check the log files or use database tools to confirm the undo was logged.
Top comments (0)