Transaction Processing & ACID Properties: Concurrency Control, Serializability & Locking Protocols for TU BCA

Author: Bhuban Subedi | Subject: Database Management Systems (CACS255) | Semester: Fourth Semester

In enterprise databases handling financial transactions, e-commerce orders, and university enrollment records, thousands of users concurrently read and modify data.

A Transaction is a logical unit of database processing that includes one or more database access operations (read, write, update).

In the Tribhuvan University (TU) BCA Fourth Semester DBMS (CACS255) board examinations, questions on ACID Properties, Transaction State Transition Diagrams, Concurrency Problems (Lost Update, Dirty Read), and Two-Phase Locking (2PL) carry guaranteed weightage in both 5-mark short notes and 10-mark long analytical questions.

In this guide, we will break down transaction management, understand serializability, and analyze concurrency control protocols.

1. The ACID Properties of Database Transactions

To maintain database integrity during system failures and concurrent execution, every database transaction must satisfy the ACID properties:

+-------------------+-------------------------------------------------------------------------+
| Property          | What It Means & How the DBMS Guarantees It                              |
+-------------------+-------------------------------------------------------------------------+
| **A - Atomicity** | **"All or Nothing" Rule.** Either all operations of the transaction     |
|                   | execute successfully, or none of them do. Managed by Recovery Manager   |
|                   | using transaction rollback logs (`UNDO`).                               |
+-------------------+-------------------------------------------------------------------------+
| **C - Consistency**| Execution of a transaction in isolation preserves database consistency|
|                   | (e.g., total account balance sum remains invariant). Maintained by      |
|                   | database integrity constraints and programmer application logic.        |
+-------------------+-------------------------------------------------------------------------+
| **I - Isolation** | Intermediate states of a transaction are hidden from other concurrently |
|                   | executing transactions. Guaranteed by the **Concurrency Control Manager**.|
+-------------------+-------------------------------------------------------------------------+
| **D - Durability**| Once a transaction commits (`COMMIT`), its updates persist permanently  |
|                   | on non-volatile disk storage, even if a power failure occurs immediately|
|                   | after. Guaranteed by Write-Ahead Logging (WAL) and Checkpointing.       |
+-------------------+-------------------------------------------------------------------------+

2. Transaction State Transition Diagram

During execution, a transaction transitions through distinct states:

                          +-------------------+
                          |      ACTIVE       |  (Initial state: operations executing)
                          +---------+---------+
                                    |
                    +---------------+---------------+
                    |                               |
                    v                               v
          +-------------------+           +-------------------+
          |PARTIALLY COMMITTED|           |      FAILED       |  (Error or abort)
          +---------+---------+           +---------+---------+
                    |                               |
                    v                               v
          +-------------------+           +-------------------+
          |    COMMITTED      |           |     ABORTED       |  (Rollback & restart)
          +-------------------+           +-------------------+

1. Active: The initial state; transaction stays here while executing read/write operations.
2. Partially Committed: After the final statement has executed, but before updates are flushed to disk.
3. Committed: Successfully executed and all changes are permanently stored on disk.
4. Failed: Normal execution can no longer proceed due to hardware error, logic check, or deadlock.
5. Aborted: The transaction has been rolled back (ROLLBACK), restoring the database to its pre-transaction state.

3. Concurrency Problems in Uncontrolled Execution

When transactions execute concurrently without proper lock management, three classic anomalies occur:

A. Dirty Read (Uncommitted Dependency)

Occurs when Transaction $T_2$ reads data that has been modified by Transaction $T_1$, but $T_1$ subsequently fails and aborts/rolls back. $T_2$ has now operated on bogus “dirty” data that never legally existed!

B. Lost Update Problem

Occurs when two transactions $T_1$ and $T_2$ read the same record simultaneously, modify it, and write it back. The second write operation completely overwrites and obliterates the updates made by the first transaction without its knowledge.

C. Unrepeatable Read (Inconsistent Analysis)

Occurs when Transaction $T_1$ reads a record, Transaction $T_2$ updates or deletes that record and commits, and $T_1$ reads the record a second time, getting a completely different result within the same transaction.

4. Concurrency Control: Two-Phase Locking (2PL) Protocol

To guarantee Conflict Serializability, database systems enforce locking protocols.

Two Types of Locks:

• Shared Lock ($S$): Read-only lock. Multiple transactions can hold shared locks on the same data item simultaneously.
• Exclusive Lock ($X$): Read-and-Write lock. Only one transaction can hold an exclusive lock on an item. No other transaction can obtain $S$ or $X$ locks.

The Two-Phase Locking (2PL) Rule:

A transaction is said to follow the Two-Phase Locking protocol if all locking operations precede the first unlocking operation.

Number of
Locks Held
    ^
    |          /‾‾‾‾‾\  (Peak Lock Point)
    |         /       \
    |        /         \
    |       /           \
    |      /             \
    +-----/---------------\-------------> Time
        Phase 1:          Phase 2:
      Growing Phase     Shrinking Phase

1. Growing Phase: A transaction may acquire locks, but cannot release any lock.
2. Shrinking Phase: A transaction may release locks, but cannot acquire any new lock.

Theorem: Any schedule produced by a strict Two-Phase Locking protocol is guaranteed to be Conflict Serializable and free from cascading rollbacks.

Frequently Asked Questions (FAQ)

Q1: What is a Conflict Serializable Schedule?

A concurrent schedule $S$ is conflict serializable if it can be transformed into an equivalent sequential serial schedule by swapping non-conflicting adjacent operations (two operations conflict if they belong to different transactions, access the same data item, and at least one is a write operation).

Q2: What is Write-Ahead Logging (WAL)?

WAL is the fundamental database durability protocol which dictates that any log record describing a database change must be written and flushed to non-volatile disk storage before the actual dirty database buffer page is written to disk.