COMMIT PREPARED
Function
In the two-phase commit (2PC) process of a distributed database, a transaction is committed in two phases. In the first phase, PREPARE TRANSACTION is executed to set the transaction to the prepared state. In the second phase, COMMIT PREPARED or ROLLBACK PREPARED is executed to commit or roll back the transaction. When the first phase of the prepared transaction is successfully completed and all participants confirm that the transaction can be committed, you can use the COMMIT PREPARED statement to complete the final commit of the transaction, making the data changes in the transaction persistent and visible to all sessions.
Typical applications include:
- Normal commit of a distributed transaction: The first phase (PREPARE) of the two-phase commit has been successfully completed, all participants have replied that the transaction can be committed, and the coordinator sends the COMMIT PREPARED statement to complete the final commit.
- Commit after fault recovery: The coordinator fails after the first phase. After the coordinator is recovered, it confirms that the prepared transactions of all participants are still available and sends the COMMIT PREPARED statement to complete the delayed commit.
- Cross-session commit: The prepared transaction is created by one session (using the PREPARE TRANSACTION statement) and committed by another session (using the COMMIT PREPARED statement). This is applicable to the scenario where the coordinator and participants are not in the same session.
Precautions
- The function is available only in maintenance mode (when the GUC parameter xc_maintenance_mode is set to on). This mode is used by maintenance personnel to locate faults. Generally, users are not allowed to use this mode.
- Only the transaction creators or system administrators can run the COMMIT command. The creation and commit operations must be in different sessions.
- The transaction function is maintained automatically by the database, and should not be used explicitly.
Syntax
1 2 | COMMIT PREPARED transaction_id ; COMMIT PREPARED transaction_id WITH CSN; |
Parameter Description
| Parameter | Description | Value Range |
|---|---|---|
| transaction_id | Specifies the identifier of the prepared transaction to be committed. The identifier is the global transaction ID (GID) specified when the PREPARE TRANSACTION statement is executed. It must be the identifier of a prepared transaction that already exists. | The value is a string of less than 200 bytes. Example: 'txn_transfer_001'. |
| CSN(commit sequence number) | Specifies the commit sequence number (CSN) of the transaction to be committed. It is a 64-bit unsigned number. When the WITH CSN syntax is used, the system commits the transaction based on the specified CSN. | The value is a 64-bit unsigned integer. You can obtain the value by querying the pg_prepared_xacts view or transaction-related system views. |
Example
The following example demonstrates how to query residual prepared transactions created in the database and use COMMIT PREPARED to commit the transactions in the DWS O&M.
- Enable the maintenance mode. (Enabling the maintenance mode may cause data inconsistency. Contact technical support before enabling the maintenance mode.)
1SET xc_maintenance_mode = on;
- Query the prepared transactions that exist currently and obtain the identifier (value of the gid column) of the transaction to be committed.
1SELECT transaction, gid, prepared, owner, database FROM pg_prepared_xacts;
Expected output (assuming that there is a residual prepared transaction):
1 2 3 4
transaction | gid | prepared | owner | database ------------+------------------+------------------------+----------+---------- 1234567 | txn_distribute_01| 2024-01-15 14:30:00+08 | app_user | db_test (1 row)
The value txn_distribute_01 of the gid column is the transaction_id that needs to be specified in the COMMIT PREPARED statement.
- Execute the COMMIT PREPARED statement to commit the prepared transaction whose GID is 'txn_distribute_01'.
1COMMIT PREPARED 'txn_distribute_01';
- Verify whether the prepared transaction has been successfully committed. Query the pg_prepared_xacts view again to confirm that the target transaction no longer exists.
1SELECT transaction, gid FROM pg_prepared_xacts WHERE gid = 'txn_distribute_01';
Expected output (If the query result is empty, the prepared transaction has been committed and terminated):
1 2 3
transaction | gid ------------+----- (0 rows)
- Disable the maintenance mode.
1SET xc_maintenance_mode = off;
Helpful Links
FAQ
Q: How Do I Query the Existing Prepared Transactions?
A: You can query the pg_prepared_xacts system view to view all currently prepared transactions.
1 | SELECT transaction, gid, prepared, owner, database FROM pg_prepared_xacts; |
The value of the gid column is the identifier of each prepared transaction, which is used for subsequent COMMIT PREPARED or ROLLBACK PREPARED operations.
Q: What Is the Difference Between COMMIT PREPARED and COMMIT PREPARED WITH CSN?
A: COMMIT PREPARED commits a prepared transaction in the default mode. COMMIT PREPARED WITH CSN allows you to specify a commit sequence number (CSN), and the system commits the transaction based on the specified CSN order. The WITH CSN syntax is typically used in scenarios where precise control over the transaction commit order is required. Generally, the default COMMIT PREPARED is sufficient.