About Concurrency Control in Greenplum Database
Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrent transactions for heap tables.
Concurrency control in a database management system allows concurrent queries to complete with correct results while ensuring the integrity of the database. Traditional databases use a two-phase locking protocol that prevents a transaction from modifying data that has been read by another concurrent transaction and prevents any concurrent transaction from reading or writing data that another transaction has updated. The locks required to coordinate transactions add contention to the database, reducing overall transaction throughput.
Greenplum Database uses the PostgreSQL Multiversion Concurrency Control (MVCC) model to manage concurrency for heap tables. With MVCC, each query operates on a snapshot of the database when the query starts. While it runs, a query cannot see changes made by other concurrent transactions. This ensures that a query sees a consistent view of the database. Queries that read rows can never block waiting for transactions that write rows. Conversely, queries that write rows cannot be blocked by transactions that read rows. This allows much greater concurrency than traditional database systems that employ locks to coordinate access between transactions that read and write data.
Note: Append-optimized tables are managed with a different concurrency control model than the MVCC model discussed in this topic. They are intended for “write-once, read-many” applications that never, or only very rarely, perform row-level updates.
The MVCC model depends on the system’s ability to manage multiple versions of data rows. A query operates on a snapshot of the database at the start of the query. A snapshot is the set of rows that are visible at the beginning of a statement or transaction. The snapshot ensures the query has a consistent and valid view of the database for the duration of its execution.
Each transaction is assigned a unique transaction ID (XID), an incrementing 32-bit value. When a new transaction starts, it is assigned the next XID. An SQL statement that is not enclosed in a transaction is treated as a single-statement transaction—the
COMMIT are added implicitly. This is similar to autocommit in some database systems.
Note: Greenplum Database assigns XID values only to transactions that involve DDL or DML operations, which are typically the only transactions that require an XID.
When a transaction inserts a row, the XID is saved with the row in the
xmin system column. When a transaction deletes a row, the XID is saved in the
xmax system column. Updating a row is treated as a delete and an insert, so the XID is saved to the
xmax of the current row and the
xmin of the newly inserted row. The
xmax columns, together with the transaction completion status, specify a range of transactions for which the version of the row is visible. A transaction can see the effects of all transactions less than
xmin, which are guaranteed to be committed, but it cannot see the effects of any transaction greater than or equal to
Multi-statement transactions must also record which command within a transaction inserted a row (
cmin) or deleted a row (
cmax) so that the transaction can see changes made by previous commands in the transaction. The command sequence is only relevant during the transaction, so the sequence is reset to 0 at the beginning of a transaction.
XID is a property of the database. Each segment database has its own XID sequence that cannot be compared to the XIDs of other segment databases. The master coordinates distributed transactions with the segments using a cluster-wide session ID number, called
gp_session_id. The segments maintain a mapping of distributed transaction IDs with their local XIDs. The master coordinates distributed transactions across all of the segment with the two-phase commit protocol. If a transaction fails on any one segment, it is rolled back on all segments.
You can see the
cmax columns for any row with a
SELECT xmin, xmax, cmin, cmax, * FROM <tablename>;
Because you run the
SELECT command on the master, the XIDs are the distributed transactions IDs. If you could run the command in an individual segment database, the
xmax values would be the segment’s local XIDs.
Note: Greenplum Database distributes all of a replicated table’s rows to every segment, so each row is duplicated on every segment. Each segment instance maintains its own values for the system columns
cmax, as well as for the
ctid system columns. Greenplum Database does not permit user queries to access these system columns for replicated tables because they have no single, unambiguous value to evaluate in a query.
The MVCC model uses transaction IDs (XIDs) to determine which rows are visible at the beginning of a query or transaction. The XID is a 32-bit value, so a database could theoretically run 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 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. To prevent this, Greenplum 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
FrozenXID within two billion transactions, and this is one of the functions the
VACUUM command performs.
Vacuuming the database at least every two billion transactions prevents XID wraparound. Greenplum Database monitors the transaction ID and warns if a
VACUUM operation is required.
A warning is issued when a significant portion of the transaction IDs are no longer available and before transaction ID wraparound occurs:
WARNING: database "<database_name>" must be vacuumed within <number_of_transactions> transactions
When the warning is issued, a
VACUUM operation is required. If a
VACUUM operation is not performed, Greenplum Database stops creating transactions to avoid possible data loss when it reaches a limit prior to when transaction ID wraparound occurs and issues this error:
FATAL: database is not accepting commands to avoid wraparound data loss in database "<database_name>"
See Recovering from a Transaction ID Limit Error for the procedure to recover from this error.
The server configuration parameters
xid_stop_limit control when the warning and error are displayed. The
xid_warn_limit parameter specifies the number of transaction IDs before the
xid_stop_limit when the warning is issued. The
xid_stop_limit parameter specifies the number of transaction IDs before wraparound would occur when the error is issued and new transactions cannot be created.
The SQL standard describes three phenomena that can occur when database transactions run concurrently:
- Dirty read – a transaction can read uncommitted data from another concurrent transaction.
- Non-repeatable read – a row read twice in a transaction can change because another concurrent transaction committed changes after the transaction began.
- Phantom read – a query run twice in the same transaction can return two different sets of rows because another concurrent transaction added rows.
The SQL standard defines four transaction isolation levels that database systems can support, with the phenomena that are allowed when transactions run concurrently for each level.
|Level||Dirty Read||Non-Repeatable||Phantom Read|
READ UNCOMMITTED and
READ COMMITTED isolation modes behave like the SQL standard
READ COMMITTED mode. Greenplum Database
REPEATABLE READ isolation modes behave like the SQL standard
READ COMMITTED mode, except that Greenplum Database also prevents phantom reads.
The difference between
READ COMMITTED and
REPEATABLE READ is that with
READ COMMITTED, each statement in a transaction sees only rows committed before the statement started, while in
READ COMMITTED mode, statements in a transaction see only rows committed before the transaction started.
READ COMMITTED isolation mode the values in a row retrieved twice in a transaction can differ if another concurrent transaction has committed changes since the transaction began.
READ COMMITTED mode also permits phantom reads, where a query run twice in the same transaction can return two different sets of rows.
REPEATABLE READ isolation mode prevents non-repeatable reads and phantom reads, although the latter is not required by the standard. A transaction that attempts to modify data modified by another concurrent transaction is rolled back. Applications that run transactions in
REPEATABLE READ mode must be prepared to handle transactions that fail due to serialization errors. If
REPEATABLE READ isolation mode is not required by the application, it is better to use
READ COMMITTED mode.
SERIALIZABLE mode, which Greenplum Database does not fully support, guarantees that a set of transactions run concurrently produces the same result as if the transactions ran sequentially one after the other. If
SERIALIZABLE is specified, Greenplum Database falls back to
REPEATABLE READ. The MVCC Snapshot Isolation (SI) model prevents dirty reads, non-repeatable reads, and phantom reads without expensive locking, but there are other interactions that can occur between some
SERIALIZABLE transactions in Greenplum Database that prevent them from being truly serializable. These anomalies can often be attributed to the fact that Greenplum Database does not perform predicate locking, which means that a write in one transaction can affect the result of a previous read in another concurrent transaction.
Note: The PostgreSQL 9.1
SERIALIZABLE isolation level introduces a new Serializable Snapshot Isolation (SSI) model, which is fully compliant with the SQL standard definition of serializable transactions. This model is not available in Greenplum Database. SSI monitors concurrent transactions for conditions that could cause serialization anomalies. When potential serialization problems are found, one transaction is allowed to commit and others are rolled back and must be retried.
Greenplum Database transactions that run concurrently should be examined to identify interactions that may update the same data concurrently. Problems identified can be prevented by using explicit table locks or by requiring the conflicting transactions to update a dummy row introduced to represent the conflict.
SET TRANSACTION ISOLATION LEVEL statement sets the isolation mode for the current transaction. The mode must be set before any
BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; ... COMMIT;
The isolation mode can also be specified as part of the
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
The default transaction isolation mode can be changed for a session by setting the default_transaction_isolation configuration property.
Updating or deleting a row leaves an expired version of the row in the table. When an expired row is no longer referenced by any active transactions, it can be removed and the space it occupied can be reused. The
VACUUM command marks the space used by expired rows for reuse.
When expired rows accumulate in a table, the disk files must be extended to accommodate new rows. Performance degrades due to the increased disk I/O required to run queries. This condition is called bloat and it should be managed by regularly vacuuming tables.
VACUUM command (without
FULL) can run concurrently with other queries. It marks the space previously used by the expired rows as free, and updates the free space map. When Greenplum Database later needs space for new rows, it first consults the table’s free space map to find pages with available space. If none are found, new pages will be appended to the file.
FULL) does not consolidate pages or reduce the size of the table on disk. The space it recovers is only available through the free space map. To prevent disk files from growing, it is important to run
VACUUM often enough. The frequency of required
VACUUM runs depends on the frequency of updates and deletes in the table (inserts only ever add new rows). Heavily updated tables might require several
VACUUM runs per day, to ensure that the available free space can be found through the free space map. It is also important to run
VACUUM after running a transaction that updates or deletes a large number of rows.
VACUUM FULL command rewrites the table without expired rows, reducing the table to its minimum size. Every page in the table is checked, and visible rows are moved up into pages which are not yet fully packed. Empty pages are discarded. The table is locked until
VACUUM FULL completes. This is very expensive compared to the regular
VACUUM command, and can be avoided or postponed by vacuuming regularly. It is best to run
VACUUM FULL during a maintenance period. An alternative to
VACUUM FULL is to recreate the table with a
CREATE TABLE AS statement and then drop the old table.
You can run
VACUUM VERBOSE tablename to get a report, by segment, of the number of dead rows removed, the number of pages affected, and the number of pages with usable free space.
pg_class system table to find out how many pages a table is using across all segments. Be sure to
ANALYZE the table first to get accurate data.
SELECT relname, relpages, reltuples FROM pg_class WHERE relname='<tablename>';
Another useful tool is the
gp_bloat_diag view in the
gp_toolkit schema, which identifies bloat in tables by comparing the actual number of pages used by a table to the expected number. See “The gp_toolkit Administrative Schema” in the Greenplum Database Reference Guide for more about
Parent topic:Greenplum Database Concepts