Video courses for company/skill based Preparation
Purchase mock tests for company/skill building
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
- Lock-Based Protocol
- Timestamp 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
- 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,
- Shared Lock(S)
- Exclusive Lock(X)
- 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.
- 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
- 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