Database Management SystemsLock Granularity

Lock Granularity

LevelIntermediate

Duration75 mins

TopicLock Granularity

5 / 5

Intention Locks

The Bridge Between Granularities

We've explored locks at different granularities—row, page, table, and database. But a critical question remains: how do these granularities coexist efficiently?

Consider this problem: Transaction T₁ holds a row-level lock on one row in a table. Transaction T₂ wants to acquire a table-level lock on the same table. Should T₂'s request be granted?

The answer is clearly no—if T₂ gets an exclusive table lock while T₁ holds a row lock, the isolation property is violated. But how does the database know that a row lock exists? Scanning thousands of potential row locks to check compatibility would be prohibitively expensive.

Intention locks solve this problem elegantly. They are a special category of locks acquired on coarse-grained resources (like tables) to signal that a transaction holds or intends to hold locks at finer granularities (like rows). By checking just the table-level intention lock, the database can immediately determine if any finer-grained locks exist.

Intention locks are the glue that makes hierarchical locking work—enabling multiple granularities to coexist with O(1) compatibility checking rather than O(n) scanning of all finer-grained locks.

What You Will Learn

By the end of this page, you will understand: (1) Why intention locks are necessary for hierarchical locking, (2) The three intention lock modes (IS, IX, SIX), (3) The complete lock compatibility matrix, (4) How intention locks are acquired and released, and (5) Practical usage patterns across major database systems.

The Problem Intention Locks Solve

Before understanding solution, we must deeply understand the problem. Let's explore why naive approaches to multi-granularity locking fail.

The Hierarchical Locking Challenge:

In a multi-granularity locking system, resources form a hierarchy:

Database
  └── Schema
        └── Table
              └── Page
                    └── Row

A transaction can lock at any level. A lock at a higher level implicitly covers all lower levels. The challenge: ensuring compatibility between locks at different levels.

Scenario Without Intention Locks:

Time 1: T₁ acquires X-lock on Row 5 in Table Orders
Time 2: T₂ requests S-lock on Table Orders (wants to read entire table)

Question: Should T₂'s request be granted?

Correct Answer: NO
- T₁ is modifying Row 5
- If T₂ reads the whole table, it would see Row 5 in an inconsistent state
- S lock on table conflicts with X lock on any row in the table

Problem: How does the lock manager know Row 5 is locked?

Naive Approach 1: Exhaustive Scan

The lock manager could scan every possible row lock in the table:

For each row in Table Orders:
    If row is locked with conflicting mode:
        Deny table lock request
Grant table lock request

Problem: For a table with 10 million rows, this scan is catastrophically expensive. Even with efficient data structures, checking millions of entries for each table lock request is unacceptable.

Naive Approach 2: Disallow Multi-Granularity

We could simply forbid mixing granularities—if row locking is used, no table locking allowed.

Problem: This eliminates legitimate use cases where table locks are appropriate (bulk operations, DDL, backups) and forces all operations to use the same granularity regardless of workload characteristics.

The Solution: Intention Locks

Intention locks provide a constant-time answer to the question 'are there any finer-grained locks held on this resource?' by requiring transactions to signal their intent before acquiring fine-grained locks.

The Key Insight

Instead of scanning down the hierarchy to find existing locks, intention locks propagate information upward. When you lock a row, you first place an intention marker on the containing table. Anyone wanting to lock the table just checks the intention marker—O(1) instead of O(n).

Intention Lock Modes

Intention locks come in three flavors, each corresponding to the type of fine-grained lock that will be acquired.

Intent Shared (IS):

An IS lock on a resource indicates that the transaction intends to acquire shared (S) locks on one or more finer-grained resources contained within.

T₁ wants to: Read Row 5 in Table Orders (S-lock on row)
T₁ first acquires: IS lock on Table Orders
T₁ then acquires: S lock on Row 5

Meaning of IS on Orders:
'I hold or intend to hold S-locks on some rows in this table'

Intent Exclusive (IX):

An IX lock indicates that the transaction intends to acquire exclusive (X) locks on one or more finer-grained resources.

T₁ wants to: Update Row 5 in Table Orders (X-lock on row)
T₁ first acquires: IX lock on Table Orders  
T₁ then acquires: X lock on Row 5

Meaning of IX on Orders:
'I hold or intend to hold X-locks on some rows in this table'

Shared with Intent Exclusive (SIX):

A SIX lock combines S and IX: the transaction holds a shared lock on the entire resource while also holding exclusive locks on some contained resources.

T₁ wants to: Read entire Table Orders AND update selected rows
T₁ acquires: SIX lock on Table Orders
T₁ then acquires: X locks on specific rows being updated

Meaning of SIX:
'I am reading the whole table (S) and modifying selected rows (IX)'
Equivalent to holding both S and IX simultaneously

Intention Lock Mode Summary
Mode	Abbreviation	Meaning	Typical Use
Intent Shared	IS	Will acquire S-locks at finer granularity	Row-level reads
Intent Exclusive	IX	Will acquire X-locks at finer granularity	Row-level writes
Shared + Intent Exclusive	SIX	Reading whole table, modifying some rows	Hybrid read/write operations

Why SIX Exists:

You might wonder: why not just acquire both S and IX separately? The issue is compatibility:

S and IX are compatible with each other (you can hold both)
But if you hold S, others cannot get IX (and vice versa for some operations)

SIX explicitly combines these modes with specific compatibility rules, allowing the holder to read everything while modifying selected rows, while communicating this intent clearly to other transactions.

Lock Mode Strength

