About XIDs and XID Wraparound in Greenplum

Products

VMware Tanzu Greenplum VMware Tanzu Data Suite VMware Tanzu Data Suite Greenplum

Issue/Introduction

This article applies to all versions of VMware Tanzu Greenplum Database (GPDB).

This article describes transaction ID (XID) and XID Wraparound in the Greenplum database.

More information can be found at Example of Managing Transaction IDs

Resolution

What are XIDs and why are they important in Greenplum?

Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) [1] model to manage concurrent transactions for heap tables. Like in PostgreSQL [2], one of the MVCC model fundamentals is to be able to compare transaction ID (XID) numbers to manage multiple versions of data rows.

At the beginning of a query, the model uses the XID to determine which rows are visible to it. To do this, every table has system columns that are implicitly defined for this matter. Those are xmin and xmax [3]. The xmin column contains the XID of the inserting transaction for the row and the xmax column contains the XID of the deleting transaction for it. Following this logic, a visible row would have a certain xmin and a zero xmax (except for certain situations). The model then compares the XID of the running query to the rows in the target tables and determines which row version it should use.

What is the XID wraparound?

The XID is a 32-bit value [4], so a database could theoretically execute over four billion transactions before the value overflows and wraps to zero. However, Greenplum Database uses modulo 232 arithmetic with XIDs which allows the transaction IDs to wrap around, much as a clock wraps at twelve o'clock. For any given XID, there could be about two billion (2^31-1) past XIDs and two billion future XIDs. This works until a version of a row persists through about two billion transactions, when it suddenly appears to be a new row.

There are two server configuration parameters in Greenplum that are related to this, xid_warn_limit and xid_stop_limit. The xid_warn_limit parameter specifies the number of XIDs before the xid_stop_limit when a warning is issued. The xid_stop_limit parameter specifies the number of transaction IDs before wraparound would occur when an error is issued and the new transactions cannot be created. These configuration parameters are both hidden and should not be modified except for very rare scenarios.

What is a frozen row?

To prevent the XID wraparound, Greenplum (as Postgres) has a special XID, called FrozenXID, which is always considered older than any regular XID it is compared with. The xmin of a row must be replaced with the FrozenXID within two billion transactions and this is one of the functions the VACUUM command performs.

The pg_class table contains a relfrozenxid column which contains the freeze cutoff XID that was used by the last VACUUM for that table. All the rows with normal XIDs older than this cutoff XID are guaranteed to have been replaced by FrozenXID and are therefore "frozen".

What is a database age and how to check it?

Database age is the age of the oldest XID in the database and knowing its value is very important to determine how close we are to the XID wraparound. The KB article provides information about database age and how to check it.

Numbers and different scenarios

Based on the numbers, there are four potential scenarios that can happen. Note that default values have been used to calculate the scenarios below (xid_stop_limit=1000000000 and xid_warn_limit=500000000):

Below xid_warn_limit

age < (2^31-1 - 1000000000 - 500000000)

Between xid_warn_limit and xid_stop_limit

(2^31-1 - 1000000000 - 500000000) < age <  (2^31-1 - 1000000000)

Over xid_stop_limit
```
age > (2^31-1 - 1000000000)
```
Over the wraparound limit
```
age > 2^31-1
```

What to do if the `xid_warn_limit` has been already reached?

If the xid_warn_limit has been reached, connections to the affected database will still go through but a warning will be printed with every transaction:

HINT: To avoid a database shutdown, execute a full-database VACUUM in       "DATABASE".
WARNING: database "DATABASE" must be vacuumed within XYZ transactions

The KB article provides information about how to proceed when warnings are seen.

What to do if the `xid_stop_limit` has been already reached?

If the xid_stop_limit has been reached, connections to the affected database will be rejected and the following error message will be displayed:

FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"

The KB article contains information about how to recover from reaching this situation.

What to do if the wraparound limit has been already reached?

If the wraparound limit (2 Billion) has been reached, age (datfrozenxid) in pg_database will produce a negative value for the affected database. This means catastrophic data loss (actually the data is still there but you cannot retrieve it) and is therefore prevented by having xid_stop_limit in place.

This should never happen as Greenplum rejects any incoming connections after the xid_stop_limit has been reached (except for template0 or any database with datallowconn set to false in pg_database because wraparound checks don't apply to those databases).

In case this is seen, Support should be contacted.

Additional Information

References

[1] Postgres documentation: Routine Vacuuming

[2] Greenplum documentation: About Concurrency Control in Greenplum Database

[3] Postgres documentation: System Columns

[4] https://en.wikipedia.org/wiki/Integer_(computer_science)