Oracle7 Server Distributed Systems Volume I: Distributed Data
Manually Committing In-Doubt Transactions
The local database administrator has two ways to manually force an in-doubt transaction to commit. The DBA can use the Server Manager Transaction Object List option Force Commit or the SQL command COMMIT with the FORCE option and a text string, indicating either the local or global transaction ID of the in-doubt transaction to commit. Figure 5 - 9 shows the Server Manager Transaction Object List.
Figure 5 - 9. Transaction Object List
Forcing a Commit or Rollback in Server Manager
To commit an in-doubt transaction, select the transaction from the Transaction Object List and choose Force Commit from the Transaction menu.
To roll back an in-doubt transaction, select the transaction from the Transaction Object List and choose Force Rollback from the Transaction menu.
Attention: You cannot roll back an in-doubt transaction to a savepoint.
Manually Committing or Rolling Back In-Doubt Transactions
The following SQL statement is the command equivalent of the action taken in Figure 5 - 9 to commit an in-doubt transaction.
COMMIT FORCE 'transaction_name';
To manually rollback an in-doubt transaction, use the SQL command ROLLBACK with the FORCE option and a text string, indicating either the local or global transaction ID of the in-doubt transaction to rollback. For example, to rollback the in-doubt transaction with the local transaction ID of 2.9.4, use the following statement:
ROLLBACK FORCE '2.9.4';
Attention: You cannot roll back an in-doubt transaction to a savepoint.
Privileges Required to Manually Commit or Rollback In-Doubt Transactions
To manually force the commit or rollback of an in-doubt transaction issued by yourself, you must have been granted the FORCE TRANSACTION system privilege. To force the commit or rollback of another user's distributed transaction, you must have the FORCE ANY TRANSACTION system privilege. Both privileges can be obtained either explicitly or via a role.
Note: Forcing the commit or rollback of an in-doubt distributed transaction does not affect the status of the operator's current transaction.
Forcing Rollback/Commit on the Local Pending Transaction Table
In all examples, the transaction is committed or rolled back on the local node, and the local pending transaction table records a value of forced commit or forced abort for the STATE column of this transaction's row.
Specifying the SCN
Optionally, you can specify the SCN for the transaction when forcing a transaction to commit. This feature allows you to commit an in-doubt transaction with the SCN assigned when it was committed at other nodes. Thus you maintain the synchronized commit time of the distributed transaction even if there is a failure. Specify an SCN only when you can determine the SCN of the same transaction already committed at another node.
For example, assume you want to manually commit a transaction with the global transaction ID global_id. First, query the DBA_2PC_PENDING view of a remote database also involved with the transaction in question. Note the SCN used for the commit of the transaction at that node. Specify the SCN (a decimal number) when committing the transaction at the local node. For example, if the SCN were 829381993, you would use the command:
COMMIT FORCE 'global_id', 829381993;