MultiXact Contention: Source Code Analysis

The Mystery

Observation:

INSERT-only (400 connections): Massive MultiXact SLRU activity (75K hits/sec), but 0% LWLock wait events
INSERT+UPDATE (500 connections): Similar SLRU activity, but 7% MultiXact LWLock wait events

Question: Why does adding UPDATE cause MultiXact LWLock contention to become visible?

Source Code Evidence

1. When MultiXacts Are Created

File: src/backend/access/heap/heapam.c:5476-5556

else if (TransactionIdIsInProgress(xmax))
{
    /*
     * If the XMAX is a valid, in-progress TransactionId, then we need to
     * create a new MultiXactId that includes both the old locker or
     * updater and our own TransactionId.
     */
    MultiXactStatus new_status;
    MultiXactStatus old_status;
    
    // Determine old lock type
    if (HEAP_XMAX_IS_LOCKED_ONLY(old_infomask))
    {
        if (HEAP_XMAX_IS_KEYSHR_LOCKED(old_infomask))
            old_status = MultiXactStatusForKeyShare;
        // ... other lock types
    }
    
    // Create new MultiXact with both transactions
    new_status = get_mxact_status_for_lock(mode, is_update);
    new_xmax = MultiXactIdCreate(xmax, old_status,
                                 add_to_xmax, new_status);
}

Key insight: MultiXact is created when a transaction encounters an in-progress xmax on a tuple.

2. MultiXact Expansion Requires SLRU Read

File: src/backend/access/transam/multixact.c:478-560

MultiXactId
MultiXactIdExpand(MultiXactId multi, TransactionId xid, MultiXactStatus status)
{
    MultiXactMember *members;
    int nmembers;
    
    // MUST READ existing MultiXact members from SLRU
    nmembers = GetMultiXactIdMembers(multi, &members, false, false);
    
    // Check if already a member (optimization)
    for (i = 0; i < nmembers; i++)
    {
        if (TransactionIdEquals(members[i].xid, xid) &&
            (members[i].status == status))
        {
            // Already member, return existing MultiXact
            return multi;
        }
    }
    
    // Filter out dead members
    for (i = 0, j = 0; i < nmembers; i++)
    {
        if (TransactionIdIsInProgress(members[i].xid) ||
            (ISUPDATE_from_mxstatus(members[i].status) &&
             TransactionIdDidCommit(members[i].xid)))
        {
            newMembers[j++] = members[i];
        }
    }
    
    // Add new member and create new MultiXact
    newMembers[j].xid = xid;
    newMembers[j++].status = status;
    newMulti = MultiXactIdCreateFromMembers(j, newMembers);
    
    return newMulti;
}

Key insight: Every MultiXact expansion requires:

Reading existing members from SLRU (GetMultiXactIdMembers)
Filtering dead members
Creating new MultiXact with updated member list

3. GetMultiXactIdMembers Accesses SLRU

File: src/backend/access/transam/multixact.c:1290-1400

GetMultiXactIdMembers(MultiXactId multi, MultiXactMember **members, ...)
{
    // Check local cache first
    length = mXactCacheGetById(multi, members);
    if (length >= 0)
        return length;  // Cache hit - no SLRU access
    
    // Cache miss - must read from SLRU
    // Acquire LWLock on MultiXactGenLock
    LWLockAcquire(MultiXactGenLock, LW_SHARED);
    oldestMXact = MultiXactState->oldestMultiXactId;
    nextMXact = MultiXactState->nextMXact;
    LWLockRelease(MultiXactGenLock);
    
    // Read offset from MultiXactOffset SLRU
    // Then read members from MultiXactMember SLRU
    // (code continues with SLRU page access requiring LWLocks)
}

Key insight: SLRU access requires acquiring LWLocks on:

MultiXactOffsetBuffer - for offset SLRU buffer
MultiXactMemberBuffer - for member SLRU buffer
MultiXactOffsetSLRU - for offset SLRU I/O
MultiXactMemberSLRU - for member SLRU I/O

Why INSERT-Only Shows No LWLock Contention

Scenario: 400 INSERT connections on 2 parent rows

What happens:

INSERT 1: Parent xmax = 0
- Acquires KEY SHARE
- Sets xmax = Txn1 (single transaction ID)
- Commits quickly (milliseconds)
INSERT 2: Parent xmax = Txn1 (committed)
- TransactionIdIsInProgress(Txn1) = FALSE (already committed)
- Falls through to "no locker" case (line 5352)
- Sets xmax = Txn2 (single transaction ID)
- No MultiXact created!
INSERT 3, 4, 5...: Same pattern
- Previous transaction already committed
- Just replace xmax with own transaction ID
- Fast, no MultiXact operations

