Locking Protocols and 2PL

Locking is the primary mechanism databases use to control concurrent access to data. Without locking, two transactions could simultaneously update the same row, causing lost updates, dirty reads, and inconsistent data.

This chapter covers lock types, lock compatibility, the Two-Phase Locking protocol (2PL), and its variants.

---

Learning Objectives

After completing this chapter, you will be able to:

• Differentiate between shared (S) and exclusive (X) locks.
• Understand lock compatibility matrices.
• Explain Two-Phase Locking (2PL) and its phases.
• Describe Strict 2PL and Rigorous 2PL.
• Explain lock escalation and lock conversion.
• Understand how locking guarantees serializability.
• Answer locking and 2PL interview questions.

---

Lock Types

Shared Lock (S-Lock)

• Also called a Read Lock.
• Multiple transactions can hold a shared lock on the same data item simultaneously.
• Any transaction with an S-lock can read the data.
• No transaction can write (acquire X-lock) while an S-lock is held.

Exclusive Lock (X-Lock)

• Also called a Write Lock.
• Only one transaction can hold an X-lock on a data item.
• No other transaction can read or write (acquire S-lock or X-lock) while an X-lock is held.
• The transaction with the X-lock can both read and write.

---

Lock Compatibility Matrix

Requested Lock	Current S-Lock	Current X-Lock
S-Lock	Granted	Denied
X-Lock	Denied	Denied

Rule: S-locks are compatible with other S-locks. X-locks are incompatible with everything.

---

Lock Granularity

Locks can be acquired at different levels of granularity. Finer granularity allows more concurrency but requires more locks.

Level	Description	Concurrency	Overhead
Database	Lock entire database	Lowest	Lowest
Table	Lock entire table	Low	Low
Page	Lock a disk page	Medium	Medium
Row	Lock a single row	High	High
Field	Lock a single column value	Highest	Highest

Real databases: Most use row-level locking by default (InnoDB, PostgreSQL, Oracle). They may escalate to table-level locks when a transaction locks many rows.

---

Two-Phase Locking (2PL)

2PL is a protocol that guarantees serializability. It divides lock operations into two phases.

Phase 1: Growing Phase

• The transaction acquires locks.
• The transaction cannot release any locks.
• Lock requests (S or X) are made as needed.

Phase 2: Shrinking Phase

• The transaction releases locks.
• The transaction cannot acquire any new locks.
• Usually starts just before commit or abort.

Transaction T1:
Growing Phase:  Lock(A), Lock(B), Lock(C)
Shrinking Phase: Unlock(A), Unlock(B), Unlock(C)

Why 2PL Works

2PL ensures that if one transaction’s operations on a data item interleave with another’s, one of them must have released its lock before the other acquired it — preventing non-serializable schedules.

---

Variations of 2PL

Basic 2PL

Lock acquisition → Lock release.

A transaction can release locks at any point in the shrinking phase, including before commit.

Problem: If T1 releases a lock, T2 reads the value, and T1 aborts, T2 has read uncommitted data (cascading rollback possible).

---

Strict 2PL

• All exclusive (X) locks are held until the transaction commits or aborts.
• Shared (S) locks can be released earlier.

T1: Lock-S(A), Lock-X(B), ... Commit, Unlock-X(B), Unlock-S(A)

Benefit: Prevents cascading rollbacks. If T1 aborts after releasing S-lock but before releasing X-lock, T2 that read A (with S-lock) is not affected because T1’s write did not commit.

Used by: Most real databases (InnoDB uses Strict 2PL).

---

Rigorous 2PL

• All locks (both S and X) are held until the transaction commits or aborts.

T1: Lock-S(A), Lock-X(B), ... Commit, Unlock-S(A), Unlock-X(B)

Benefit: Simple to implement, easy to guarantee serializability. Cost: Lower concurrency (all locks held until commit).

---

Lock Conversion (Lock Upgrade)

Sometimes a transaction needs to upgrade a lock from S to X.

Example

1. T1 reads row (acquires S-lock).
2. T1 decides to update the same row (needs X-lock).
3. T1 requests an upgrade: S → X.

Behavior

• The S-lock must be the only S-lock on the data item (no other readers).
• If other transactions hold S-locks, T1 waits.
• The upgrade request is treated as requesting an X-lock.

In 2PL

An upgrade request is a lock acquisition. It can only happen in the growing phase.

---

Lock Escalation

When a transaction holds many fine-grained locks (e.g., thousands of row locks), the DBMS may automatically escalate them to a coarser lock (table lock).

Why

• Reduces memory usage for lock management.
• Reduces lock management overhead.

Example

UPDATE Employees SET Salary = Salary * 1.1;

This updates every row. Instead of keeping 10,000 row locks, the DBMS escalates to a single table lock.

Tradeoff

Benefit	Cost
Lower memory overhead	Reduced concurrency (other transactions blocked)
Faster lock management	May cause unexpected blocking in applications

---

Lock Modes Supported by InnoDB

Lock Mode	Description
Shared (S)	Read lock, compatible with other S-locks
Exclusive (X)	Write lock, incompatible with everything
Intention Shared (IS)	Intends to set S-locks at finer granularity
Intention Exclusive (IX)	Intends to set X-locks at finer granularity
SIX	Shared + Intention Exclusive (combination)

Intention locks allow the DBMS to efficiently check table-level lock conflicts without scanning all row-level locks.

---

Interview Deep Dive

---

Key Takeaways

• Shared locks (S) allow multiple readers; exclusive locks (X) allow one writer.
• S-locks are compatible with S-locks; X-locks are incompatible with everything.
• 2PL divides lock operations into growing (acquire only) and shrinking (release only) phases.
• 2PL guarantees conflict serializability.
• Strict 2PL holds X-locks until commit (prevents cascading aborts).
• Rigorous 2PL holds all locks until commit (highest safety, lowest concurrency).
• Lock conversion (upgrade from S to X) is an acquisition and must occur in the growing phase.
• Lock escalation trades concurrency for memory efficiency.
• Row-level locking provides the highest concurrency; table-level locking is a fallback.