5.1 Transaction ID

PostgreSQL’s concurrency control mechanism uses transaction IDs (txids) to identify transactions and determine the visibility of tuples.

At the start of each transaction, the transaction manager assigns a 32-bit unsigned integer as its txid, providing approximately 4.2 billion possible values.

The built-in txid_current() function returns the current txid:

testdb=# BEGIN;
BEGIN
testdb=# SELECT txid_current();
 txid_current
--------------
          100
(1 row)

PostgreSQL reserves three special txids:

  • 0: Represents an Invalid txid.
  • 1: Represents the Bootstrap txid, used only during database cluster initialization.
  • 2: Represents the Frozen txid, as described in Section 5.10.2.

Txids are comparable. From the perspective of txid 100, IDs greater than 100 are considered “in the future” and are invisible. IDs less than 100 are considered “in the past” and are visible (Figure 5.1 a)).

Figure 5.1. Transaction ids in PostgreSQL.

Because the 32-bit txid space is insufficient in practical systems, PostgreSQL treats it as a circle. For any specific txid, the previous 2.1 billion IDs are “in the past”, while the subsequent 2.1 billion IDs are “in the future” (Figure 5.1 b)).

The txid wraparound problem and its solutions are described in Section 5.10.1.

Info

The BEGIN command does not assign a txid.

In PostgreSQL, the transaction manager assigns a txid only when the first command after BEGIN is executed.

testdb=# BEGIN;                    -- TXID is not assigned yet.
BEGIN
testdb=# SELECT txid_current();    -- TXID is just assigned.
 txid_current
--------------
          100
(1 row)