Even when MultiXacts ARE created:

All transactions use KEY SHARE (same lock type)
Compatible locks - no blocking
Transactions complete in milliseconds
LWLock acquisition is fast and non-contended
No visible wait events

Why UPDATE+INSERT Shows LWLock Contention

Scenario: 100 UPDATE + 400 INSERT connections on 2 parent rows

What happens:

UPDATE 1: Acquires FOR UPDATE on parent
- Sets xmax = UpdateTxn1
- Transaction in-progress (even without pg_sleep, takes milliseconds)
INSERT 1: Needs KEY SHARE on same parent
- Sees xmax = UpdateTxn1
- TransactionIdIsInProgress(UpdateTxn1) = TRUE
- Must create MultiXact (line 5553):
```
new_xmax = MultiXactIdCreate(UpdateTxn1, MultiXactStatusUpdate,
                             InsertTxn1, MultiXactStatusForKeyShare);
```
- Parent xmax = MultiXact1
INSERT 2: Needs KEY SHARE on same parent
- Sees xmax = MultiXact1
- Must expand MultiXact (line 5398):
```
new_xmax = MultiXactIdExpand(MultiXact1, InsertTxn2, 
                             MultiXactStatusForKeyShare);
```
- Calls GetMultiXactIdMembers() → SLRU read
- Creates MultiXact2 with 3 members
UPDATE 2: Tries to update same parent
- Sees xmax = MultiXact2 (contains UpdateTxn1)
- BLOCKS on Lock:tuple waiting for UpdateTxn1
- Transaction queued
INSERT 3, 4, 5... (400 connections):
- All see MultiXact xmax
- All must call GetMultiXactIdMembers() → SLRU reads
- All must expand MultiXact → SLRU writes
UPDATE 3, 4, 5... (100 connections):
- All queued waiting for lock
- All holding or waiting for LWLocks on MultiXact structures

The Contention Cascade

With 100 UPDATEs queued + 400 INSERTs running:

Same 2 parent rows = hot spot
Mixed lock types (FOR UPDATE + KEY SHARE) = complex MultiXact operations
Sustained in-progress transactions = constant MultiXact expansion
500 concurrent connections all accessing same MultiXact SLRU pages
LWLock contention on:
- MultiXactOffsetBuffer (10%)
- MultiXactMemberBuffer (6%)
- MultiXactOffsetSLRU (1%)
- MultiXactMemberSLRU (1%)

Proof from Testing

Test 1: Session overlap creates MultiXact

-- Session 1: UPDATE in progress
BEGIN;
UPDATE locations SET loc_name = 'junk-001' WHERE loc_id = 1;
-- xmax = 1567133 (in-progress)

-- Session 2: INSERT while UPDATE in-progress
INSERT INTO users (loc_id, fname) VALUES (1, 'test');
-- Parent xmax changes: 1567133 → 1897574 (MultiXact created!)

Test 2: SLRU activity proves MultiXact operations

INSERT-only workload:

MultiXactMember blks_hit: 19,932,415 → 20,073,773 (75K hits/sec)
MultiXactOffset blks_hit: 19,872,893 → 20,014,799 (75K hits/sec)
LWLock wait events: 0%

INSERT+UPDATE workload:

MultiXactMember blks_hit: 17,198,033 → 17,228,164 (similar rate)
MultiXactOffset blks_hit: 17,148,469 → 17,178,119 (similar rate)
LWLock wait events: 7% (MultiXact-related)

Conclusion

Why INSERT-only doesn't show LWLock contention:

Transactions commit quickly (milliseconds)
Next transaction sees committed xmax → no MultiXact needed (optimization)
Even when MultiXacts created, all KEY SHARE locks are compatible
No blocking → LWLocks acquired/released quickly
No visible wait events

Why UPDATE+INSERT shows LWLock contention:

UPDATE creates in-progress xmax with FOR UPDATE lock
INSERT must create/expand MultiXact to track mixed lock types
UPDATE-UPDATE blocking creates transaction queue (Lock:tuple 11%)
500 connections all accessing same MultiXact SLRU pages for 2 hot rows
Sustained pressure on MultiXact infrastructure
LWLock contention becomes visible as wait events (7%)

The root cause: High concurrency + hot rows + mixed lock types + transaction queuing = MultiXact LWLock bottleneck.

References

PostgreSQL source: src/backend/access/heap/heapam.c
PostgreSQL source: src/backend/access/transam/multixact.c
Workshop: https://catalog.workshops.aws/apg-perf-troubleshooting/en-US/multixact/1-demonstrate
Date: 2025-11-25

Donghua's Blog - DBAGlobe

Tuesday, November 25, 2025