PREPARE TRANSACTION
Function
In a distributed database (DDB), when a transaction involves multiple nodes or data sources, the 2PC protocol is required to ensure that all participants either commit or roll back the transaction. The PREPARE TRANSACTION command is used to complete the first phase of 2PC, which persists the status of the current transaction to the disk, causing it to enter the prepared state.
After this command is executed, the transaction is no longer associated with the current session, and its status is completely stored on the disk. Even if the database crashes after this point but before the formal commit request, the prepared transaction still has a very high success rate of being committed after the database is restored.
Once a transaction enters the prepared state, it can be committed or rolled back later using the COMMIT PREPARED or ROLLBACK PREPARED command, respectively. These two commands can be issued from any session, not limited to the session where the transaction was initially executed.
Typical scenarios:
- Cross-node distributed transactions: A transaction involves multiple database nodes. In this case, the coordinator must first instruct each participant to enter the prepared state, and then uniformly decide whether to commit or roll back the transaction.
- Cross-system data consistency: A transaction involves collaborative operations between the database and external systems (such as message queues and caches). After the external systems confirm that they are ready, the transaction is finally committed through COMMIT PREPARED.
- Fault recovery: If the coordinator fails during the two-phase commit process, the participants enter the prepared state and hold resources. After the coordinator recovers, it sends a commit or rollback instruction.
From the perspective of the session that issues the PREPARE TRANSACTION statement, the current transaction ends immediately after the statement is executed, and there is no active current transaction. In addition, the data changes of the prepared transaction are invisible in the session. (The data changes will be visible again when the transaction is finally committed.)
If the PREPARE TRANSACTION statement fails to be executed due to some reasons, the current transaction will be automatically rolled back.
Precautions
- PREPARE TRANSACTION is usually automatically called by the distributed transaction coordinator. Generally, users should not explicitly use this statement in applications. This syntax is for reference only.
- The function is only available when GUC parameter xc_maintenance_mode is on. Exercise caution when enabling the mode. It is used by maintenance engineers for troubleshooting. Common users should not use the mode.
- When running the PREPARE TRANSACTION command, increasing the value of max_prepared_transactions in configuration file postgresql.conf. You are advised to set max_prepared_transactions to a value not less than that of max_connections so that one pending prepared transaction is available for each session.
Syntax
1 | PREPARE TRANSACTION transaction_id; |
Parameter Description
| Parameter | Description | Value Range |
|---|---|---|
| transaction_id | Identifier of the transaction to be submitted, which is used to identify the transaction in COMMIT PREPARED or ROLLBACK PREPARED. The identifier is the global transaction ID (GID). | The value is a string of less than 200 bytes. It cannot be the same as the identifier used by other prepared transactions. Example: 'txn_transfer_001'. |
Example
The following example uses the bank transfer service as the background to demonstrate the syntax and usage of PREPARE TRANSACTION and the method of verifying prepared transactions.
In the DWS distributed environment, PREPARE TRANSACTION is automatically invoked by the internal distributed transaction coordinator and cannot be manually executed on the client. The following example describes the syntax of PREPARE TRANSACTION. In the actual environment, prepared transactions are automatically created by the database. For details about how to view and terminate prepared transactions that are automatically created, see COMMIT PREPARED and ROLLBACK PREPARED.
- (Optional) Clear the residual prepared transaction with the same name. If the PREPARE TRANSACTION statement with the same GID has been executed but has not been committed or rolled back, clear it first.
1 2 3 4
SET xc_maintenance_mode = on; SELECT transaction, gid FROM pg_prepared_xacts WHERE gid = 'txn_transfer_001'; -- If there are residual records, run the following command to clear them: ROLLBACK PREPARED 'txn_transfer_001';
- Create a test table and insert initial data into the table.
1 2 3 4 5 6 7 8
DROP TABLE IF EXISTS accounts; CREATE TABLE accounts ( id INT PRIMARY KEY, name VARCHAR(50), balance NUMERIC(10,2) ); INSERT INTO accounts VALUES (1, 'Alice', 1000.00); INSERT INTO accounts VALUES (2, 'Bob', 500.00);
- Start a transaction and perform a transfer: Alice transfers 200 dollars to Bob.
1 2 3
BEGIN; UPDATE accounts SET balance = balance - 200.00 WHERE id = 1; UPDATE accounts SET balance = balance + 200.00 WHERE id = 2;
- Run PREPARE TRANSACTION to set the current transaction to the prepared state and specify the global transaction identifier as 'txn_transfer_001'. (This is for reference only. The client cannot execute this statement.)
1PREPARE TRANSACTION 'txn_transfer_001';
After the execution is successful, the current session no longer has an active transaction.
- Verify that the transaction has entered the prepared state. Query the pg_prepared_xacts system view to confirm that the prepared transaction has been created and obtain its transaction identifier (value of the gid column).
1SELECT transaction, gid, prepared, owner, database FROM pg_prepared_xacts;
Expected output:
1 2 3 4
transaction | gid | prepared | owner | database ------------+------------------+------------------------+----------+---------- 1234567 | txn_transfer_001 | 2024-01-15 14:30:00+08 | app_user | db_test (1 row)
The value txn_transfer_001 of the gid column is the transaction_id that needs to be specified when you run the COMMIT PREPARED or ROLLBACK PREPARED statement.
- Verify that the data changes of the prepared transaction are invisible in the current session (because the transaction has not been committed).
1SELECT * FROM accounts ORDER BY id;
Expected output:
1 2 3 4 5
id | name | balance ----+-------+--------- 1 | Alice | 1000.00 2 | Bob | 500.00 (2 rows)
For subsequent operations, see COMMIT PREPARED (committing a prepared transaction) or ROLLBACK PREPARED (rolling back a prepared transaction).
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.