Multixact ID

An identifier used to support row locking by multiple transactions

A multixact ID is an internal identifier used  support row locking by multiple transactions.

Multixact IDs are created when transactions use "SELECT … FOR UPDATE" (or one of these lock modes: SHARE, KEY SHARE, NO KEY UPDATE) to lock and update tuples.

Multixact IDs live in the pg_multixact directory.

Examples

Show multixact ID usage:

  SELECT relname, relminmxid,
         $NextMultiXactId - relminmxid::TEXT::BIGINT AS age
    FROM pg_class
   WHERE relminmxid::TEXT::BIGINT <> 0
ORDER BY relminmxid::TEXT::BIGINT ASC
   LIMIT 10;

where $NextMultiXactId is the value returned by:

SELECT next_multixact_id FROM pg_control_checkpoint()

(For PostgreSQL 9.5 and earlier, use the value contained in pg_controldata output for "Latest checkpoint's NextMultiXactId").

Categories

Locking, PostgreSQL internals, Transactions

See also

mxid_age(), vacuum_multixact_freeze_table_age, autovacuum_multixact_freeze_table_age