Transaction IDs, snapshots and tuple visibility

The previous two lessons established that visibility is decided by looking up transaction status rather than by locking. This lesson is about the thing that does the looking up, because it is the object most of Part VIII’s diagnostics are really asking about.

Transaction ids

A transaction that writes anything is assigned a transaction id. A transaction that only reads is not — this matters more than it sounds, and the lesson returns to it.

SELECT pg_current_xact_id();     -- assigns one if this transaction lacks it
SELECT pg_current_xact_id_if_assigned();  -- NULL rather than assigning one

Use the second in monitoring. The first has a side effect, and running it in a loop on a busy system consumes transaction ids for no reason.

A snapshot is three values

pg_current_snapshot() renders them as xmin:xmax:xip_list.

The definition of xmax is the part that catches people out, and it is worth seeing before it costs you an hour.

$ psql -U postgres -c "SELECT pg_current_snapshot() AS snapshot, pg_snapshot_xmin(pg_current_snapshot()) AS snap_xmin, pg_snapshot_xmax(pg_current_snapshot()) AS snap_xmax" -c "SELECT * FROM pg_snapshot_xip(pg_current_snapshot()) AS in_progress"

    snapshot    | snap_xmin | snap_xmax
----------------+-----------+-----------
683657:683657: |    683657 |    683657
(1 row)

in_progress
-------------
(0 rows)

Three transactions running and an empty in-progress list. That is correct. xmax is 683657, all three running ids are at or above it, and the list holds only ids between xmin and xmax. Anything at or above xmax is invisible without needing to be enumerated.

Now the same measurement arranged so that one long transaction sits below xmax: it starts, then three short transactions begin and complete.

$ psql -U postgres -c "SELECT pg_current_snapshot() AS snapshot, pg_snapshot_xmin(pg_current_snapshot()) AS snap_xmin, pg_snapshot_xmax(pg_current_snapshot()) AS snap_xmax" -c "SELECT * FROM pg_snapshot_xip(pg_current_snapshot()) AS in_progress_xid"

       snapshot       | snap_xmin | snap_xmax
----------------------+-----------+-----------
683660:683664:683660 |    683660 |    683664
(1 row)

in_progress_xid
-----------------
        683660
(1 row)

Now 683660 appears explicitly, because it is below xmax. This is the shape a long-running transaction produces, and it is the shape that holds xmin down.

Testing one tuple

With a snapshot in hand, deciding whether a version is visible is mechanical. Given a tuple with xmin = C (creator) and xmax = D (deleter, superseder or locker):

1. Is C visible to this snapshot? If C is at or above the snapshot’s xmax, or is in the in-progress list, the version does not exist yet as far as this transaction is concerned. Invisible.
2. Did C commit? If it aborted, the version never existed. Invisible.
3. Is xmax zero? Then nothing has superseded it. Visible.
4. Is D visible to this snapshot, and did it commit? If yes, the version has been superseded. Invisible. If D is still running or aborted, the deletion has not happened as far as this transaction is concerned. Visible.

Step 4 is exactly what session B did in lesson VII-01: it found xmax = 683645, determined that transaction had not committed, and treated the old version as current.

Reading the horizon on a running system

pg_stat_activity exposes two columns that are constantly confused.

backend_xmin is the one that matters for vacuum. A read-only session has no backend_xid at all and can still pin the horizon indefinitely through backend_xmin.

$ psql -U postgres -c "SELECT pid, state, age(backend_xmin) AS xmin_age, round(extract(epoch FROM now()-xact_start)) AS xact_s, left(query,38) AS query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC"

 pid  | state  | xmin_age | xact_s |                 query
------+--------+----------+--------+----------------------------------------
2136 | active |        9 |      2 | SELECT pg_sleep(30);
2206 | active |        0 |      0 | SELECT pid, state, age(backend_xmin) A
(2 rows)

The holder’s snapshot has aged by nine transactions; the fresh session reports zero. The maximum of age(backend_xmin) across sessions is the age of the oldest snapshot the system must respect.

Sessions are not the only thing that can hold it back. The full list, as one query:

SELECT 'oldest running xact'     AS source,
       coalesce(max(age(backend_xmin))::text, 'none') AS xid_age
  FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'oldest prepared xact',
       coalesce(max(age(transaction))::text, 'none') FROM pg_prepared_xacts
UNION ALL
SELECT 'oldest replication slot',
       coalesce(max(age(xmin))::text, 'none') FROM pg_replication_slots
UNION ALL
SELECT 'oldest replica feedback',
       coalesce(max(age(backend_xmin))::text, 'none') FROM pg_stat_replication;

$ psql -U postgres -f horizon.sql

         source          | xid_age
-------------------------+---------
oldest running xact     |       9
oldest prepared xact    | none
oldest replication slot | none
oldest replica feedback | none
(4 rows)

Learn this query now. When Part VIII asks “why is vacuum not removing anything”, this is the query that answers it, and three of those four rows are sources people forget exist.

What to take from this

• A snapshot is xmin, xmax and the in-progress list between them. xmax is one past the highest completed id, which is why running transactions can be absent from the list.
• Transaction ids wrap and have no ordering operator. Use age(), always.
• backend_xid is what a session wrote under; backend_xmin is what it pins. Vacuum cares about the second.
• Four things hold the horizon: running transactions, prepared transactions, replication slots and replica feedback. Check all four.
• pg_current_xact_id() assigns an id as a side effect. Use pg_current_xact_id_if_assigned() in anything that runs repeatedly.

Cross-course references

• Observability for Production Sysadmins — Part LIX (Database observability) covers exporting transaction age as a series, which is the only way the slow drift towards wraparound becomes visible.
• Linux for Production Sysadmins — Part VI (Processes) covers reading the backend that holds an old snapshot as an operating-system process, which is often how the owning application is identified.

Quiz