/    /  DBMS- The Two-Phase Locking Protocol

The Two-Phase Locking Protocol

 

The protocol that ensures serializability in transactions is the ‘two-phase locking protocol’.

 

There are two phases in this :

  1. Growing Phase: A transaction can obtain locks, but can not release any lock.
  2. Shrinking Lock: A transaction can release locks, but can not obtain any new locks.

 

Initially, a transaction is in a growing phase. The transaction acquires the locks as needed.

Once the transaction releases a lock, it enters the shrinking phase, and it can not issue any more locks.

In Transaction T5, all the locking instructions are at the beginning and unlock instructions are at the end.

 

Transaction T5 (Transaction T3  following two-phase protocol) :

T5
lock-X(B)

read(B);

B=B-50;

write(B);

lock-X(A)

Read A

A=A+50;

write(A)

unlock(B)

unlock(A)

 

Example-2 : 

Consider the transaction T8, which reads (a1, a2,…an) values and writes a1.

Consider the transaction T9 which reads (a1, a2) values and displays the sum.

 

Transaction-8 : read (a1) ;

   read (a2) ;

 …….

 read (an) ;

 write (a1).

 

Transaction-9 : read (a1) ;

 read (a2) ;

 display (a1 + a2).

 

Upgrade: Conversion of ‘lock-s’ to ‘lock-x’.

Downgrade: Conversion of ‘lock-x’ to lock-x’.

 

Schedule-3: Incomplete schedule with lock conversion

T8T9
lock-S(a1)
lock-S(a1)
lock-S(a2)
lock-S(a2)
lock-S(a3)

lock-S(a4)

unlock(a1)

unlock(a2)

lock-S(an)
upgrade(a1)

 

 

Reference Link

The Two-Phase Locking Protocol