Concurrency Control 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 

• 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).