Transactions in DBMS

Transaction Management and Recovery Basics

In the previous article, we studied:

  • What is a transaction
  • ACID properties
  • Transaction states
  • COMMIT and ROLLBACK

Now we will understand what happens internally when failures occur and how a DBMS ensures atomicity and durability using recovery mechanisms.

1. Why Recovery is Needed

A transaction may fail due to several reasons:

  • System crash
  • Power failure
  • Hardware issues
  • Software errors
  • Disk failure

If a failure occurs during execution, the database may be left in an inconsistent state.

Example:

While transferring ₹1000:

  • ₹1000 is deducted from Account A
  • System crashes before adding it to Account B

Now the database is incorrect.

Recovery mechanisms ensure:

  • Uncommitted transactions are undone
  • Committed transactions are not lost

2. Types of Failures

2.1 Transaction Failure

Occurs due to logical errors or constraint violations.

Example:

  • Division by zero
  • Invalid input
  • Constraint violation

Such transactions are rolled back.

2.2 System Crash

Occurs due to:

  • Power failure
  • Operating system crash

Main memory is lost, but disk data remains safe.

2.3 Disk Failure

Rare but serious failure where disk data is damaged.

This requires backup recovery techniques.

3. Log-Based Recovery

To maintain atomicity and durability, DBMS maintains a log file.

A log file records every operation performed by transactions.

This log is stored on stable storage (disk).

3.1 What is Logged?

For each transaction, the log stores:

  • Transaction start
  • Old value of data
  • New value of data
  • Commit or abort status

Example log entry:

<T1, Start>
<T1, A, 5000, 4000>
<T1, B, 2000, 3000>
<T1, Commit>

This means:
Transaction T1 changed A from 5000 to 4000 and B from 2000 to 3000.

4. Write-Ahead Logging (WAL)

Write-Ahead Logging is an important rule.

Rule:

The log record must be written to disk before the actual data is updated on disk.

This ensures that:

  • If a crash occurs, we can use the log to undo or redo changes.

WAL guarantees durability and atomicity.

5. Undo and Redo Operations

After a crash, the system performs recovery using logs.

Two operations are possible:

5.1 Undo

Used for transactions that:

  • Started but did not commit.

The system restores old values using the log.

This maintains atomicity.

5.2 Redo

Used for transactions that:

  • Committed successfully before the crash
  • But changes were not fully written to disk

The system reapplies new values from the log.

This ensures durability.

6. Immediate vs Deferred Update (Basic Idea)

There are two common approaches.

6.1 Deferred Update

  • Changes are written to the log first.
  • Actual database update happens only after commit.
  • Requires only a redo during recovery.

6.2 Immediate Update

  • The database is updated immediately.
  • Both undo and redo may be required.

Modern systems mostly use immediate updates with WAL.

7. Checkpointing

Over time, log files grow very large.

To reduce recovery time, DBMS uses checkpointing.

During a checkpoint:

  • All modified data is written to disk.
  • A special checkpoint record is written in the log.

After a crash, recovery starts from the last checkpoint instead of scanning the entire log.

This improves efficiency.

8. Cascading Rollback (Brief Idea)

If one transaction reads data written by another uncommitted transaction and the first transaction fails, the dependent transaction must also be rolled back.

This is called cascading rollback.

Proper isolation levels and locking mechanisms prevent this.

Detailed discussion belongs to concurrency control.

9. Isolation Levels (Overview Only)

SQL provides different isolation levels:

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable

Higher isolation means fewer anomalies but lower performance.

Isolation will be discussed in detail under concurrency control.

Summary

  • Transactions may fail due to crashes or errors.
  • Recovery mechanisms maintain atomicity and durability.
  • Log-based recovery records every transaction action.
  • Write-Ahead Logging ensures safety.
  • Undo restores uncommitted transactions.
  • Redo preserves committed transactions.
  • Checkpointing reduces recovery time.