From weakest to strongest: IS < IX < S < SIX < X. Weaker locks are more compatible with other locks. Stronger locks provide more exclusive access but block more operations.

Complete Lock Compatibility Matrix

The lock compatibility matrix defines which lock modes can be held simultaneously on the same resource. This matrix is the foundation for determining whether a lock request should be granted or queued.

Complete 5-Mode Compatibility Matrix:

Complete Lock Compatibility Matrix
Request \ Held	IS	IX	S	SIX	X
IS (Intent Shared)	✓	✓	✓	✓	✗
IX (Intent Exclusive)	✓	✓	✗	✗	✗
S (Shared)	✓	✗	✓	✗	✗
SIX (Shared+Intent Excl)	✓	✗	✗	✗	✗
X (Exclusive)	✗	✗	✗	✗	✗

Understanding the Matrix:

Let's trace through the logic:

Row 1 (Requesting IS):

IS + IS: ✓ — Multiple transactions can read different rows
IS + IX: ✓ — One can read rows while another writes different rows
IS + S: ✓ — Fine-grained reading compatible with table-wide reading
IS + SIX: ✓ — Fine-grained reading compatible with table-wide read/modify
IS + X: ✗ — Cannot read rows if table is exclusively locked

Row 2 (Requesting IX):

IX + IS: ✓ — Writing some rows compatible with reading other rows
IX + IX: ✓ — Multiple transactions can write different rows
IX + S: ✗ — Cannot write rows if table is shared (read-locked)
IX + SIX: ✗ — Cannot write rows if table is SIX (would conflict with the S component)
IX + X: ✗ — Cannot write rows if table is exclusively locked

Row 3 (Requesting S):

S + IS: ✓ — Table-wide read compatible with fine-grained reads
S + IX: ✗ — Cannot read whole table if someone is modifying rows
S + S: ✓ — Multiple readers allowed
S + SIX: ✗ — Cannot read whole table if someone is modifying rows
S + X: ✗ — Cannot read if exclusively locked

Row 4 (Requesting SIX):

SIX + IS: ✓ — SIX allows others to read rows
SIX + IX: ✗ — SIX's S component conflicts with others' IX
SIX + S: ✗ — SIX's IX component conflicts with others' S
SIX + SIX: ✗ — Two transactions can't both SIX (both want to modify)
SIX + X: ✗ — Exclusive blocks everything

Row 5 (Requesting X):

X conflicts with everything—it's exclusive

Converting Mermaid diagram...

Quick Compatibility Check

A shortcut: X conflicts with everything. SIX conflicts with everything except IS. S conflicts with IX, SIX, and X. IX conflicts with S, SIX, and X. IS only conflicts with X. The 'intention' locks (IS, IX) are the most permissive because they only signal intent, not actual data conflict.

The Intention Lock Protocol

For intention locks to work correctly, transactions must follow a specific protocol when acquiring and releasing locks across the hierarchy.

Lock Acquisition Protocol:

When acquiring a lock on a resource, the transaction must first acquire appropriate intention locks on all ancestor resources in the hierarchy.

Rule 1: Acquiring Fine-Grained Locks

To acquire an S-lock on a row:
1. Acquire IS-lock on the database (if hierarchical)
2. Acquire IS-lock on the schema (if applicable)
3. Acquire IS-lock on the table
4. Acquire IS-lock on the page (if page-level intentions used)
5. Acquire S-lock on the row

To acquire an X-lock on a row:
1. Acquire IX-lock on the database
2. Acquire IX-lock on the schema
3. Acquire IX-lock on the table
4. Acquire IX-lock on the page
5. Acquire X-lock on the row

Rule 2: Acquiring Coarse-Grained Locks

To acquire an S-lock on a table:
- No intention locks needed (just acquire S on table directly)
- This blocks all IX holders (no row modifications)

To acquire an X-lock on a table:
- No intention locks needed (just acquire X on table directly)  
- This blocks everything

Lock Release Protocol:

Locks must be released in the reverse order—from fine to coarse.

To release locks after row update:
1. Release X-lock on the row
2. Release IX-lock on the page (if no other row locks held on page)
3. Release IX-lock on the table (if no other page/row locks held)
4. Release IX-lock on the schema (if no other table locks held)
5. Release IX-lock on the database (if no other locks held)

Note on Lock Holding:

In practice, a transaction may hold multiple intention locks at the same level. For example, if T₁ locks Row 5 and Row 10 in the same table:

Locking Row 5:
- Acquire IX on Table (first row lock in this table)
- Acquire X on Row 5

Locking Row 10 (same transaction):
- IX on Table already held (no new acquisition needed)
- Acquire X on Row 10

Releasing Row 5:
- Release X on Row 5
- Keep IX on Table (still holding Row 10)

Releasing Row 10:
- Release X on Row 10
- Release IX on Table (no more row locks in this table)

Reference Counting

Many database systems use reference counting for intention locks. Each fine-grained lock acquisition increments the reference count on the parent intention lock. Release decrements. The intention lock is actually released only when the count reaches zero. This optimization avoids repeatedly acquiring and releasing the same intention lock.

Practical Example Walkthrough

Let's trace through a complete example showing how intention locks enable safe multi-granularity locking.

Scenario:

Three transactions operate on the orders table simultaneously:

T₁: Updates order 100 (row-level X-lock)
T₂: Reads order 200 (row-level S-lock)
T₃: Wants to read the entire orders table (table-level S-lock)

Transaction T₁: UPDATE order 100

Transaction T₁ Lock Sequence
1
2
3
4
5
6
7
8
9
10
11
12
13
-- T₁: UPDATE orders SET total = 500 WHERE order_id = 100;
 
