Transaction In DBMS

In Database Management Systems (DBMS), transaction management ensures that database operations grouped under a transaction are executed reliably, consistently, and securely. It guarantees that the database remains correct and free from anomalies, even in cases of system crashes, errors, or concurrent transactions.

The key to transaction management is maintaining the ACID properties:

  • Atomicity: All operations of a transaction are executed, or none are.
  • Consistency: Transactions bring the database from one valid state to another.
  • Isolation: Concurrent transactions do not interfere with each other.
  • Durability: Once committed, changes persist even after system failures.

Transaction Operations

  1. Read Operation – Fetches data from the database.

  2. Write Operation – Modifies data in the database.

  3. Commit Operation – Permanently saves changes made by a transaction.

  4. Rollback Operation – Undoes changes if a transaction fails or is aborted.

Transaction States

  1. Active – Transaction is executing.

  2. Partially Committed – All operations done, waiting for final commit.

  3. Committed – Transaction changes are permanent.

  4. Failed – Error occurred; transaction cannot proceed.

  5. Aborted – Transaction rolled back, database restored to consistent state.

Concurrency in DBMS

Concurrency occurs when multiple transactions execute simultaneously. While concurrency improves throughput and resource utilization, it can lead to anomalies if not managed properly.

Problems in Concurrency

  1. Lost Update Problem – Two transactions overwrite each other’s updates.

  2. Dirty Read – A transaction reads uncommitted changes from another transaction.

  3. Non-Repeatable Read – A transaction reads the same data twice but gets different results.

  4. Phantom Read – A transaction reads a set of rows twice, and new rows appear in the second read.

Concurrency Control Techniques

Transaction management uses protocols to ensure serializability and isolation:

1. Lock-Based Protocols

  • Shared Lock (S) – Allows multiple reads.

  • Exclusive Lock (X) – Allows a single write.

  • Two-Phase Locking (2PL) – Divides execution into a growing phase (acquire locks) and shrinking phase (release locks).

2. Timestamp-Based Protocols

  • Assign timestamps to each transaction.

  • Ensure conflicting operations are executed in timestamp order.

3. Optimistic Concurrency Control

  • Transactions execute without restrictions.

  • Validation is performed before commit to check for conflicts.

4. Multiversion Concurrency Control (MVCC)

  • Maintains multiple versions of data.

  • Readers access older versions while writers create new ones.

  • Used in modern DBMS (e.g., PostgreSQL, Oracle).

Schedules in DBMS

A schedule defines the sequence in which operations (read/write) of multiple transactions are executed.

Types of Schedules

  1. Serial Schedule – Transactions execute one after another without interleaving.

  2. Non-Serial Schedule – Operations of multiple transactions are interleaved.

  3. Recoverable Schedule – A transaction commits only after the one it depends on has committed.

  4. Cascadeless Schedule – Prevents cascading rollbacks by disallowing reads of uncommitted data.

  5. Strict Schedule – Ensures transactions can neither read nor write data modified by uncommitted transactions.

Serializability of Schedules

Serializability ensures that a non-serial schedule is equivalent to some serial schedule and thus preserves consistency.

  1. Conflict Serializability – Based on conflicts between operations (Read-Write, Write-Read, Write-Write). Checked using precedence graphs.

  2. View Serializability – Two schedules are equivalent if they produce the same output and preserve reads/writes dependencies.

  •  

Deadlock in Transactions

When two or more transactions wait for resources locked by each other, a deadlock occurs.

  • Deadlock Prevention – Assign priorities, force rollbacks to avoid deadlock.

  • Deadlock Detection – Use wait-for graphs to detect cycles.

  • Deadlock Recovery – Abort and restart one or more transactions.

Transaction Recovery

To ensure durability, recovery mechanisms handle failures:

  • Deferred Update – Updates applied only after commit.

  • Immediate Update – Updates applied during transaction execution but undone if rollback occurs.

  • Shadow Paging – Keeps a backup page for recovery.

  • Checkpoints – Save the database state periodically to reduce recovery time.

Conclusion

Transaction Management in DBMS is the backbone of reliable databases. It:

  • Ensures ACID properties.

  • Manages concurrency and schedules for multiple transactions.

  • Uses locking, timestamps, and validation to control conflicts.

  • Provides deadlock handling and recovery mechanisms to safeguard against crashes.

Without proper transaction management, databases would suffer from inconsistencies, data loss, and anomalies in multi-user environments.