This content originally appeared on DEV Community and was authored by Hareesh
Managing database transactions safely is crucial to ensure data integrity and prevent issues like partial updates or deadlocks. In this tutorial, we’ll explore transactions, deadlocks, and log-based recovery using a simple Accounts table.
First, let’s create a sample 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);
*Transaction – Atomicity & Rollback
*
Suppose we want to transfer 500 from Alice to Bob. Using a transaction ensures that either both operations succeed or none.
START TRANSACTION;
UPDATE Accounts
SET balance = balance – 500
WHERE name = ‘Alice’;
ROLLBACK;
UPDATE Accounts
SET balance = balance + 500
WHERE name = ‘Bob’;
Deadlock Simulation
Deadlocks occur when two transactions block each other waiting for resources.
START TRANSACTION;
SELECT * FROM Accounts WHERE name=’Alice’ FOR UPDATE;
UPDATE Accounts SET balance = balance + 100 WHERE name=’Bob’;
START TRANSACTION;
SELECT * FROM Accounts WHERE name=’Bob’ FOR UPDATE;
UPDATE Accounts SET balance = balance – 100 WHERE name=’Alice’;RESULT
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Log-Based Recovery
Modern DBMS (MySQL/PostgreSQL) automatically maintain transaction logs. These logs help undo changes if a transaction fails.
UPDATE Accounts SET balance = balance – 300 WHERE name=’Alice’;Log-based recovery ensures that undo operations are possible. Even if the database crashes, the system can restore a consistent state.
Summary
Transactions guarantee atomicity; either all operations succeed or none.
Deadlocks occur when transactions block each other; they must be handled with care.
Log-based recovery ensures durability and recoverability
This content originally appeared on DEV Community and was authored by Hareesh