-- Step 1: Request IX lock on table 'orders'
-- Lock table state: orders -> IX (T₁)
-- Result: GRANTED (table was unlocked)
 
-- Step 2: Request X lock on row order_id=100
-- Lock table state: 
--   orders -> IX (T₁)
--   orders.row[100] -> X (T₁)
-- Result: GRANTED (row was unlocked)
 
-- T₁ now holds: IX on orders, X on row 100

Transaction T₂: SELECT order 200

Transaction T₂ Lock Sequence
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- T₂: SELECT * FROM orders WHERE order_id = 200;
 
-- Step 1: Request IS lock on table 'orders'
-- Current state: orders -> IX (T₁)
-- Check compatibility: IS compatible with IX? YES (from matrix)
-- Lock table state: orders -> IX (T₁), IS (T₂)  [both held simultaneously]
-- Result: GRANTED
 
-- Step 2: Request S lock on row order_id=200
-- Current state: orders.row[200] -> (unlocked)
-- Result: GRANTED
-- Lock table state:
--   orders -> IX (T₁), IS (T₂)
--   orders.row[100] -> X (T₁)
--   orders.row[200] -> S (T₂)
 
-- T₂ now holds: IS on orders, S on row 200
-- Note: T₁ and T₂ operate concurrently on different rows

Transaction T₃: SELECT entire table

Transaction T₃ Lock Sequence
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- T₃: SELECT * FROM orders; (wants to read all rows)
 
-- Step 1: Request S lock on table 'orders' (table-level read)
-- Current state: orders -> IX (T₁), IS (T₂)
-- Check compatibility: S compatible with IX? NO (from matrix)
-- Result: BLOCKED - T₃ must wait for T₁ to release IX
 
-- Why is T₃ blocked?
-- If T₃'s S lock were granted, T₃ would read the entire table
-- But T₁ is modifying row 100 (holds X lock)
-- T₃ would read inconsistent data (dirty read of row 100)
-- The IX lock on 'orders' signals that row-level X locks exist
-- Therefore, table-level S is incompatible
 
-- T₃ waits in lock queue for 'orders'...

Resolution Sequence:

Lock Resolution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- T₁ commits (releases locks in reverse order)
-- Release X on row 100
-- Release IX on table orders (reference count: 0)
-- Lock table state:
--   orders -> IS (T₂)          [IX removed]
--   orders.row[200] -> S (T₂)  [row 100 lock removed]
 
-- T₃'s S lock request is re-evaluated
-- Current state: orders -> IS (T₂)
-- Check compatibility: S compatible with IS? YES
-- Result: GRANTED
-- Lock table state:
--   orders -> IS (T₂), S (T₃)
--   orders.row[200] -> S (T₂)
 
-- T₃ can now read the entire table
-- Note: T₂'s row 200 read is consistent with T₃'s table read

Protocol Success

The intention lock protocol prevented T₃ from reading inconsistent data without scanning every row lock. By checking only the table-level IX lock, the lock manager knew that exclusive row locks existed and correctly blocked the table-level S request. This is O(1), not O(n).

Intention Locks in Major Databases

Understanding how specific database systems implement intention locks helps in monitoring and troubleshooting lock-related issues.

SQL Server Intention Locks:

SQL Server implements the full IS/IX/SIX intention lock protocol and exposes them in system views:

SQL Server Intention Locks
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- View intention locks on objects
SELECT 
    resource_type,
    resource_associated_entity_id,
    OBJECT_NAME(resource_associated_entity_id) as object_name,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE request_mode IN ('IS', 'IX', 'SIX')
  AND resource_type = 'OBJECT';
 
-- Typical lock escalation showing intention locks:
-- When you UPDATE a row:
-- 1. IX lock on TABLE
-- 2. IX lock on PAGE (depending on lock escalation settings)
-- 3. X lock on KEY (row/index key)
 
-- Example query and its locks:
UPDATE orders SET total = 500 WHERE order_id = 100;
 
-- Locks acquired (simplified):
-- OBJECT: orders -> IX
-- PAGE: page containing row 100 -> IX
-- KEY: order_id=100 -> X
 
-- View the full lock chain
SELECT 
    tl.resource_type,
    tl.request_mode,
    tl.request_status,
    CASE 
        WHEN tl.resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
        ELSE CAST(tl.resource_associated_entity_id AS VARCHAR)
    END AS resource_name
FROM sys.dm_tran_locks tl
WHERE tl.request_session_id = @@SPID;

PostgreSQL Lock Representation:

PostgreSQL uses different terminology but implements similar hierarchical locking. Its lock modes include several intention-like behaviors:

PostgreSQL Lock Hierarchy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- PostgreSQL lock modes (roughly mapped to traditional terms):
-- ACCESS SHARE      ≈ IS (acquired by SELECT)
-- ROW SHARE         ≈ IS (acquired by SELECT FOR UPDATE target tables)
-- ROW EXCLUSIVE     ≈ IX (acquired by INSERT, UPDATE, DELETE)
-- SHARE UPDATE EXCL ≈ Special (VACUUM, CREATE INDEX CONCURRENTLY)
-- SHARE             ≈ S (CREATE INDEX)
-- SHARE ROW EXCL    ≈ SIX-like (CREATE TRIGGER, ALTER TABLE variants)
-- EXCLUSIVE         ≈ X-like (blocks ROW SHARE/EXCLUSIVE)
-- ACCESS EXCLUSIVE  ≈ X (DROP TABLE, ALTER TABLE, VACUUM FULL)
 
