5.5. Transaction Snapshot

A transaction snapshot is a dataset that stores information regarding the activity of all transactions at a specific point in time for an individual transaction. In this context, an active transaction is either in progress or has not yet started.

PostgreSQL defines the internal textual representation of a transaction snapshot in the format xmin:xmax:xip_list. For example, in a simplified representation such as ‘100:100:’, it indicates that txids less than 100 are not active, while txids equal to or greater than 100 are active.

This representation format is used throughout the following descriptions. (For further details on the format, refer to the below.)

The built-in function pg_current_snapshot and its textual representation format

The function pg_current_snapshot returns a snapshot of the current transaction.

testdb=# SELECT pg_current_snapshot();
 pg_current_snapshot
---------------------
 100:104:100,102
(1 row)

The textual representation of a snapshot follows the format xmin:xmax:xip_list. Each component is defined as follows:

• xmin: The earliest txid that is still active. All earlier transactions are either committed and visible, or rolled back and dead.
• xmax: The first as-yet-unassigned txid. All txids greater than or equal to this value have not started at the time of the snapshot and are therefore invisible.
• xip_list: A list of active txids at the time of the snapshot. This list includes only active txids between xmin and xmax.

For example, in the snapshot ‘100:104:100,102’, xmin is 100, xmax is 104, and the xip_list contains 100 and 102.

Figure 5.8 illustrates two specific examples of snapshot representations.

• Example 1: ‘100:100:’ As shown in Figure 5.8(a), this snapshot indicates the following:

  • Txids less than 100 are not active (xmin = 100).
  • Txids greater than or equal to 100 are active (xmax = 100).

• Example 2: ‘100:104:100,102’ As shown in Figure 5.8(b), this snapshot indicates the following:

  • Txids less than 100 are not active.
  • Txids greater than or equal to 104 are active.
  • Txids 100 and 102 are active because they appear in the xip_list.
  • Txids 101 and 103 are not active.

The transaction manager provides these snapshots. Under the READ COMMITTED isolation level, a transaction obtains a new snapshot whenever an SQL command is executed. In contrast, under REPEATABLE READ or SERIALIZABLE levels, a transaction obtains a snapshot only when the first SQL command is executed. These snapshots are essential for the visibility check of tuples, which is described in Section 5.7.

When performing a visibility check, transactions identified as active in the snapshot must be treated as in progress, even if they have since been committed or aborted. This rule creates the fundamental behavioral difference between READ COMMITTED and REPEATABLE READ (or SERIALIZABLE).

The following scenario, illustrated in Figure 5.9, demonstrates the interaction between the transaction manager and individual transactions.

The transaction manager maintains information on all currently running transactions. In this scenario, three transactions start sequentially: Transaction_A and Transaction_B use READ COMMITTED, while Transaction_C uses REPEATABLE READ.

• T1:
  Transaction_A starts and executes its first SELECT command. At this moment, the transaction manager assigns txid 200 and returns the snapshot ‘200:200:’.

• T2:
  Transaction_B starts and executes its first SELECT command. The transaction manager assigns txid 201 and returns the snapshot ‘200:200:’ because Transaction_A (txid 200) is still in progress. Consequently, Transaction_A is invisible to Transaction_B.

• T3:
  Transaction_C starts and executes its first SELECT command. The transaction manager assigns txid 202 and returns the snapshot ‘200:200:’. Both Transaction_A and Transaction_B are invisible to Transaction_C.

• T4:
  Transaction_A commits. The transaction manager removes the information for this transaction.

• T5:
  Transaction_B and Transaction_C execute subsequent SELECT commands:

  • Transaction_B obtains a new snapshot because it operates at the READ COMMITTED level. It receives snapshot ‘201:201:’ because Transaction_A is now committed. Thus, Transaction_A becomes visible to Transaction_B.
  • In contrast, Transaction_C does not request a new snapshot. It continues to use the initial snapshot (‘200:200:’) as required by the REPEATABLE READ level. Therefore, Transaction_A remains invisible to Transaction_C.