Concurrency Control Techniques in DBMS

by

The concurrency control technique is a process of managing the simultaneous execution of transactions in a shared database. It preserves the database consistency, enforces the isolation of different transactions, and resolves the conflicts which occur due to the read-write operation of transactions.

The various Concurrency Control techniques are

  1. Locking based protocol
  2. Timestamp based protocol
  3. Optimistic protocol
  4. Multiversion protocol

1). Locking based protocol

Locking is a fundamental concurrency control technique used in Database Management Systems (DBMS) to manage access to data by multiple transactions concurrently. Its primary goal is to ensure data integrity and consistency by controlling how transactions interact with each other when accessing the same data. Locking can be detailed as follows:

Types of Locks

  • Exclusive Locks (X-Locks): These locks are acquired on a data item when a transaction wants to write (update or delete) the data. While an exclusive lock is held on a data item, no other transaction can read or write that item.
  • Shared Locks (S-Locks): These locks are acquired on a data item when a transaction wants to read the data. Multiple transactions can hold shared locks on the same data item simultaneously, allowing concurrent reads. However, if a shared lock is held on a data item, no transaction can acquire an exclusive lock on that item until all shared locks are released.

2). Timestamp based protocol

Timestamp-based protocols are a method for concurrency control in Database Management Systems (DBMS) that use timestamps to maintain consistent transaction ordering and ensure serializability. They help manage how transactions interact with the database and with each other, particularly focusing on the timing of these interactions to prevent conflicts and ensure data integrity. Here’s a detailed look into how timestamp-based protocols work:

Principle of Operation

Each transaction is assigned a unique timestamp when it begins. This timestamp reflects the transaction’s start time and is used to order transactions in a consistent manner. The DBMS uses these timestamps to decide the order in which read and write operations should occur, applying two main rules:

  1. Write Timestamp (WTS): If a transaction T wants to write to an object, the DBMS checks the timestamp of the last read and write operations on that object. If T’s timestamp is older than either of these timestamps, the write operation could potentially cause a conflict with operations that have already been viewed by other transactions. In such cases, the write operation is typically aborted to preserve the consistency of the database.
  2. Read Timestamp (RTS): If a transaction T wants to read an object, the DBMS checks the timestamp of the last write operation on that object. If T’s timestamp is older than this write timestamp, reading the current value could violate serializability because T would be seeing a future version of the object. Depending on the protocol variant, the read might be aborted, or T might be allowed to read an older version of the object if multi-version concurrency control (MVCC) is used.

    3). Optimistic Concurrency Control (OCC)/Validation Based Protocol

    Optimistic Concurrency Control (OCC) is a concurrency control method used in database systems to manage access to the database in environments where conflicts among transactions are expected to be rare. OCC operates on the principle that multiple transactions can frequently complete without interfering with each other, making it an attractive alternative to locking mechanisms in certain scenarios. Here’s a detailed look at how OCC works:

    Principle of Operation

    OCC is characterized by its non-locking approach, where transactions are allowed to execute without imposing locks on the database objects they access. It consists of three main phases:

    1. Read Phase: A transaction reads database objects and makes changes to local copies without affecting the actual database. It records the version of the objects it accessed to ensure consistency.
    2. Validation Phase: Before committing its changes, a transaction enters the validation phase to determine if any other transactions have modified the objects it has read or written to. The validation ensures that the transaction’s view of the data is still consistent with the current state of the database.
    3. Write Phase (Commit Phase): If the transaction passes the validation phase without detecting conflicts, it applies its changes to the database. If a conflict is detected, the transaction is rolled back and may be retried.

    4). Multi-Version protocol

    Multi-Version Concurrency Control (MVCC) is a concurrency control technique widely used in database management systems (DBMS) to enhance performance by allowing multiple transactions to access the same data simultaneously without blocking each other. MVCC achieves this by maintaining different versions of data objects, enabling read operations to occur without waiting for write locks to be released, thereby reducing contention and improving throughput. Here’s a detailed explanation of how MVCC works and its implications:

    How MVCC Works

    1. Version Creation: When a transaction modifies a data object (such as a row in a database table), instead of overwriting the existing data, the system creates a new version of that object. Each version is associated with a timestamp or transaction ID, indicating when it was created.
    2. Read Operations: When a transaction reads a data object, instead of waiting for ongoing write operations to complete, it reads the most recent version of the object that was committed before the transaction started. This ensures that reads are non-blocking and consistent, reflecting a snapshot of the database at a specific point in time.
    3. Write Operations: Write operations create new versions of data objects. Other transactions can continue to read older versions of these objects, ensuring that writes do not block reads.
    4. Garbage Collection: Over time, old versions of data objects that are no longer needed (because no transactions require them for consistency) can be purged from the system to reclaim storage space.

    5). Two-Phase Locking (2PL)

    Two-Phase Locking (2PL) is a concurrency control method used in database systems to ensure serializability, the highest level of isolation in transaction processing. It’s a locking protocol that divides the execution of a transaction into two distinct phases: the growing phase and the shrinking phase. Here’s an in-depth look at how 2PL works and its significance:

    How 2PL Works:

    1. Growing Phase: During this phase, a transaction can acquire locks but cannot release any. The transaction continues to request and lock all the resources it needs. As locks are acquired, the set of locked items can grow, but no locks are released. This phase ends when the transaction has acquired all the locks it needs.
    2. Shrinking Phase: This phase starts after the transaction acquires all required locks and begins to release them. In this phase, the transaction can release locks but cannot acquire any new ones. The set of locked items can only shrink. No new locks are acquired until the end of the transaction.

    6). Deadlock Handling

    Deadlock handling is a critical aspect of concurrency control in Database Management Systems (DBMS) and other multi-threaded environments. A deadlock occurs when two or more transactions hold locks on resources and each waits for the other to release their locks before proceeding, resulting in a standstill where none of the transactions can move forward. Effective deadlock handling strategies are essential to maintain system efficiency and prevent transaction stalls.