-- View locks including intention-equivalent modes
SELECT 
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    pid
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY relation, mode;
 
-- PostgreSQL handles row-level differently (MVCC + row-level locks for FOR UPDATE)
-- The table-level lock (ROW EXCLUSIVE) functions as intention lock
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;
-- Acquires:
--   ROW SHARE on table (intention-like)
--   tuple-level lock on specific row

MySQL/InnoDB Intention Locks:

InnoDB explicitly uses intention locks and exposes them through performance_schema:

MySQL InnoDB Intention Locks
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- InnoDB uses IS, IX at table level before row locks
-- View InnoDB locks (MySQL 8.0+)
SELECT 
    ENGINE_LOCK_ID,
    ENGINE_TRANSACTION_ID,
    OBJECT_NAME,
    LOCK_TYPE,   -- TABLE or RECORD
    LOCK_MODE,   -- IS, IX, S, X, or specific record lock modes
    LOCK_STATUS, -- GRANTED or WAITING
    LOCK_DATA
FROM performance_schema.data_locks
WHERE LOCK_TYPE = 'TABLE';
 
-- Example: SELECT FOR UPDATE acquires:
-- 1. IX lock on table (intention exclusive)
-- 2. X lock on specific record(s)
 
START TRANSACTION;
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;
 
-- Check locks held:
SELECT * FROM performance_schema.data_locks;
-- Results show:
-- TABLE lock with LOCK_MODE = 'IX'
-- RECORD lock with LOCK_MODE = 'X,REC_NOT_GAP'
 
ROLLBACK;
 
-- InnoDB lock modes for records include:
-- S - Shared (next-key lock by default)
-- X - Exclusive (next-key lock by default)  
-- S,REC_NOT_GAP - Shared record-only lock
-- X,REC_NOT_GAP - Exclusive record-only lock
-- S,GAP - Shared gap lock
-- X,GAP - Exclusive gap lock

Monitoring Intention Locks

Excessive intention locks are rarely the direct cause of contention—they're lightweight markers. If you see many IX locks on a table, investigate the underlying row locks. If intention locks are blocked, the blocker is likely a table-level S, X, or SIX lock.

Lock Escalation and Intention Locks

Lock escalation—a database's automatic promotion from fine-grained to coarse-grained locks—interacts significantly with intention locks.

How Escalation Works:

When a transaction accumulates too many row/page locks, the database may escalate to a table lock to reduce memory overhead:

Before Escalation:
- Table: orders -> IX (T₁)
- Page 1: IX (T₁)
- Row 1: X (T₁)
- Row 2: X (T₁)
- Row 3: X (T₁)
... (thousands of row locks)

After Escalation:
- Table: orders -> X (T₁)  [All row/page locks replaced with single table lock]

Intent Lock Transformation:

During escalation:

The intention lock (IX) on the table is converted to an actual lock (X)
All finer-grained locks are released (no longer needed—table lock covers everything)
Other transactions must now wait for the table lock instead of only conflicting rows

SQL Server Lock Escalation Monitoring
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Create extended event session to monitor lock escalation
CREATE EVENT SESSION [LockEscalation] ON SERVER
ADD EVENT sqlserver.lock_escalation (
    ACTION(
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.database_name
    )
    WHERE ([escalation_cause] <= 2)  -- 0=statement, 1=scope, 2=table
)
ADD TARGET package0.event_file(SET filename=N'LockEscalation.xel')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
 
-- Start monitoring
ALTER EVENT SESSION [LockEscalation] ON SERVER STATE = START;
 
-- View escalation events
SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') as event_time,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as sql_text,
    event_data.value('(event/data[@name="escalation_cause"]/value)[1]', 'int') as escalation_cause,
    event_data.value('(event/data[@name="mode"]/value)[1]', 'nvarchar(50)') as lock_mode
FROM (
    SELECT CAST(event_data AS XML) as event_data
    FROM sys.fn_xe_file_target_read_file('LockEscalation*.xel', NULL, NULL, NULL)
) AS events;

Preventing Unwanted Escalation:

To preserve intention-lock-based concurrency, you may want to prevent escalation:

Preventing Lock Escalation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- SQL Server: Disable escalation for a table
ALTER TABLE orders SET (LOCK_ESCALATION = DISABLE);
 
-- SQL Server: Escalate to partition level instead of table
ALTER TABLE orders SET (LOCK_ESCALATION = AUTO);
 
-- Process in batches to stay below escalation threshold
DECLARE @BatchSize INT = 4000;  -- Below default ~5000 threshold
 
WHILE EXISTS (SELECT 1 FROM orders WHERE status = 'pending' AND processed = 0)
BEGIN
    UPDATE TOP (@BatchSize) orders 
    SET processed = 1 
    WHERE status = 'pending' AND processed = 0;
    
    -- Commit to release locks, then continue
    IF @@TRANCOUNT > 0 COMMIT;
    BEGIN TRANSACTION;
END
COMMIT;

When to Allow vs Prevent Escalation

•Allow Escalation: Batch jobs processing most of the table anyway; exclusive operations where blocking is acceptable.
•Prevent Escalation: High-concurrency OLTP tables; tables with hot rows that should remain independently lockable.
•Partition-Level Escalation: Large partitioned tables where you want to escalate within partition boundaries only.

The Escalation Trap

Disabling escalation sounds appealing but has trade-offs: your lock manager memory usage can grow unbounded. On memory-constrained systems, this can cause lock manager exhaustion and system-wide failures. Monitor lock counts if you disable escalation.

