This content originally appeared on DEV Community and was authored by Harshitha S
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';
SELECT * FROM Accounts;
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 Harshitha S
Harshitha S | Sciencx (2025-10-03T10:37:30+00:00) Transactions, Deadlocks & Log Based Recovery. Retrieved from https://www.scien.cx/2025/10/03/transactions-deadlocks-log-based-recovery/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.