5.7. Visibility Check

This section describes the visibility check process in PostgreSQL. This process selects heap tuples of the appropriate versions for a given transaction. This section also explains how PostgreSQL prevents the anomalies defined in the ANSI SQL-92 Standard: Dirty Reads, Non-Repeatable Reads, and Phantom Reads.

Section Contents

5.7.1. Visibility Check

Figure 5.10 illustrates a scenario for the visibility check.

Figure 5.10. Scenario to describe visibility check.

In the scenario shown in Figure 5.10, SQL commands execute in the following sequence:

  • T1: Start transaction (txid 200)
  • T2: Start transaction (txid 201)
  • T3: Execute SELECT commands for txid 200 and 201
  • T4: Execute UPDATE command for txid 200
  • T5: Execute SELECT commands for txid 200 and 201
  • T6: Commit txid 200
  • T7: Execute SELECT command for txid 201

To simplify the description, this scenario assumes only two transactions: txid 200 and 201. The isolation level of txid 200 is READ COMMITTED. The isolation level of txid 201 is either READ COMMITTED or REPEATABLE READ.

The following describes how SELECT commands perform a visibility check for each tuple.

SELECT commands of T3:

At T3, only Tuple_1 exists in table ’tbl’. It is visible by Rule 6. Therefore, SELECT commands in both transactions return ‘Jekyll’.

  • Rule6(Tuple_1) $\Rightarrow$ Status(t_xmin:199) = COMMITTED $\wedge$ t_xmax = INVALID $\Rightarrow$ Visible
testdb=# -- txid 200
testdb=# SELECT * FROM tbl;
  name
--------
 Jekyll
(1 row)
testdb=# -- txid 201
testdb=# SELECT * FROM tbl;
  name
--------
 Jekyll
(1 row)

SELECT commands of T5:

First, consider the SELECT command executed by txid 200. Tuple_1 is invisible by Rule 7, and Tuple_2 is visible by Rule 2. Consequently, this SELECT command returns ‘Hyde’.

  • Rule7(Tuple_1): Status(t_xmin:199) = COMMITTED $\wedge$ Status(t_xmax:200) = IN_PROGRESS $\wedge$ t_xmax:200 = current_txid:200 $\Rightarrow$ Invisible
  • Rule2(Tuple_2): Status(t_xmin:200) = IN_PROGRESS $\wedge$ t_xmin:200 = current_txid:200 $\wedge$ t_xmax = INVALID $\Rightarrow$ Visible
testdb=# -- txid 200
testdb=# SELECT * FROM tbl;
 name
------
 Hyde
(1 row)

In contrast, in the SELECT command executed by txid 201, Tuple_1 is visible by Rule 8, and Tuple_2 is invisible by Rule 4. Therefore, this SELECT command returns ‘Jekyll’.

  • Rule8(Tuple_1): Status(t_xmin:199) = COMMITTED $\wedge$ Status(t_xmax:200) = IN_PROGRESS $\wedge$ t_xmax:200 $\ne$ current_txid:201 $\Rightarrow$ Visible
  • Rule4(Tuple_2): Status(t_xmin:200) = IN_PROGRESS $\wedge$ t_xmin:200 $\ne$ current_txid:201 $\Rightarrow$ Invisible
testdb=# -- txid 201
testdb=# SELECT * FROM tbl;
  name
--------
 Jekyll
(1 row)
Dirty Read

A Dirty Read (or wr-conflict) is the visibility of uncommitted updates to other transactions. Such reads do not occur at any isolation level in PostgreSQL.

SELECT command of T7:

The following describes the behavior of SELECT commands at T7 for both isolation levels.

When txid 201 uses the READ COMMITTED level, the transaction snapshot is ‘201:201:’. In this case, txid 200 is treated as COMMITTED. Therefore, Tuple_1 is invisible by Rule 10, and Tuple_2 is visible by Rule 6. The SELECT command returns ‘Hyde’.

  • Rule10(Tuple_1): Status(t_xmin:199) = COMMITTED $\wedge$ Status(t_xmax:200) = COMMITTED $\wedge$ Snapshot(t_xmax:200) $\ne$ active $\Rightarrow$ Invisible
  • Rule6(Tuple_2): Status(t_xmin:200) = COMMITTED $\wedge$ t_xmax = INVALID $\Rightarrow$ Visible