Summary

Intention locks are the critical mechanism that enables efficient multi-granularity locking. They provide constant-time compatibility checking, allowing fine-grained and coarse-grained locks to coexist safely. Let's consolidate the key concepts:

Key Takeaways

•Purpose: Intention locks signal that a transaction holds or will hold locks at finer granularity, enabling O(1) compatibility checking.
•Three Modes: IS (intent to read rows), IX (intent to write rows), and SIX (reading entire resource while writing some parts).
•Compatibility Matrix: IS is most compatible (only conflicts with X); X conflicts with everything; SIX and IX conflict with S.
•Protocol: Acquire intention locks from coarse to fine; release from fine to coarse. Intention locks must be acquired before fine-grained locks.
•Implementation: Major databases (SQL Server, PostgreSQL, MySQL/InnoDB) all implement intention locks, though terminology varies.
•Lock Escalation: When too many fine-grained locks are held, intention locks may be replaced with actual table locks, reducing concurrency.
•Monitoring: Use system views (sys.dm_tran_locks, pg_locks, performance_schema.data_locks) to observe intention lock activity.

Module Complete:

With this page, we've completed the Lock Granularity module. You now understand the full spectrum of lock granularities—from row to page to table to database—and the intention lock mechanism that enables them to coexist. This knowledge is fundamental for understanding database concurrency, diagnosing performance issues, and designing applications that scale.

The next module will explore Deadlock Handling—what happens when transactions wait for each other in a cycle, and how databases detect and resolve these dangerous situations.

Module Complete

You now understand intention locks—the elegant mechanism that enables hierarchical locking to work efficiently. Intention locks transform the O(n) problem of checking all fine-grained locks into an O(1) check at the parent level, enabling databases to support multiple lock granularities simultaneously. This completes the Lock Granularity module.

5 / 5

Loading learning content...

Database Management SystemsLock Granularity

Lock Granularity

LevelIntermediate

Duration75 mins

TopicLock Granularity

5 / 5

Intention Locks

The Bridge Between Granularities

We've explored locks at different granularities—row, page, table, and database. But a critical question remains: how do these granularities coexist efficiently?

Consider this problem: Transaction T₁ holds a row-level lock on one row in a table. Transaction T₂ wants to acquire a table-level lock on the same table. Should T₂'s request be granted?

What You Will Learn

The Problem Intention Locks Solve

Before understanding solution, we must deeply understand the problem. Let's explore why naive approaches to multi-granularity locking fail.

The Hierarchical Locking Challenge:

In a multi-granularity locking system, resources form a hierarchy:

Database
  └── Schema
        └── Table
              └── Page
                    └── Row

A transaction can lock at any level. A lock at a higher level implicitly covers all lower levels. The challenge: ensuring compatibility between locks at different levels.

Scenario Without Intention Locks:

Time 1: T₁ acquires X-lock on Row 5 in Table Orders
Time 2: T₂ requests S-lock on Table Orders (wants to read entire table)

Question: Should T₂'s request be granted?

Correct Answer: NO
- T₁ is modifying Row 5
- If T₂ reads the whole table, it would see Row 5 in an inconsistent state
- S lock on table conflicts with X lock on any row in the table

Problem: How does the lock manager know Row 5 is locked?

Naive Approach 1: Exhaustive Scan

The lock manager could scan every possible row lock in the table:

For each row in Table Orders:
    If row is locked with conflicting mode:
        Deny table lock request
Grant table lock request

Problem: For a table with 10 million rows, this scan is catastrophically expensive. Even with efficient data structures, checking millions of entries for each table lock request is unacceptable.

Naive Approach 2: Disallow Multi-Granularity

We could simply forbid mixing granularities—if row locking is used, no table locking allowed.

The Solution: Intention Locks

The Key Insight

Intention Lock Modes

Intention locks come in three flavors, each corresponding to the type of fine-grained lock that will be acquired.

Intent Shared (IS):

An IS lock on a resource indicates that the transaction intends to acquire shared (S) locks on one or more finer-grained resources contained within.

T₁ wants to: Read Row 5 in Table Orders (S-lock on row)
T₁ first acquires: IS lock on Table Orders
T₁ then acquires: S lock on Row 5

Meaning of IS on Orders:
'I hold or intend to hold S-locks on some rows in this table'

Intent Exclusive (IX):

An IX lock indicates that the transaction intends to acquire exclusive (X) locks on one or more finer-grained resources.

T₁ wants to: Update Row 5 in Table Orders (X-lock on row)
T₁ first acquires: IX lock on Table Orders  
T₁ then acquires: X lock on Row 5

Meaning of IX on Orders:
'I hold or intend to hold X-locks on some rows in this table'

Shared with Intent Exclusive (SIX):

A SIX lock combines S and IX: the transaction holds a shared lock on the entire resource while also holding exclusive locks on some contained resources.

T₁ wants to: Read entire Table Orders AND update selected rows
T₁ acquires: SIX lock on Table Orders
T₁ then acquires: X locks on specific rows being updated

Meaning of SIX:
'I am reading the whole table (S) and modifying selected rows (IX)'
Equivalent to holding both S and IX simultaneously

Intention Lock Mode Summary
Mode	Abbreviation	Meaning	Typical Use
Intent Shared	IS	Will acquire S-locks at finer granularity	Row-level reads
Intent Exclusive	IX	Will acquire X-locks at finer granularity	Row-level writes
Shared + Intent Exclusive	SIX	Reading whole table, modifying some rows	Hybrid read/write operations

Why SIX Exists:

