5.5. Transaction Snapshot

A transaction snapshot is a dataset that stores information about whether all transactions are active at a certain point in time for an individual transaction. Here an active transaction means it is in progress or has not yet started.

PostgreSQL internally defines the textual representation format of transaction snapshots as ‘100:100:’. For example, ‘100:100:’ means ’txids that are less than 99 are not active, and txids that are equal or greater than 100 are active’.

In the following descriptions, this convenient representation form is used. If you are not familiar with it, see below.

The built-in function pg_current_snapshot and its textual representation format

The function pg_current_snapshot shows a snapshot of the current transaction.

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

The textual representation of the txid_current_snapshot is ‘xmin:xmax:xip_list’, and the components are described as follows:

• xmin
  (earliest txid that is still active): All earlier transactions will either be committed and visible, or rolled back and dead.

• xmax
  (first as-yet-unassigned txid): All txids greater than or equal to this are not yet started as of the time of the snapshot, and thus invisible.

• xip_list
  (list of active transaction ids at the time of the snapshot): The list includes only active txids between xmin and xmax.

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

Here are two specific examples:

The first example is ‘100:100:’. This snapshot means the following (Figure 5.8(a)):

• txids equal or less than 99 are not active because xmin is 100.
• txids equal or greater than 100 are active because xmax is 100.

The second example is ‘100:104:100,102’. This snapshot means the following (Figure 5.8(b)):

• txids equal or less than 99 are not active.
• txids equal or greater than 104 are active.
• txids 100 and 102 are active since they exist in the xip list, whereas txids 101 and 103 are not active.

Transaction snapshots are provided by the transaction manager. In the READ COMMITTED isolation level, the transaction obtains a snapshot whenever an SQL command is executed; otherwise (REPEATABLE READ or SERIALIZABLE), the transaction only gets a snapshot when the first SQL command is executed. The obtained transaction snapshot is used for a visibility check of tuples, which is described in Section 5.7.

When using the obtained snapshot for the visibility check, active transactions in the snapshot must be treated as in progress even if they have actually been committed or aborted. This rule is important because it causes the difference in the behavior between READ COMMITTED and REPEATABLE READ (or SERIALIZABLE). We refer to this rule repeatedly in the following sections.

In the remainder of this section, the transaction manager and transactions are described using the specific scenario illustrated in Figure 5.9.

The transaction manager always holds information about currently running transactions. Suppose that three transactions start one after another, and the isolation level of Transaction_A and Transaction_B are READ COMMITTED, and that of Transaction_C is REPEATABLE READ.

• T1:
  Transaction_A starts and executes the first SELECT command. When executing the first command, Transaction_A requests the txid and snapshot of this moment.
  In this scenario, the transaction manager assigns txid 200, and returns the transaction snapshot ‘200:200:’.

• T2:
  Transaction_B starts and executes the first SELECT command. The transaction manager assigns txid 201, and returns the transaction snapshot ‘200:200:’ because Transaction_A (txid 200) is in progress. Thus, Transaction_A cannot be seen from Transaction_B.

• T3:
  Transaction_C starts and executes the first SELECT command. The transaction manager assigns txid 202, and returns the transaction snapshot ‘200:200:’, thus, Transaction_A and Transaction_B cannot be seen from Transaction_C.

• T4:
  Transaction_A has been committed. The transaction manager removes the information about this transaction.

• T5:
  Transaction_B and Transaction_C execute their respective SELECT commands.
  Transaction_B requires a transaction snapshot because it is in the READ COMMITTED level.
  In this scenario, Transaction_B obtains a new snapshot ‘201:201:’ because Transaction_A (txid 200) is committed. Thus, Transaction_A is no longer invisible from Transaction_B. Transaction_C does not require a transaction snapshot because it is in the REPEATABLE READ level and uses the obtained snapshot, i.e. ‘200:200:’. Thus, Transaction_A is still invisible from Transaction_C.