This content originally appeared on DEV Community and was authored by HARINI SRI K A 24CB016
Databases are at the heart of almost every application. To ensure reliability, developers need to understand transactions, deadlocks, and log-based recovery. In this blog, we’ll explore these concepts using a simple Accounts table.
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
) ENGINE=InnoDB;
INSERT INTO Accounts VALUES
(1, ‘Alice’, 1000),
(2, ‘Bob’, 1500),
(3, ‘Charlie’, 2000);
Transactions
— Start transaction
BEGIN;
— Deduct 500 from Alice
UPDATE Accounts
SET balance = balance – 500
WHERE name = ‘Alice’;
— Add 500 to Bob
UPDATE Accounts
SET balance = balance + 500
WHERE name = ‘Bob’;
— Rollback before commit
ROLLBACK;
SELECT * FROM Accounts;
*Deadlock Simulation
*
BEGIN;
— Lock Alice’s account
UPDATE Accounts SET balance = balance + 100 WHERE name=’Alice’;
— Try updating Bob’s account (will wait if Session 2 locks it)
UPDATE Accounts SET balance = balance – 100 WHERE name=’Bob’;
BEGIN;
— Lock Bob’s account
UPDATE Accounts SET balance = balance – 50 WHERE name=’Bob’;
— Try updating Alice’s account (will wait if Session 1 locks it)
UPDATE Accounts SET balance = balance + 50 WHERE name=’Alice’;
At this point, both sessions are waiting on each other → deadlock.
Log-Based Recovery
BEGIN;
UPDATE Accounts
SET balance = balance + 200
WHERE name=’Charlie’;
ROLLBACK;
SHOW BINLOG EVENTS;
Conclusion
Transactions provide atomicity, ensuring all-or-nothing updates.
Deadlocks can occur in concurrent sessions; modern DBMS detect and resolve them.
Logs enable rollback and recovery, keeping databases consistent and reliable.
THANK YOU @santhoshnc sir for guiding us!!!
This content originally appeared on DEV Community and was authored by HARINI SRI K A 24CB016