You might wonder: why not just acquire both S and IX separately? The issue is compatibility:

S and IX are compatible with each other (you can hold both)
But if you hold S, others cannot get IX (and vice versa for some operations)

Lock Mode Strength

From weakest to strongest: IS < IX < S < SIX < X. Weaker locks are more compatible with other locks. Stronger locks provide more exclusive access but block more operations.

Complete Lock Compatibility Matrix

Complete 5-Mode Compatibility Matrix:

Complete Lock Compatibility Matrix
Request \ Held	IS	IX	S	SIX	X
IS (Intent Shared)	✓	✓	✓	✓	✗
IX (Intent Exclusive)	✓	✓	✗	✗	✗
S (Shared)	✓	✗	✓	✗	✗
SIX (Shared+Intent Excl)	✓	✗	✗	✗	✗
X (Exclusive)	✗	✗	✗	✗	✗

Understanding the Matrix:

Let's trace through the logic:

Row 1 (Requesting IS):

IS + IS: ✓ — Multiple transactions can read different rows
IS + IX: ✓ — One can read rows while another writes different rows
IS + S: ✓ — Fine-grained reading compatible with table-wide reading
IS + SIX: ✓ — Fine-grained reading compatible with table-wide read/modify
IS + X: ✗ — Cannot read rows if table is exclusively locked

Row 2 (Requesting IX):

IX + IS: ✓ — Writing some rows compatible with reading other rows
IX + IX: ✓ — Multiple transactions can write different rows
IX + S: ✗ — Cannot write rows if table is shared (read-locked)
IX + SIX: ✗ — Cannot write rows if table is SIX (would conflict with the S component)
IX + X: ✗ — Cannot write rows if table is exclusively locked

Row 3 (Requesting S):

S + IS: ✓ — Table-wide read compatible with fine-grained reads
S + IX: ✗ — Cannot read whole table if someone is modifying rows
S + S: ✓ — Multiple readers allowed
S + SIX: ✗ — Cannot read whole table if someone is modifying rows
S + X: ✗ — Cannot read if exclusively locked

Row 4 (Requesting SIX):

SIX + IS: ✓ — SIX allows others to read rows
SIX + IX: ✗ — SIX's S component conflicts with others' IX
SIX + S: ✗ — SIX's IX component conflicts with others' S
SIX + SIX: ✗ — Two transactions can't both SIX (both want to modify)
SIX + X: ✗ — Exclusive blocks everything

Row 5 (Requesting X):

X conflicts with everything—it's exclusive

Converting Mermaid diagram...

Quick Compatibility Check

The Intention Lock Protocol

For intention locks to work correctly, transactions must follow a specific protocol when acquiring and releasing locks across the hierarchy.

Lock Acquisition Protocol:

When acquiring a lock on a resource, the transaction must first acquire appropriate intention locks on all ancestor resources in the hierarchy.

Rule 1: Acquiring Fine-Grained Locks

To acquire an S-lock on a row:
1. Acquire IS-lock on the database (if hierarchical)
2. Acquire IS-lock on the schema (if applicable)
3. Acquire IS-lock on the table
4. Acquire IS-lock on the page (if page-level intentions used)
5. Acquire S-lock on the row

To acquire an X-lock on a row:
1. Acquire IX-lock on the database
2. Acquire IX-lock on the schema
3. Acquire IX-lock on the table
4. Acquire IX-lock on the page
5. Acquire X-lock on the row

Rule 2: Acquiring Coarse-Grained Locks

To acquire an S-lock on a table:
- No intention locks needed (just acquire S on table directly)
- This blocks all IX holders (no row modifications)

To acquire an X-lock on a table:
- No intention locks needed (just acquire X on table directly)  
- This blocks everything

Lock Release Protocol:

Locks must be released in the reverse order—from fine to coarse.

To release locks after row update:
1. Release X-lock on the row
2. Release IX-lock on the page (if no other row locks held on page)
3. Release IX-lock on the table (if no other page/row locks held)
4. Release IX-lock on the schema (if no other table locks held)
5. Release IX-lock on the database (if no other locks held)

Note on Lock Holding:

In practice, a transaction may hold multiple intention locks at the same level. For example, if T₁ locks Row 5 and Row 10 in the same table:

Locking Row 5:
- Acquire IX on Table (first row lock in this table)
- Acquire X on Row 5

Locking Row 10 (same transaction):
- IX on Table already held (no new acquisition needed)
- Acquire X on Row 10

Releasing Row 5:
- Release X on Row 5
- Keep IX on Table (still holding Row 10)

Releasing Row 10:
- Release X on Row 10
- Release IX on Table (no more row locks in this table)

Reference Counting

Practical Example Walkthrough

Let's trace through a complete example showing how intention locks enable safe multi-granularity locking.

Scenario:

Three transactions operate on the orders table simultaneously:

T₁: Updates order 100 (row-level X-lock)
T₂: Reads order 200 (row-level S-lock)
T₃: Wants to read the entire orders table (table-level S-lock)

Transaction T₁: UPDATE order 100

Transaction T₁ Lock Sequence
1
2
3
4
5
6
7
8
9
10
11
12
13
-- T₁: UPDATE orders SET total = 500 WHERE order_id = 100;
 
-- Step 1: Request IX lock on table 'orders'
-- Lock table state: orders -> IX (T₁)
-- Result: GRANTED (table was unlocked)
 
-- Step 2: Request X lock on row order_id=100
-- Lock table state: 
--   orders -> IX (T₁)
--   orders.row[100] -> X (T₁)
-- Result: GRANTED (row was unlocked)
 
