Concurrency control

Concurrency control is provided in a database to:

(i) Enforce isolation among transactions.
(ii) Preserve database consistency through consistency preserving execution of transactions.
(iii) Resolve read-write and write-read conflicts.

Various concurrency control techniques are:

(1) Two Phase Locking (2PL) Protocol
(2) Time Stamp Ordering Protocol 

Lock-Based Protocol

Locking is an operation which secures: permission to read, OR permission to write a data item. Two phase locking is a process used to gain ownership of shared resources without creating the possibility of deadlock.

The 3 activities taking place in the two phase update algorithm are:
(i) Lock Acquisition
(ii) Modification of Data
(iii) Release Lock

 

In this type of protocol, any transaction cannot read or write data until it acquires an appropriate lock on it. There are two types of lock:

1. Shared lock:

• It is also known as a Read-only lock. In a shared lock, the data item can only read by the transaction.
• It can be shared between the transactions because when the transaction holds a lock, then it can't update the data on the data item.

2. Exclusive lock:

• In the exclusive lock, the data item can be both reads as well as written by the transaction.
• This lock is exclusive, and in this lock, multiple transactions do not modify the same data simultaneously.

 

1. Two-Phase Locking Protocol:

• The two-phase locking protocol divides the execution phase of the transaction into three parts.
• In the first part, when the execution of the transaction starts, it seeks permission for the lock it requires.
• In the second part, the transaction acquires all the locks. The third phase is started as soon as the transaction releases its first lock.
• In the third phase, the transaction cannot demand any new locks. It only releases the acquired locks.

 

 Two phase locking prevents deadlock from occurring in distributed systems by releasing all the resources it has acquired, if it is not possible to acquire all the resources required without waiting for another process to finish using a lock.

This means that no process is ever in a state where it is holding some shared resources, and waiting for another process to release a shared resource which it requires. This means that deadlock cannot occur due to resource contention. 

A transaction is said to follow Two Phase Locking protocol if Locking and Unlocking can be done in two phases.

 

I. Growing Phase: 

In this phase a transaction can only acquire locks but cannot release any lock. The point when a transaction acquires all the locks it needs is called the Lock Point.

II. Shrinking Phase: 

In this phase a transaction can only release locks but cannot acquire any Note – If lock conversion is allowed, then upgrading of lock( from S(a) to X(a) ) is allowed in Growing Phase and downgrading of lock (from X(a) to S(a)) must be done in shrinking phase 

 

Let’s see a transaction implementing 2-PL. 

 

	T1	T2
1	lock-S(A)
2		lock-S(A)
3	lock-X(B)
4	…….	……
5	Unlock(A)
6		Lock-X(C)
7	Unlock(B)
8		Unlock(A)
9		Unlock(C)
10	…….	……

This is just a skeleton transaction that shows how unlocking and locking work with 2-PL. Note for:  Transaction T1: The growing Phase is from steps 1-3. The shrinking Phase is from steps 5-7. Lock Point at 3 Transaction T2: The growing Phase is from steps 2-6. The shrinking Phase is from steps 8-9. Lock Point at 6

2-PL ensures serializability, but there are still some drawbacks of 2-PL. Let’s glance at the drawbacks: 

• Cascading Rollback is possible under 2-PL.
• Deadlocks and Starvation are possible.