Two-phase locking (2PL) in DBMS Theory - Step-by-Step Execution

Concept Flow - Two-phase locking (2PL)

Start Transaction

↓

Growing Phase

↓

Lock Point

↓

Shrinking Phase

↓

End Transaction

The transaction first acquires all needed locks (growing phase), then releases them (shrinking phase) without acquiring new locks.

Execution Sample

DBMS Theory

Transaction T:
1. Lock X (growing)
2. Lock Y (growing)
3. Unlock X (shrinking)
4. Unlock Y (shrinking)

Shows a transaction acquiring locks on X and Y, then releasing them, following 2PL phases.

Analysis Table

Step	Action	Phase	Locks Held	Can Acquire New Locks?
1	Lock X	Growing	X	Yes
2	Lock Y	Growing	X, Y	No
3	Unlock X	Shrinking	Y	No
4	Unlock Y	Shrinking		No
5	Transaction Ends	End		No

💡 After step 2, no new locks can be acquired; transaction releases locks in shrinking phase until end.

State Tracker

Locks Held	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Locks	None	X	X, Y	Y	None	None

Key Insights - 2 Insights

Why can't the transaction acquire new locks after releasing one?

Because after the lock point (step 2), the transaction enters the shrinking phase where it can only release locks, not acquire new ones, as shown in execution_table rows 3 and 4.

What happens if a transaction tries to acquire a lock during the shrinking phase?

It violates 2PL rules and can cause concurrency issues; the execution_table shows 'Can Acquire New Locks?' is 'No' during shrinking phase steps.

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What locks does the transaction hold?

A Locks X and Y

B Only lock X

C No locks

D Only lock Y

💡 Hint

Refer to the 'Locks Held' column at step 2 in the execution_table.

At which step does the transaction stop acquiring new locks?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Check the 'Can Acquire New Locks?' column in the execution_table.

If the transaction tried to lock Z after step 3, what would happen according to 2PL?

A It would be allowed because locks can be acquired anytime

B It would violate 2PL because no new locks can be acquired in shrinking phase

C It would release all locks first

D It would restart the transaction

💡 Hint

Refer to the 'Phase' and 'Can Acquire New Locks?' columns after step 3 in the execution_table.

Concept Snapshot

Two-phase locking (2PL) controls transaction locks in two phases:
1. Growing phase: acquire all locks needed.
2. Shrinking phase: release locks, no new locks allowed.
This ensures serializability and prevents conflicts.

Full Transcript

Two-phase locking (2PL) is a method used in databases to control how transactions lock data. It has two phases: first, the growing phase where the transaction acquires all the locks it needs; second, the shrinking phase where it releases locks but cannot get any new ones. This method helps keep data consistent when many transactions happen at the same time. The execution table shows a transaction locking X and Y, then unlocking them, following these phases. After the lock point, no new locks can be acquired. This prevents conflicts and ensures safe data access.