5.7. Visibility Check

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

5.7.1. Visibility Check

Figure 5.10 shows a scenario to describe the visibility check.

Fig. 5.10. Scenario to describe visibility check.

In the scenario shown in Fig. 5.10, SQL commands are executed in the following time sequence.

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

To simplify the description, assume that there are only two transactions, i.e. txid 200 and 201. The isolation level of txid 200 is READ COMMITTED, and the isolation level of txid 201 is either READ COMMITTED or REPEATABLE READ.

We explore how SELECT commands perform a visibility check for each tuple.

SELECT commands of T3:

At T3, there is only Tuple_1 in the table ’tbl’ and 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, we explore the SELECT command executed by txid 200. Tuple_1 is invisible by Rule 7 and Tuple_2 is visible by Rule 2. Therefore, 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 = INVAILD $\Rightarrow$ Visible

testdb=# -- txid 200
testdb=# SELECT * FROM tbl;
 name 
------
 Hyde
(1 row)

On the other hand, 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)

If the updated tuples are visible from other transactions before they are committed, this is known as Dirty Reads, also known as wr-conflicts. However, as shown above, Dirty Reads do not occur in any isolation levels in PostgreSQL.

SELECT command of T7:

In the following, the behaviors of SELECT commands of T7 in both isolation levels are described.

When txid 201 is in the READ COMMITTED level, txid 200 is treated as COMMITTED because the transaction snapshot is ‘201:201:’. 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 the SELECT commands, which are executed before and after txid 200 is committed, differ. This is generally known as Non-Repeatable Reads.

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

Note that Non-Repeatable Reads do not occur in the REPEATABLE READ (and SERIALIZABLE) level.

  • 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)
Hint Bits

To obtain the status of a transaction, PostgreSQL internally provides three functions: TransactionIdIsInProgress, TransactionIdDidCommit, and TransactionIdDidAbort. These functions are implemented to reduce frequent access to the clog, such as caches. However, bottlenecks will occur if they are executed whenever each tuple is checked.

To deal with this issue, PostgreSQL uses hint bits, which are shown blow:

#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 */

When reading or writing a tuple, PostgreSQL sets hint bits to the t_informask of the tuple if possible. For example, assume that PostgreSQL checks the status of the t_xmin of a tuple and obtains the status COMMITTED. In this case, PostgreSQL sets a hint bit HEAP_XMIN_COMMITTED to the t_infomask of the tuple. If hint bits are already set, TransactionIdDidCommit and TransactionIdDidAbort are no longer needed. Therefore, PostgreSQL can efficiently check the statuses of both t_xmin and t_xmax of each tuple.

5.7.2. Phantom Reads in PostgreSQL’s REPEATABLE READ Level

REPEATABLE READ as defined in the ANSI SQL-92 standard allows Phantom Reads. However, PostgreSQL’s implementation does not allow them. In principle, SI does not allow Phantom Reads.

Assume that two transactions, i.e. Tx_A and Tx_B, are running concurrently. Their isolation levels are READ COMMITTED and REPEATABLE READ, and their txids are 100 and 101, respectively. First, Tx_A inserts a tuple. Then, it is committed. The t_xmin of the inserted tuple is 100.

Next, Tx_B executes a SELECT command; however, the tuple inserted by Tx_A is invisible by Rule 5. Thus, 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)