Deadlocks & Log-Based Recovery in DBMS

⚙️ Transactions, Deadlocks & Log-Based Recovery in DBMS

In a Database Management System (DBMS), transactions are the building blocks of reliable data operations. But things can go wrong — transactions can fail, multiple users can lock the same data, and system crashes can cause data loss.

To handle all of this, DBMS uses Transactions, deals with Deadlocks, and ensures Recovery using logs.

Let’s explore each concept 👇

💡 What is a Transaction?

A transaction is a sequence of one or more SQL operations performed as a single logical unit of work.

✅ Key Properties (ACID)

Every transaction should satisfy:

A — Atomicity: all or nothing

C — Consistency: database moves from one valid state to another

I — Isolation: concurrent transactions don’t affect each other

D — Durability: once committed, data persists

Example:

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE id = 1;
UPDATE Accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

If any step fails, the system rolls back the changes.

🧩 Transaction States

A transaction typically passes through these states:

State Description
Active Transaction is executing.
Partially Committed All statements executed, waiting for commit.
Committed Changes are permanently saved.
Failed An error occurred, can’t proceed.
Aborted Rolled back to previous consistent state.
🔒 Deadlocks

When two or more transactions wait on each other indefinitely, a deadlock occurs.

🧠 Example:

T1 locks A and waits for B

T2 locks B and waits for A

Neither can proceed — the system is stuck.

T1 → A (locked), waiting for B

T2 → B (locked), waiting for A

🧰 Deadlock Handling

There are three main strategies:

Deadlock Prevention – design transactions to avoid circular waits (e.g. lock ordering).

Deadlock Detection – DBMS checks for cycles in the wait-for graph and aborts one transaction.

Deadlock Recovery – after detection, one transaction is rolled back to break the cycle.

🧾 Log-Based Recovery

To recover from crashes or failures, DBMS maintains a log file (a sequential record of all transactions and actions).

🔹 Types of Log Records

[Start, T] → Transaction T started

[Write, T, X, old_value, new_value] → T updated data item X

[Commit, T] → Transaction T committed

[Abort, T] → Transaction T aborted

🧱 Types of Recovery

Deferred Update (No immediate changes)

Changes are written to the log first, applied only after commit.

Immediate Update

Updates are made in real-time but logged, so uncommitted changes can be undone if needed.

🔁 Checkpointing

A checkpoint is a snapshot where all committed transactions’ changes are written to disk — helps speed up recovery after a crash.

⚡ Summary
Concept Purpose
Transactions Ensure consistent, reliable operations
Deadlocks Handle concurrent access conflicts
Log-Based Recovery Restore data after crash/failure

💬 Final Thoughts:
Transactions keep databases consistent, logs keep them safe, and deadlock handling keeps them alive under concurrency. Together, they form the backbone of reliable DBMS operations. 💪