Explain schedules in 2PL with multiple granularities locking and schedules under tree(DBMS)

Locking and unlocking of the database should be done in such a way that there is no inconsistency, deadlock and no starvation.

2PL locking protocol

Every transaction will lock and unlock the data item in two different phases.

• Growing Phase − All the locks are issued in this phase. No locks are released, after all changes to data-items are committed and then the second phase (shrinking phase) starts.

• Shrinking phase − No locks are issued in this phase, all the changes to data-items are noted (stored) and then locks are released.

Consider a tree structure database as follows −

A -> B -> C

Let us now learn about the schedules which are not possible under the 2PL with multiple granularities locking and the schedules which are possible under tree protocol.

• The first lock by a transaction Ti can be any item on the graph.

• Then a data item X can be locked by the transaction Ti given that the parent of X is presently locked by Ti.

• Data items which are locked before can be unlocked at any time.

T1	T2
lock(A)
lock(B)
Unlock(A)
	lock(A)
lock(C)
Unlock(B)
	lock(B)
	Unlock(A)
	Unlock(B)
Unlock(C)

Here,

In transaction T₁

• The Growing Phase is from steps 1-3.
• The Shrinking Phase is from steps 5-6.
• Lock Point at 3.

Now, let us understand the schedules which are possible under the 2PL with multiple granularities locking and the schedules which are not possible under tree protocol.

• It is performed in two phases.
• Locking is done in the growing phase.
• Unlocking is done in the shrinking phase.
• Lock point is the point at which the growing phase ends.

T1	T2
lock(A)
	lock(B)
lock(C)
	Unlock(B)
Unlock(A)
Unlock(B)

Here,

In transaction T₂

• The Growing Phase is on step 2.
• Shrinking Phase is on step 4.
• Lock Point at 2