Transactions, Deadlocks & Log Based Recovery

In this tutorial, we’ll explore transactions, deadlocks, and log-based recovery using a simple Accounts table.

Let’s create a table

insert values

Transaction – Atomicity & Rollback

ROLLBACK

UPDATE Accounts

Deadlock Simulation

Deadlocks occur when two transactions block each other waiting for resources.

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

SELECT * FROM Accounts;

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.