Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

Concurrency control protocols

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 ensure speed of the transactions but at the same time we should address conflicts occurring in a multiuser system and make sure the database transactions are performed from currently without violating the Data integrity of the respective databases
  • Concurrency control can you broadly divided into two protocols
    1. Lock-Based Protocol
    2. Timestamp Based Protocol

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 correct data the order
  • 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) H4
  • 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 

Shared Exclusive
Shared True False
Exclusive False False
  • 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 and item no other transaction may hold any Lock on the item

Timestamp Based Protocol H3

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 H4

 

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 H4