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.

Practice

1. What is the main purpose of Two-phase locking (2PL) in a database system?

easy

A. To allow multiple users to write to the same data simultaneously

B. To speed up database queries by skipping locks

C. To ensure transactions are executed without interfering with each other

D. To delete old data automatically after transactions

Solution

1. Step 1: Understand the role of 2PL

   Two-phase locking controls how transactions lock data to avoid conflicts.

2. Step 2: Identify the main goal

   The goal is to keep data consistent by preventing transactions from interfering with each other.

3. Final Answer:

   To ensure transactions are executed without interfering with each other -> Option C

4. Quick Check:

   2PL ensures safe transaction execution = To ensure transactions are executed without interfering with each other [OK]

Hint: 2PL controls locks to keep data safe from conflicts [OK]

Common Mistakes:

• Thinking 2PL speeds up queries by skipping locks
• Believing 2PL allows simultaneous writes on same data
• Confusing 2PL with automatic data deletion

2. Which of the following correctly describes the two phases in Two-phase locking (2PL)?

easy

A. Lock acquisition phase followed by lock release phase

B. Lock release phase followed by lock acquisition phase

C. Transaction commit phase followed by rollback phase

D. Data read phase followed by data write phase

Solution

1. Step 1: Recall the two phases of 2PL

   2PL has a phase where locks are obtained first, then a phase where locks are released.

2. Step 2: Match the correct order

   The correct order is acquiring locks first, then releasing them after work is done.

3. Final Answer:

   Lock acquisition phase followed by lock release phase -> Option A

4. Quick Check:

   2PL phases = acquire locks then release locks [OK]

Hint: Locks must be acquired before releasing in 2PL [OK]

Common Mistakes:

• Reversing the order of lock release and acquisition
• Confusing 2PL phases with commit and rollback
• Mixing data read/write phases with locking phases

3. Consider two transactions T1 and T2 using strict 2PL. If T1 locks data item X and then T2 requests a lock on X, what will happen?

medium

A. Both T1 and T2 will share the lock on X

B. T2 will immediately get the lock on X

C. T1 will release the lock on X immediately

D. T2 will wait until T1 releases the lock on X

Solution

1. Step 1: Understand strict 2PL locking rules

   In strict 2PL, a transaction holds exclusive locks until it commits or aborts.

2. Step 2: Analyze the lock request by T2

   T2 must wait because T1 still holds the lock on X and has not released it yet.

3. Final Answer:

   T2 will wait until T1 releases the lock on X -> Option D

4. Quick Check:

   Strict 2PL means waiting for lock release [OK]

Hint: New lock requests wait if current lock is held [OK]

Common Mistakes:

• Assuming locks are shared simultaneously
• Thinking locks are released immediately after use
• Believing T2 can force lock release

4. A transaction T1 acquires a lock on data item A and then releases it before completing all its operations. What problem can this cause in Two-phase locking?

medium

A. It can cause a deadlock

B. It can violate serializability and cause inconsistent data

C. It speeds up transaction processing safely

D. It prevents other transactions from accessing data

Solution

1. Step 1: Understand the 2PL rule about lock release

   In 2PL, locks should be held until the transaction finishes to ensure consistency.

2. Step 2: Analyze early lock release effect

   Releasing locks early can allow other transactions to access data prematurely, causing inconsistent results.

3. Final Answer:

   It can violate serializability and cause inconsistent data -> Option B

4. Quick Check:

   Early lock release breaks serializability = It can violate serializability and cause inconsistent data [OK]

Hint: Releasing locks too soon breaks data consistency [OK]

Common Mistakes:

• Confusing deadlock with early lock release
• Thinking early release speeds up safely
• Assuming early release blocks others

5. In a database using strict 2PL, two transactions T1 and T2 both want to update data items X and Y. T1 locks X then Y, while T2 locks Y then X. What problem can arise and how can it be resolved?

hard

A. Deadlock can occur; resolved by ordering lock acquisition consistently

B. Data corruption can occur; resolved by disabling locks

C. No problem occurs; transactions proceed normally

D. Transactions will merge automatically; resolved by splitting data

Solution

1. Step 1: Identify the problem from lock order

   T1 locks X then Y, T2 locks Y then X, creating a circular wait condition.

2. Step 2: Recognize deadlock and resolution

   This circular wait causes deadlock; it can be resolved by enforcing a consistent lock order for all transactions.

3. Final Answer:

   Deadlock can occur; resolved by ordering lock acquisition consistently -> Option A

4. Quick Check:

   Conflicting lock order causes deadlock; fix by consistent order [OK]

Hint: Consistent lock order prevents deadlocks in 2PL [OK]

Common Mistakes:

• Ignoring deadlock possibility
• Thinking disabling locks solves corruption
• Assuming transactions merge automatically