Concurrency Control Protocols in Database

Concurrency control can be divided into two protocols

1. Lock-Based Protocol
2. Timestamp Based Protocol

1. Lock-Based Protocol

  • Lock is a mechanism which is important in a concurrent control.
  • It controls concurrent access to a data item.
  • It assures that one process should not retrieve or update a record which another process is updating.

    • For example, in traffic, there are signals which indicate stop and go. When one signal is allowed to pass at a time, then other signals are locked. Similarly, in database transaction only one transaction is performed at a time and other transactions are locked.

  • If the locking is not done properly, then it will display the inconsistent and corrupt data.
  • It manages the order between the conflicting pairs among transactions at the time of execution.
There are two lock modes,

1. Shared Lock
2. Exclusive Lock

Shared Locks are represented by S. The data items can only read without performing modification to it from the database. S – lock is requested using lock – s instruction.

Exclusive Locks are represented by X. The data items can be read as well as written. X – lock is requested using lock – X instruction.  

Lock Compatibility Matrix
  • Lock Compatibility Matrix controls whether multiple transactions can acquire locks on the same resource at the same time.

    • SharedExclusive
    SharedTrueFalse
    ExclusiveFalseFalse

  • If a resource is already locked by another transaction, then a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock.
  • Any number of transactions can hold shared locks on an item, but if any transaction holds an exclusive lock on item, no other transaction may hold any lock on the item.

2. Timestamp Based Protocol

  • Timestamp Based Protocol helps DBMS to identify the transactions.
  • It is a unique identifier. Each transaction is issued a timestamp when it enters into the system.
  • Timestamp protocol determines the serializability order.
  • It is most commonly used concurrency protocol.
  • It uses either system time or logical counter as a timestamp.
  • It starts working as soon as a transaction is created.
Timestamp Ordering Protocol
  • The TO Protocol ensures serializability among transactions in their conflicting read and write operations.
  • The transaction of timestamp (T) is denoted as TS(T).
  • Data item (X) of read timestamp is denoted by R–timestamp(X).
  • Data item (X) of write timestamp is denoted by W–timestamp(X).

Following are the Timestamp Ordering Algorithms

1. Basic Timestamp Ordering
  • It compares the timestamp of T with Read_TS(X) and Write_TS(X) to ensure that the transaction execution is not violated.
  • If the transaction execution order is violated, transaction T is aborted and resubmitted to the system as a new transaction with a new timestamp.
2. Strict Timestamp Ordering
  • It ensures that the schedules are both strict for easy recoverability and conflict serializability.
3. Thomas's Write Rule
It does not enforce conflict serializability. It rejects some write operations, by modifying the checks for the write_item(X) operation as follows,

1. If Read_TS(X) > TS(T) (read timestamp is greater than timestamp transaction), then abort and rollback transaction T and reject the operation.

2. If Write_TS(X) > TS(T) (write timestamp is greater than timestamp transaction), then do not execute the write operation but continue processing. Because some transaction with a timestamp is greater than TS(T) and after T in the timestamp has already written the value of X.

3. If neither the condition transaction 1 nor the condition in transaction 2 occurs, then execute the Write_item(X) operation of transaction T and set Write_TS(X) to TS(T).
▲