Concurrency Control Protocols in DBMS

Concurrency Control Protocols in DBMS

 

In this article, we will learn about Concurrency Control Protocols in DBMS.

  • Concurrency control protocols in DBMS are procedures that are used for managing multiple simultaneous operations without conflict with each other
  • Concurrency control ensures the speed of the transactions but at the same time we should address conflicts occurring in a multi-user system and make sure the database transactions are performed concurrently without violating the Data integrity of the respective databases

Concurrency control can you broadly divided into two protocols

    • Lock-Based Protocol
    • Timestamp Based Protocol
Concurrency Control Protocols in DBMS

Lock-Based Protocol 

  • Lock based protocol mechanism is very crucial in concurrency control which controls concurrent access to a data item
  • It ensures that one transaction should not retrieve and update record while another transaction is performing a write operation on it

Example

In traffic light signal that indicates stop and go, when one signal is allowed to pass at a time and other signals are locked, in the same way in a database transaction, only one transaction is performed at a time meanwhile other transactions are locked

  • If this locking is not done correctly then it will display inconsistent and incorrect data 
  • It maintains the order between the conflicting pairs among transactions during execution
  • There are two lock modes,
    1. Shared Lock(S)
    2. Exclusive Lock(X)

Shared lock(S) 

  • Shared locks can only read without performing any changes to it from the database
  • Shared Locks are represented by S.
  • S – lock is requested using lock – s instruction.

Exclusive Lock(X) 

  • The data items accessed using this instruction can perform both read and write operations
  • Exclusive Locks are represented by X.
  • X – lock is requested using lock – X instruction. 

 

Lock Compatibility Matrix 

 SharedExclusive
SharedTrueFalse
ExclusiveFalseFalse

 

  • Lock compatibility Matrix controls whether this multiple transactions can acquire locks on the same resource at a time or not
  • 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.
  • There can be any number of transactions for holding shared locks on an item but if any transaction holds exclusive lock then item no other transaction may hold any Lock on the item.

 

Timestamp Based Protocol 

  • It is the most commonly used concurrency protocol
  • Timestamp based protocol helps DBMS to identify transactions and determines the serializability order
  • It has a unique identifier where each transaction is issued with a timestamp when it is entered into the system 
  • This protocol uses the system time  or a logical counter as a timestamp which starts working as soon as the transaction is created

Timestamp Ordering Protocol

This protocol ensure serializability among transactions in their conflicting read/write operations

  • TS(T): transaction of timestamp (T)
  • R–timestamp(X): Data item (X) of read timestamp
  • W–timestamp(X): Data item (X) of write timestamp

Timestamp Ordering Algorithms 

1. Basic Timestamp ordering

    • It ensures transaction execution is not violated by comparing the timestamp of T with Read_TS(X) and Write_TS(X)
    • When it finds that transaction execution sequence is violated transaction T is aborted and resubmitted to the system as a new transaction with a new timestamp

2. Strict Timestamp ordering

It makes sure 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.

 

Read_TS(X) > TS (T)

  • Abort and rollback transaction T and reject the operation when read timestamp is greater than timestamp transaction

Write_TS(X) > TS(T)

  • Whenever write timestamp is greater than the timestamp of the entire transaction then do not execute the write operation but continue processing
  • Because sometimes transaction with set and timestamp may be greater than TS(T) and after T in the timestamp has already written the value of X.

If neither 1 nor 2 occurs As mentioned above

  •  Execute the Write item(X) operation of transaction T and set Write_TS(X) to TS(T).
Learn more about DBMS here on this page.