testdb=# -- txid 201 (READ COMMITTED)
testdb=# SELECT * FROM tbl;
 name
------
 Hyde
(1 row)

Note that the results of SELECT commands differ depending on whether txid 200 has committed. This phenomenon is known as a Non-Repeatable Read.

In contrast, when txid 201 uses the REPEATABLE READ level, the transaction snapshot is ‘200:200:’. Consequently, txid 200 is treated as IN_PROGRESS. Therefore, Tuple_1 is visible by Rule 9, and Tuple_2 is invisible by Rule 5. The SELECT command returns ‘Jekyll’.

Non-Repeatable Reads do not occur in REPEATABLE READ (or SERIALIZABLE) isolation levels.

  • Rule9(Tuple_1): Status(t_xmin:199) = COMMITTED $\wedge$ Status(t_xmax:200) = COMMITTED $\wedge$ Snapshot(t_xmax:200) = active $\Rightarrow$ Visible

  • Rule5(Tuple_2): Status(t_xmin:200) = COMMITTED $\wedge$ Snapshot(t_xmin:200) = active $\Rightarrow$ Invisible

testdb=# -- txid 201 (REPEATABLE READ)
testdb=# SELECT * FROM tbl;
  name
--------
 Jekyll
(1 row)

5.7.2. Hint Bits

PostgreSQL provides three internal functions to obtain transaction status: TransactionIdIsInProgress(), TransactionIdDidCommit(), and TransactionIdDidAbort(). These functions use caches to reduce frequent access to the clog. However, executing them for every tuple check would create bottlenecks.

To resolve this issue, PostgreSQL uses hint bits, as defined below:

#define HEAP_XMIN_COMMITTED       0x0100   /* t_xmin committed */
#define HEAP_XMIN_INVALID         0x0200   /* t_xmin invalid/aborted */
#define HEAP_XMAX_COMMITTED       0x0400   /* t_xmax committed */
#define HEAP_XMAX_INVALID         0x0800   /* t_xmax invalid/aborted */

PostgreSQL sets hint bits in the t_infomask of a tuple during read or write operations whenever possible.

For example, if PostgreSQL checks the status of t_xmin and obtains COMMITTED, it sets the HEAP_XMIN_COMMITTED hint bit in the t_infomask of that tuple.

Once hint bits are set, PostgreSQL no longer needs to call TransactionIdDidCommit() or TransactionIdDidAbort(). This mechanism allows the system to efficiently check the statuses of both t_xmin and t_xmax for each tuple.

5.7.3. Phantom Reads in PostgreSQL’s REPEATABLE READ Level

The ANSI SQL-92 standard defines REPEATABLE READ as an isolation level that allows Phantom Reads. However, PostgreSQL’s implementation prevents them. In principle, Snapshot Isolation (SI) does not allow Phantom Reads.

Assume two transactions, Tx_A and Tx_B, run concurrently. Their isolation levels are READ COMMITTED and REPEATABLE READ, and their txids are 100 and 101, respectively. First, Tx_A inserts a tuple and commits. The t_xmin of the inserted tuple is 100.

Next, Tx_B executes a SELECT command. The tuple inserted by Tx_A is invisible according to Rule 5. Therefore, Phantom Reads do not occur.

  • Rule5(new tuple): Status(t_xmin:100) = COMMITTED $\wedge$ Snapshot(t_xmin:100) = active $\Rightarrow$ Invisible
testdb=# -- Tx_A: txid 100
testdb=# START TRANSACTION
testdb-#  ISOLATION LEVEL READ COMMITTED;
START TRANSACTION
testdb=# SELECT txid_current();
 txid_current
--------------
          100
(1 row)

testdb=# INSERT INTO tbl(id, data)
                VALUES (1,'phantom');
INSERT 1
testdb=# COMMIT;
COMMIT
testdb=# -- Tx_B: txid 101
testdb=# START TRANSACTION
testdb-#  ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION
testdb=# SELECT txid_current();
 txid_current
--------------
          101
(1 row)






testdb=# SELECT * FROM tbl WHERE id=1;
 id | data
----+------
(0 rows)