Transactions, Deadlocks & Log-Based Recovery in DBMS

Transactions, Deadlocks & Log-Based Recovery in DBMS

When working with databases, you must ensure that operations remain safe, consistent, and recoverable. In this article, I’ll explain:

What a transaction is, and how atomicity and rollback work

How deadlocks happen and how to handle them

What log-based recovery means and how it ensures durability

1. Transactions: Atomicity & Rollback

A transaction is a unit of work comprising one or more operations (such as UPDATE, INSERT, DELETE). The key property is atomicity — the transaction must complete entirely or not at all.

If something inside the transaction fails (due to constraint violation, runtime error, etc.), none of the changes should persist.

The database can rollback the transaction, undoing all partial changes.

Example Workflow

Start transaction

Run some updates (e.g. deduct money, add to another account)

If all succeed → commit

If any step fails → rollback

This ensures that you never end up in some halfway state with only a few operations applied.

1. Deadlocks

A deadlock occurs when two (or more) transactions hold locks on resources that the other needs, and neither can proceed or release its lock. Each waits for the other — a cycle of waiting.

Deadlock example scenario

Transaction T1 locks row A, then requests a lock on row B

Transaction T2 locks row B, then requests a lock on row A

They both wait indefinitely for each other → deadlock

In many DBMS (like MySQL, PostgreSQL), a deadlock is detected automatically. The system will abort one of the transactions with an error like:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

To handle deadlocks:

Use shorter transactions (keep locks held for minimal time)

Access objects in a consistent order in different transactions

Retry aborted transactions

1. Log-Based Recovery

To ensure durability and recoverability after crashes or failures, modern DBMS maintain transaction logs (also called write-ahead logs). These logs record each change (before it’s applied) so the system can:

Undo incomplete or uncommitted changes

Redo committed changes during recovery

How it works (simplified)

Before modifying data pages, write a record of the intended change to the log.

Apply the change to the data pages.

On commit, mark in the log that the transaction is committed.

On recovery (after crash), scan the log:

Undo all uncommitted transactions

Redo the committed ones

This ensures that even if the system crashes after a commit, the change is persisted. Likewise, half-done transactions are rolled back.

Summary & Takeaways

A transaction groups multiple operations into a single unit: either it all succeeds or it all fails (atomicity).

Deadlocks arise when transactions block each other; DBMS detect them and abort one transaction, but application logic should minimize them.

Log-based recovery (using a transaction log) is vital for ensuring durability and allowing rollback or redo during system recovery.