-- T₁ now holds: IX on orders, X on row 100

Transaction T₂: SELECT order 200

Transaction T₂ Lock Sequence
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- T₂: SELECT * FROM orders WHERE order_id = 200;
 
-- Step 1: Request IS lock on table 'orders'
-- Current state: orders -> IX (T₁)
-- Check compatibility: IS compatible with IX? YES (from matrix)
-- Lock table state: orders -> IX (T₁), IS (T₂)  [both held simultaneously]
-- Result: GRANTED
 
-- Step 2: Request S lock on row order_id=200
-- Current state: orders.row[200] -> (unlocked)
-- Result: GRANTED
-- Lock table state:
--   orders -> IX (T₁), IS (T₂)
--   orders.row[100] -> X (T₁)
--   orders.row[200] -> S (T₂)
 
-- T₂ now holds: IS on orders, S on row 200
-- Note: T₁ and T₂ operate concurrently on different rows

Transaction T₃: SELECT entire table

Transaction T₃ Lock Sequence
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- T₃: SELECT * FROM orders; (wants to read all rows)
 
-- Step 1: Request S lock on table 'orders' (table-level read)
-- Current state: orders -> IX (T₁), IS (T₂)
-- Check compatibility: S compatible with IX? NO (from matrix)
-- Result: BLOCKED - T₃ must wait for T₁ to release IX
 
-- Why is T₃ blocked?
-- If T₃'s S lock were granted, T₃ would read the entire table
-- But T₁ is modifying row 100 (holds X lock)
-- T₃ would read inconsistent data (dirty read of row 100)
-- The IX lock on 'orders' signals that row-level X locks exist
-- Therefore, table-level S is incompatible
 
-- T₃ waits in lock queue for 'orders'...

Resolution Sequence:

Lock Resolution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- T₁ commits (releases locks in reverse order)
-- Release X on row 100
-- Release IX on table orders (reference count: 0)
-- Lock table state:
--   orders -> IS (T₂)          [IX removed]
--   orders.row[200] -> S (T₂)  [row 100 lock removed]
 
-- T₃'s S lock request is re-evaluated
-- Current state: orders -> IS (T₂)
-- Check compatibility: S compatible with IS? YES
-- Result: GRANTED
-- Lock table state:
--   orders -> IS (T₂), S (T₃)
--   orders.row[200] -> S (T₂)
 
-- T₃ can now read the entire table
-- Note: T₂'s row 200 read is consistent with T₃'s table read

Protocol Success

Intention Locks in Major Databases

Understanding how specific database systems implement intention locks helps in monitoring and troubleshooting lock-related issues.

SQL Server Intention Locks:

SQL Server implements the full IS/IX/SIX intention lock protocol and exposes them in system views:

SQL Server Intention Locks
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- View intention locks on objects
SELECT 
    resource_type,
    resource_associated_entity_id,
    OBJECT_NAME(resource_associated_entity_id) as object_name,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE request_mode IN ('IS', 'IX', 'SIX')
  AND resource_type = 'OBJECT';
 
-- Typical lock escalation showing intention locks:
-- When you UPDATE a row:
-- 1. IX lock on TABLE
-- 2. IX lock on PAGE (depending on lock escalation settings)
-- 3. X lock on KEY (row/index key)
 
-- Example query and its locks:
UPDATE orders SET total = 500 WHERE order_id = 100;
 
-- Locks acquired (simplified):
-- OBJECT: orders -> IX
-- PAGE: page containing row 100 -> IX
-- KEY: order_id=100 -> X
 
-- View the full lock chain
SELECT 
    tl.resource_type,
    tl.request_mode,
    tl.request_status,
    CASE 
        WHEN tl.resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
        ELSE CAST(tl.resource_associated_entity_id AS VARCHAR)
    END AS resource_name
FROM sys.dm_tran_locks tl
WHERE tl.request_session_id = @@SPID;

PostgreSQL Lock Representation:

PostgreSQL uses different terminology but implements similar hierarchical locking. Its lock modes include several intention-like behaviors:

PostgreSQL Lock Hierarchy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- PostgreSQL lock modes (roughly mapped to traditional terms):
-- ACCESS SHARE      ≈ IS (acquired by SELECT)
-- ROW SHARE         ≈ IS (acquired by SELECT FOR UPDATE target tables)
-- ROW EXCLUSIVE     ≈ IX (acquired by INSERT, UPDATE, DELETE)
-- SHARE UPDATE EXCL ≈ Special (VACUUM, CREATE INDEX CONCURRENTLY)
-- SHARE             ≈ S (CREATE INDEX)
-- SHARE ROW EXCL    ≈ SIX-like (CREATE TRIGGER, ALTER TABLE variants)
-- EXCLUSIVE         ≈ X-like (blocks ROW SHARE/EXCLUSIVE)
-- ACCESS EXCLUSIVE  ≈ X (DROP TABLE, ALTER TABLE, VACUUM FULL)
 
-- View locks including intention-equivalent modes
SELECT 
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    pid
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY relation, mode;
 
-- PostgreSQL handles row-level differently (MVCC + row-level locks for FOR UPDATE)
-- The table-level lock (ROW EXCLUSIVE) functions as intention lock
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;
-- Acquires:
--   ROW SHARE on table (intention-like)
--   tuple-level lock on specific row

MySQL/InnoDB Intention Locks:

InnoDB explicitly uses intention locks and exposes them through performance_schema:

