Transactions, Deadlocks & Log Based Recovery

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.

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.