Updated on 2026-11-12 GMT+08:00

ROLLBACK PREPARED

Function

In the 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. If a prepared transaction fails to enter the second phase for a long time due to node faults, network exceptions, or application errors, it will hold locks and system resources, affecting the execution of other transactions. In this case, you can use the ROLLBACK PREPARED statement to cancel the prepared transaction and release the occupied resources.

Typical applications include:

  • After a node participating in a distributed transaction breaks down, the prepared transaction is not terminated and needs to be manually rolled back and cleared.
  • The two-phase commit is interrupted due to abnormal exit of the application layer. The prepared transaction remains and needs to be manually handled.
  • When maintenance personnel check the two-phase commit problem, they need to roll back a specific prepared transaction to restore the system to the normal state.
  • After the first phase of the two-phase commit, a participating node reports that the transaction cannot be committed. The coordinator decides to roll back the entire transaction.

Precautions

  • The function is only available in maintenance mode (when GUC parameter xc_maintenance_mode is on). This mode is used by maintenance personnel to locate faults. Generally, users are not allowed to use this mode.
  • Only the user that initiates a transaction or the system administrator can roll back the transaction.
  • The transaction function is maintained automatically by the database, and should not be used explicitly. The ROLLBACK PREPARED statement is used to clear the residual prepared transactions created in the database. In the distributed architecture of DWS, prepared transactions are automatically created by the internal distributed transaction coordinator (GTM). Therefore, you cannot manually execute the PREPARE TRANSACTION statement on the client. O&M personnel can query the pg_prepared_xacts view to view residual prepared transactions and use the ROLLBACK PREPARED statement to clear them.

Syntax

1
ROLLBACK PREPARED transaction_id ;

Parameter Description

Table 1 ROLLBACK PREPARED parameters

Parameter

Description

Value Range

transaction_id

Specifies the identifier of the prepared transaction to be rolled back. 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 up to 256 characters and cannot conflict with the identifiers of other prepared transactions. Example: 'txn_transfer_002'.

Example

The following example demonstrates the most common scenario in DWS O&M: querying orphaned prepared transactions created in the database and using the ROLLBACK PREPARED command to roll back and clear the transactions.

  1. Enable the maintenance mode (Enabling the maintenance mode may cause data inconsistency. Contact technical support before enabling the maintenance mode).

    1
    SET xc_maintenance_mode = on;
    

  2. Query all current orphaned prepared transactions and obtain the identifiers (values of the gid column) of the transactions to be rolled back.

    1
    SELECT transaction, gid, prepared, owner, database FROM pg_prepared_xacts;
    

    Expected output (assuming that there are two residual prepared transactions):

    1
    2
    3
    4
    5
    transaction |       gid        |        prepared         |  owner   | database
    ------------+------------------+------------------------+----------+----------
         1234567 | txn_distribute_01| 2024-01-15 14:30:00+08 | app_user | db_test
         1234568 | txn_distribute_02| 2024-01-15 15:00:00+08 | app_user | db_test
    (2 rows)
    

    The value of the gid column is the identifier of each prepared transaction, which is used for the ROLLBACK PREPARED operation.

  3. Roll back the first residual prepared transaction.

    1
    ROLLBACK PREPARED 'txn_distribute_01';
    

  4. Verify whether the prepared transaction has been successfully rolled back.

    1
    SELECT transaction, gid FROM pg_prepared_xacts WHERE gid = 'txn_distribute_01';
    

    Expected output (if the query returns no result, the rollback is successful):

    1
    2
    3
    transaction | gid
    ------------+-----
    (0 rows)
    

  5. Disable the maintenance mode.

    1
    SET xc_maintenance_mode = off;
    

FAQ

What Should I Do if the Error Message "ERROR: prepared transaction with identifier xxx does not exist" Is Displayed When I Run the ROLLBACK PREPARED Statement?

A: This error indicates that the specified transaction ID does not exist in the current prepared transaction list. The possible causes are as follows:

  • The identifier is misspelled or the case does not match.
  • The prepared transaction has been committed or rolled back by another session.
  • The prepared transaction was never successfully created.

You can query the pg_prepared_xacts view to confirm the existing prepared transactions and their correct GID values. Note that the identifier is case-sensitive and must be enclosed in single quotation marks.

What Is the Difference Between ROLLBACK PREPARED and ROLLBACK?

A: ROLLBACK is used to roll back an active ordinary transaction in the current session. ROLLBACK PREPARED is used to roll back a transaction that has been prepared by PREPARE TRANSACTION. A prepared transaction has been separated from the session that created it and must be rolled back using ROLLBACK PREPARED (in maintenance mode). The ordinary ROLLBACK statement cannot affect the prepared transaction.

Error Message "Explicit prepare transaction is not supported" Is Displayed When ROLLBACK PREPARED Is Executed

A: The maintenance mode is not enabled. Run the following command to enable it:

SET xc_maintenance_mode = on;