Transactions, Deadlocks & Log-Based Recovery in SQL

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!!!