MySQL InnoDB Intention Locks
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- InnoDB uses IS, IX at table level before row locks
-- View InnoDB locks (MySQL 8.0+)
SELECT 
    ENGINE_LOCK_ID,
    ENGINE_TRANSACTION_ID,
    OBJECT_NAME,
    LOCK_TYPE,   -- TABLE or RECORD
    LOCK_MODE,   -- IS, IX, S, X, or specific record lock modes
    LOCK_STATUS, -- GRANTED or WAITING
    LOCK_DATA
FROM performance_schema.data_locks
WHERE LOCK_TYPE = 'TABLE';
 
-- Example: SELECT FOR UPDATE acquires:
-- 1. IX lock on table (intention exclusive)
-- 2. X lock on specific record(s)
 
START TRANSACTION;
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;
 
-- Check locks held:
SELECT * FROM performance_schema.data_locks;
-- Results show:
-- TABLE lock with LOCK_MODE = 'IX'
-- RECORD lock with LOCK_MODE = 'X,REC_NOT_GAP'
 
ROLLBACK;
 
-- InnoDB lock modes for records include:
-- S - Shared (next-key lock by default)
-- X - Exclusive (next-key lock by default)  
-- S,REC_NOT_GAP - Shared record-only lock
-- X,REC_NOT_GAP - Exclusive record-only lock
-- S,GAP - Shared gap lock
-- X,GAP - Exclusive gap lock

Monitoring Intention Locks

Lock Escalation and Intention Locks

Lock escalation—a database's automatic promotion from fine-grained to coarse-grained locks—interacts significantly with intention locks.

How Escalation Works:

When a transaction accumulates too many row/page locks, the database may escalate to a table lock to reduce memory overhead:

Before Escalation:
- Table: orders -> IX (T₁)
- Page 1: IX (T₁)
- Row 1: X (T₁)
- Row 2: X (T₁)
- Row 3: X (T₁)
... (thousands of row locks)

After Escalation:
- Table: orders -> X (T₁)  [All row/page locks replaced with single table lock]

Intent Lock Transformation:

During escalation:

The intention lock (IX) on the table is converted to an actual lock (X)
All finer-grained locks are released (no longer needed—table lock covers everything)
Other transactions must now wait for the table lock instead of only conflicting rows

SQL Server Lock Escalation Monitoring
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Create extended event session to monitor lock escalation
CREATE EVENT SESSION [LockEscalation] ON SERVER
ADD EVENT sqlserver.lock_escalation (
    ACTION(
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.database_name
    )
    WHERE ([escalation_cause] <= 2)  -- 0=statement, 1=scope, 2=table
)
ADD TARGET package0.event_file(SET filename=N'LockEscalation.xel')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
 
-- Start monitoring
ALTER EVENT SESSION [LockEscalation] ON SERVER STATE = START;
 
-- View escalation events
SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') as event_time,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as sql_text,
    event_data.value('(event/data[@name="escalation_cause"]/value)[1]', 'int') as escalation_cause,
    event_data.value('(event/data[@name="mode"]/value)[1]', 'nvarchar(50)') as lock_mode
FROM (
    SELECT CAST(event_data AS XML) as event_data
    FROM sys.fn_xe_file_target_read_file('LockEscalation*.xel', NULL, NULL, NULL)
) AS events;

Preventing Unwanted Escalation:

To preserve intention-lock-based concurrency, you may want to prevent escalation:

Preventing Lock Escalation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- SQL Server: Disable escalation for a table
ALTER TABLE orders SET (LOCK_ESCALATION = DISABLE);
 
-- SQL Server: Escalate to partition level instead of table
ALTER TABLE orders SET (LOCK_ESCALATION = AUTO);
 
-- Process in batches to stay below escalation threshold
DECLARE @BatchSize INT = 4000;  -- Below default ~5000 threshold
 
WHILE EXISTS (SELECT 1 FROM orders WHERE status = 'pending' AND processed = 0)
BEGIN
    UPDATE TOP (@BatchSize) orders 
    SET processed = 1 
    WHERE status = 'pending' AND processed = 0;
    
    -- Commit to release locks, then continue
    IF @@TRANCOUNT > 0 COMMIT;
    BEGIN TRANSACTION;
END
COMMIT;

When to Allow vs Prevent Escalation

•Allow Escalation: Batch jobs processing most of the table anyway; exclusive operations where blocking is acceptable.
•Prevent Escalation: High-concurrency OLTP tables; tables with hot rows that should remain independently lockable.
•Partition-Level Escalation: Large partitioned tables where you want to escalate within partition boundaries only.

The Escalation Trap

Summary

Key Takeaways

•Purpose: Intention locks signal that a transaction holds or will hold locks at finer granularity, enabling O(1) compatibility checking.
•Three Modes: IS (intent to read rows), IX (intent to write rows), and SIX (reading entire resource while writing some parts).
•Compatibility Matrix: IS is most compatible (only conflicts with X); X conflicts with everything; SIX and IX conflict with S.
•Protocol: Acquire intention locks from coarse to fine; release from fine to coarse. Intention locks must be acquired before fine-grained locks.
•Implementation: Major databases (SQL Server, PostgreSQL, MySQL/InnoDB) all implement intention locks, though terminology varies.
•Lock Escalation: When too many fine-grained locks are held, intention locks may be replaced with actual table locks, reducing concurrency.
•Monitoring: Use system views (sys.dm_tran_locks, pg_locks, performance_schema.data_locks) to observe intention lock activity.

Module Complete:

The next module will explore Deadlock Handling—what happens when transactions wait for each other in a cycle, and how databases detect and resolve these dangerous situations.

Module Complete

5 / 5