Tuesday, November 25, 2025

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:

  1. Reading existing members from SLRU (GetMultiXactIdMembers)
  2. Filtering dead members
  3. 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:

  1. INSERT 1: Parent xmax = 0

    • Acquires KEY SHARE
    • Sets xmax = Txn1 (single transaction ID)
    • Commits quickly (milliseconds)

  2. 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!

  3. 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:

  1. UPDATE 1: Acquires FOR UPDATE on parent

    • Sets xmax = UpdateTxn1
    • Transaction in-progress (even without pg_sleep, takes milliseconds)

  2. 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

  3. 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

  4. UPDATE 2: Tries to update same parent

    • Sees xmax = MultiXact2 (contains UpdateTxn1)
    • BLOCKS on Lock:tuple waiting for UpdateTxn1
    • Transaction queued

  5. INSERT 3, 4, 5... (400 connections):

    • All see MultiXact xmax
    • All must call GetMultiXactIdMembers() → SLRU reads
    • All must expand MultiXact → SLRU writes

  6. 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:

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

Why UPDATE+INSERT shows LWLock contention:

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

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

References

Posted by Luo Donghua at 7:30 PM 0 comments
Labels:
Subscribe to: Comments (Atom)