Two-Phase Locking Protocol
Growing Phase
Read aloud is unavailable in this browser.
The Art of Lock Acquisition
The Growing Phase is where transactions prepare for their work by systematically acquiring all the locks they will need. Think of it as a construction crew securing a building site before any work begins—once the perimeter is established, work can proceed safely without external interference.
But lock acquisition is not as simple as "request and receive." Transactions must navigate conflicts with other transactions, make strategic decisions about lock ordering, and handle the possibility of waiting or being denied. The Growing Phase is where the complexity of concurrent access truly manifests.
In this page, we'll dissect every aspect of the Growing Phase, from the fundamental mechanics of lock acquisition to the sophisticated strategies that production databases use to maximize concurrency while maintaining correctness.
By the end of this page, you will understand the mechanics of lock acquisition during the growing phase, how the lock manager processes lock requests and handles conflicts, different lock acquisition strategies and their trade-offs, and how transactions handle blocked/waiting states during lock acquisition.
Mechanics of Lock Acquisition
During the Growing Phase, a transaction systematically acquires locks on data items it intends to access. Let's examine the step-by-step mechanics of this process:
The Lock Request Lifecycle:
Step 1: Lock Request Issuance
When a transaction needs to access a data item, it first issues a lock request to the database's lock manager. The request specifies:
- Transaction ID (TID): Unique identifier of the requesting transaction
- Data Item Identifier: The specific item to lock (row ID, page ID, table ID, etc.)
- Lock Mode: Shared (S) for reads, Exclusive (X) for writes
Step 2: Compatibility Check
The lock manager consults its data structures to determine if the request can be granted immediately:
1234567891011121314151617181920212223242526272829303132333435
FUNCTION process_lock_request(tid, data_item, mode): // Get current lock state for data item current_locks = lock_table.get(data_item) IF current_locks is EMPTY: // No existing locks - grant immediately lock_table.add(data_item, tid, mode) RETURN GRANTED // Check compatibility with all existing locks FOR EACH (holder_tid, holder_mode) IN current_locks: IF holder_tid == tid: // Same transaction - check for lock upgrade IF mode == EXCLUSIVE AND holder_mode == SHARED: // Lock upgrade request IF only_holder(data_item, tid): upgrade_lock(data_item, tid, EXCLUSIVE) RETURN GRANTED ELSE: // Others hold shared locks - must wait wait_queue.add(data_item, tid, mode) RETURN WAITING ELSE: // Already have compatible or stronger lock RETURN GRANTED ELSE: // Different transaction - check compatibility matrix IF NOT compatible(holder_mode, mode): // Conflict - add to wait queue wait_queue.add(data_item, tid, mode) RETURN WAITING // All existing locks are compatible lock_table.add(data_item, tid, mode) RETURN GRANTED| Existing: S (Shared) | Existing: X (Exclusive) | |
|---|---|---|
| Request: S (Shared) | ✓ Compatible — Both can read | ✗ Conflict — Writer blocks readers |
| Request: X (Exclusive) | ✗ Conflict — Writer blocks readers | ✗ Conflict — Writers block each other |
The Wait Queue and Blocking
When a lock request conflicts with existing locks, the requesting transaction enters a wait queue. Understanding how wait queues work is essential for predicting transaction behavior and diagnosing performance issues.
Wait Queue Structure:
Each lockable data item maintains its own wait queue—an ordered list of transactions waiting for the lock. The queue follows a defined ordering policy (typically FIFO) to ensure fairness.
123456789101112131415161718192021222324252627
DATA ITEM: Account_A (Account Row with ID = 42) CURRENT STATE:┌─────────────────────────────────────────────────────────────┐│ LOCK HOLDER: T₁ (Exclusive Lock) ││ Currently executing write operations │├─────────────────────────────────────────────────────────────┤│ WAIT QUEUE: ││ Position 1: T₂ (Requests Shared Lock) — Waiting since t₁ ││ Position 2: T₃ (Requests Exclusive Lock) — Waiting since t₂││ Position 3: T₄ (Requests Shared Lock) — Waiting since t₃ │└─────────────────────────────────────────────────────────────┘ WHEN T₁ RELEASES THE LOCK:1. Lock Manager removes T₁ from holder list2. Examines wait queue head (T₂)3. T₂ requests S lock — No conflict with empty holder list → GRANT4. T₄ also requests S lock — Compatible with T₂'s S lock → GRANT5. T₃ requests X lock — Conflicts with T₂ and T₄ → REMAINS WAITING NEW STATE:┌─────────────────────────────────────────────────────────────┐│ LOCK HOLDERS: T₂ (Shared), T₄ (Shared) │├─────────────────────────────────────────────────────────────┤│ WAIT QUEUE: ││ Position 1: T₃ (Requests Exclusive Lock) │└─────────────────────────────────────────────────────────────┘Notice how both T₂ and T₄ received their shared locks simultaneously, even though T₄ was behind T₃ in the queue. This is called lock batching or group mode granting—the lock manager grants all compatible requests at the head of the queue, skipping over incompatible ones temporarily. This optimization increases concurrency but can lead to starvation of exclusive lock requests if shared requests continuously arrive.
Transaction States During Waiting:
When a transaction enters the wait queue, it transitions to a BLOCKED state. During this state:
- CPU resources are released — The transaction's thread/process suspends
- Other transactions continue — The blocked transaction doesn't consume CPU
- Timeout monitoring begins — The lock manager tracks how long the transaction waits
- Deadlock detection may trigger — Periodic checks determine if a deadlock cycle exists
- •FIFO (First-In-First-Out) — Requests granted in arrival order. Simple and fair, but may limit concurrency when compatible requests arrive out of order.
- •Priority-Based — Higher-priority transactions jump the queue. Useful for time-critical operations but can starve low-priority transactions.
- •Lock Mode Priority — Some systems prioritize exclusive requests to prevent writer starvation. Readers don't continuously preempt writers.
- •Wound-Wait / Wait-Die — Policies based on transaction age to prevent deadlocks (covered in deadlock module). Older transactions may preempt younger ones.
Lock Upgrade Operations
A common scenario during the Growing Phase is lock upgrade—a transaction that initially acquired a shared lock needs to modify the data and must upgrade to an exclusive lock.
Lock Upgrade Mechanics:
123456789101112131415161718192021222324
-- Transaction T₁: Read-Modify-Write Pattern-- This is extremely common - read data, process it, update based on read value BEGIN TRANSACTION T₁; -- Step 1: Read the current balance (acquire shared lock)SELECT balance FROM accounts WHERE id = 42;-- Lock Manager grants: T₁ holds S-lock on row 42 -- Application logic: calculate 10% interestnew_balance = balance * 1.10; -- Step 2: Update with new balance (need exclusive lock)UPDATE accounts SET balance = @new_balance WHERE id = 42;-- Lock Manager receives: T₁ requests X-lock upgrade on row 42 -- UPGRADE SCENARIO 1: T₁ is the ONLY holder of S-lock-- → Upgrade granted immediately, T₁ now holds X-lock -- UPGRADE SCENARIO 2: T₂ also holds S-lock on row 42-- → T₁ must WAIT for T₂ to release its S-lock-- → T₁ enters wait queue for upgrade COMMIT;Lock upgrades are a common source of conversion deadlocks. If two transactions both hold shared locks on the same item and both attempt to upgrade to exclusive, neither can proceed—each is waiting for the other to release its shared lock. This is called a deadly embrace and must be resolved by aborting one transaction.
123456789101112131415161718192021222324252627
UPGRADE DEADLOCK SCENARIO: Initial State:┌─────────────────────────────────────────────────────┐│ Data Item: Row 42 ││ Lock Holders: T₁ (S-lock), T₂ (S-lock) ││ Wait Queue: Empty │└─────────────────────────────────────────────────────┘ T₁ requests X-lock (upgrade):┌─────────────────────────────────────────────────────┐│ Lock Holders: T₁ (S-lock), T₂ (S-lock) ││ Wait Queue: T₁ (upgrade to X) — blocked by T₂ │└─────────────────────────────────────────────────────┘ T₂ requests X-lock (upgrade):┌─────────────────────────────────────────────────────┐│ Lock Holders: T₁ (S-lock), T₂ (S-lock) ││ Wait Queue: T₁ (upgrade to X) — blocked by T₂ ││ T₂ (upgrade to X) — blocked by T₁ │└─────────────────────────────────────────────────────┘ DEADLOCK DETECTED:T₁ waits for T₂ to release S-lockT₂ waits for T₁ to release S-lock RESOLUTION: Abort one transaction (typically the younger one)Strategies to Avoid Upgrade Deadlocks:
-
Update Locks (U-locks): Some database systems introduce an intermediate lock mode called Update Lock. A U-lock is compatible with S-locks for reading but indicates intent to upgrade. Only one transaction can hold a U-lock on an item at a time, preventing multiple upgrade attempts.
-
SELECT FOR UPDATE: SQL provides syntax to acquire an exclusive lock immediately during the read, avoiding the upgrade scenario entirely:
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE; -
Optimistic Patterns: Read with S-lock, prepare the update, then re-verify and apply atomically. If the data changed, retry the entire operation.
Lock Acquisition Strategies
Not all transactions acquire locks the same way. Different strategies offer different trade-offs between simplicity, performance, and deadlock avoidance:
- •Description: Acquire locks on-demand as data items are accessed
- •Advantage: Simple implementation; no pre-planning required
- •Advantage: Minimal lock holding time before first access
- •Disadvantage: Susceptible to deadlocks
- •Disadvantage: May need to wait mid-transaction
- •Used by: Most OLTP workloads
- •Description: Acquire ALL locks at transaction start before any operation
- •Advantage: Zero deadlocks (if all locks available, proceed; else wait)
- •Advantage: Predictable execution once started
- •Disadvantage: Must know all data items in advance
- •Disadvantage: Holds locks longer than necessary
- •Used by: Batch processing, stored procedures
Ordered Lock Acquisition:
A middle-ground approach that prevents deadlocks while allowing dynamic acquisition:
Rule: All transactions must acquire locks in a predefined global order (e.g., by data item ID, alphabetically by table.row, etc.)
Why it prevents deadlocks:
- Deadlocks require cyclic waiting: T₁ waits for T₂, T₂ waits for T₁
- With ordered acquisition, if T₁ holds lock on item A (earlier in order) and wants B (later in order), T₂ cannot be holding B while wanting A (because T₂ would have acquired them in order too)
- No cycles are possible in the wait graph
1234567891011121314151617181920
-- GLOBAL ORDER: Lock items by ascending ID-- All transactions agree to acquire locks in ID order -- Transaction T₁ needs to access items: 5, 2, 9-- Ordered acquisition: 2, 5, 9 (ascending order)LOCK(item_2);LOCK(item_5);LOCK(item_9);-- ... perform operations ... -- Transaction T₂ needs to access items: 9, 2-- Ordered acquisition: 2, 9 (ascending order)LOCK(item_2); -- If T₁ holds this, T₂ waits HERELOCK(item_9); -- T₂ will never hold 9 while waiting for 2-- ... perform operations ... -- DEADLOCK PREVENTION:-- If T₁ holds 2 and wants 9, T₁ will proceed (9 > 2, correct order)-- If T₂ holds 9 and wants 2, IMPOSSIBLE (would violate T₂'s ordering)-- Therefore, no cyclic waits can occurOrdered lock acquisition is elegant but often impractical for complex transactions where data access patterns are data-dependent. If a transaction needs to access different items based on runtime values, it cannot know the lock order in advance. Most systems use dynamic acquisition with deadlock detection instead.
Implicit vs Explicit Locking
In practice, developers rarely issue explicit lock commands. Instead, the database automatically acquires locks based on the SQL statements executed. Understanding this implicit locking behavior is crucial for predicting lock acquisition during the Growing Phase.
| SQL Statement | Lock Type Acquired | Lock Granularity | Notes |
|---|---|---|---|
| SELECT ... FROM table | Shared (S) | Row-level (typically) | May escalate to page/table if many rows |
| SELECT ... FOR UPDATE | Exclusive (X) | Row-level | Immediate exclusive lock for update intent |
| SELECT ... FOR SHARE | Shared (S) + Intent | Row-level | Explicit shared lock with no upgrade |
| INSERT INTO table | Exclusive (X) | New row + Index entries | Also locks index pages briefly |
| UPDATE table SET ... | Exclusive (X) | Affected rows | S-lock first if WHERE evaluated |
| DELETE FROM table | Exclusive (X) | Target rows | Similar to UPDATE pattern |
| CREATE INDEX | Schema lock | Table-level | Blocks all DML on table |
| ALTER TABLE | Schema lock | Table-level | Exclusive schema modification lock |
1234567891011121314151617181920212223242526272829
-- Example: What locks are acquired during this transaction? BEGIN TRANSACTION; -- Statement 1: Read customer dataSELECT name, email FROM customers WHERE id = 100;-- IMPLICIT: S-lock on customers row where id=100 -- Statement 2: Read order historySELECT * FROM orders WHERE customer_id = 100;-- IMPLICIT: S-lock on all orders rows where customer_id=100-- (may be multiple rows - multiple S-locks acquired) -- Statement 3: Update customer's last_visit timestampUPDATE customers SET last_visit = NOW() WHERE id = 100;-- IMPLICIT: Request X-lock on customers row where id=100-- Since we already hold S-lock, this is a LOCK UPGRADE-- If upgrade succeeds, we now hold X-lock -- Statement 4: Insert new orderINSERT INTO orders (customer_id, total, created_at) VALUES (100, 99.99, NOW());-- IMPLICIT: X-lock on new row-- IMPLICIT: Brief X-lock on index pages for orders table COMMIT;-- IMPLICIT: All locks released -- GROWING PHASE: Covered statements 1-4 (acquired 3+ locks)-- SHRINKING PHASE: Started at COMMIT (all locks released together)While implicit locking handles most cases, SQL provides explicit locking syntax for fine-grained control:
-- PostgreSQL
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- SQL Server
SELECT * FROM accounts WITH (TABLOCKX);
-- MySQL
LOCK TABLES accounts WRITE;
Explicit locks should be used sparingly—they reduce concurrency and are usually unnecessary when implicit locking is understood.
Growing Phase Duration and Lock Point
The Growing Phase ends when a transaction reaches its lock point—the moment when it has acquired all the locks it will ever need. After this point, the transaction may only perform data operations using its existing locks and eventually release them.
Factors Affecting Growing Phase Duration:
- •Transaction Complexity — More data items accessed = more locks to acquire = longer growing phase
- •Contention Level — High contention means more waiting in lock queues, extending the growing phase
- •Lock Granularity — Coarse-grained locks (table-level) are acquired faster but cause more blocking
- •Application Logic — Time spent processing between lock acquisitions extends real-time duration
- •Query Optimization — Efficient queries discover needed data faster, shortening the lock discovery process
The Lock Point Trade-off:
In basic 2PL, the exact location of the lock point isn't fixed—it depends on when the transaction finishes acquiring locks. However, there's a fundamental tension:
| Earlier Lock Point | Later Lock Point |
|---|---|
| Transaction holds fewer locks | Transaction holds more locks |
| Less concurrency impact | Greater concurrency impact |
| May need more locks later (violates 2PL!) | All locks ready for any operation |
| Not actually possible under 2PL | Standard 2PL behavior |
The key insight: Under strict 2PL implementation, the lock point effectively occurs just before COMMIT. This is because strict 2PL holds all locks until transaction end (as we'll see in the variants module).
In basic 2PL, the lock point is wherever the final lock acquisition occurs. After that point, only releases are permitted.
A common mistake is assuming you can "release and re-acquire" locks during processing. For example: acquire A, process A, release A, acquire B—this VIOLATES 2PL! Once you release any lock, you cannot acquire new ones. Plan your lock acquisition to cover all future needs.
Practical Growing Phase Patterns
Database applications exhibit common patterns during the Growing Phase. Recognizing these patterns helps in designing efficient transactions and troubleshooting lock-related issues.
The Index Lookup Pattern:
Most read operations follow a path: Index → Table Row. Each step acquires locks:
12345678910111213
-- Query: SELECT * FROM employees WHERE email = 'alice@company.com'; -- Step 1: Lock index page containing 'alice@company.com' key-- Lock acquired: S-lock on index page/entry -- Step 2: Follow pointer to table row-- Lock acquired: S-lock on data page/row containing Alice's record -- Growing Phase Locks:-- [Index Entry: alice@...] → S-lock-- [Data Row: Employee #42] → S-lock -- Total locks in growing phase: 2 (index + data)KEEP LEARNING
There’s more to understand.
You’re reading the introduction. Create a free account to read the complete lesson, save notes and keep track of your progress.