DBMS Notes

Two Phase Locking (2PL) Protocol

  • 2-PL is an extension of Shared/Exclusive locking.
  • It is used to reduce the problems of Shared/Exclusive locking
  • Any schedule which is following 2PL will always serializable which was not in Shared/Exclusive locking

Phases of 2PL

There are two basic phases of two phase locking which are explained below

I. Growing Phase: In Growing phase, only Locks are acquired by a transaction and no locks are released by a transaction at that time.

II. Shrinking Phase: In shrinking phase only Locks are released by transaction and no locks are acquired by a transaction at that time.

Important in 2PL is Lock Point

Lock Point: The Point at which the growing phase ends (i.e., when transaction takes the final lock) is called Lock Point.

Example: The following diagrams shows growing and shrinking phases in 2-PL.

Transaction T1

  • Growing Phase: From Time 1 to 3.
  • Shrinking Phase: From Time 5 to 7
  • Lock Point: At time 3

Transaction T2

  • Growing Phase: From Time 2 to 6
  • Shrinking Phase: From Time 8 to 9
  • Lock Point: At Time 6

 2PL Transaction Execution Sequence

As we say, 2PL will generate he serial schedule then what will be the sequence of transaction execution?

Answer:  If locking point of Transaction T1 comes earlier than transaction T2 then the T1 will execute first than T2.  As given below in diagram

Important: Keep in mind when multiple transactions needs to acquire the shared or exclusive lock then it must follow the lock compatibility table.

In the following schedule where T1 holds the shared lock on “A” and T2 also required exclusive lock on “A”. This case is not a possible according to lock compatibility table. So, T2 will be blocked(waiting state) until T1 release the the exclusive lock on “A”.

Advantages of 2-PL

2PL always ensure the Serilizability. It means the schedule in 2PL must be serial. No loop existing in transaction execution sequence  graph.

Disadvantages of 2-PL

1. Produced schedule through 2PL locking may be irrecoverable

2. Produced schedule through 2PL locking may contains a deadlock problem

3. Produced schedule through 2PL locking may contains a Starvation Problem

4. Cascading Rollback Problem

If the rollback of one transaction cause the rollback of other dependent transactions called cascading rollback.

As 2PL remove the Irrecoverability problem but still cascading, deadlock and starvation problem is there. To remove such problems we use different types of 2PL.

We will see types of 2PL in next lecture.

Help Other’s By Sharing…

Contact Us

Burewala, Vehari, Punjab, Pakistan

cstaleem1@gmail.com

Website: CStaleem.com

Pin It on Pinterest