dbcc complete_xact enables a System Administrator to commit or roll back a distributed transaction in circumstances where the external transaction coordinator cannot. In earlier versions of Adaptive Server, a transaction could not heuristically complete unless it was in the “prepare” state, and the transaction coordinator used a two-phase commit protocol to commit the transaction. However, in some cases, a transaction coordinator may want to use a one-phase commit protocol as an optimization to commit the transaction.
Adaptive Server 12.5.2 includes the 1pc parameter to the dbcc complete_xact command. 1pc heuristically completes a transaction that was subject to a one-phase commit protocol optimization—instead of the regular two-phase commit protocol—by the external transaction manager that was coordinating its completion. Heuristically committing such a transaction requires that the transaction is in a “done” state (as reported by sp_transactions).
The partial syntax for dbcc complete_xact is:
dbcc complete_xact("<xid>", "commit", "1pc")
Before heuristically committing the transaction, the
System Administrator should make every effort to determine whether
the coordinating transaction manager committed the distributed transaction.
The following example uses sp_transactions to determine the name of a one-phase commit transaction that did not heuristically commit because it was not in a “prepared” state. The example then explains how to use the 1pc parameter to successfully commit the transaction:
sp_transactions
xactkey type coordinator starttime state connection dbid spid loid failover srvnname namelen xactname ------------------------------ --------- ----------- -------------------- --------------- ---------- ------ ------ ----------- ---------- ----------- ----------------- ------------- 0xbc0500000b00000030c316480100 External XA Feb 2 2004 1:07PM Done-Detached Detached 1 0 2099 Resident Tx NULL 88 28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM000FU_00003M0000Y_:SYBBEV0A_LRM (1 row affected)(return status = 0)
If you try to commit this transaction:
dbcc complete_xact("28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM000FU_00003M0000Y_:SYBBEV0A_LRM", "commit"))
Adaptive Server issues the following error message:
Msg 3947, Level 16, State 1: Server 'PISSARRO_1251_P', Line 1: A heuristic completion related operation failed. Please see errorlog for more details. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Because the transaction is in a “done” state, you can use a one-phase commit protocol optimization to heuristically complete the transaction after verifying the transaction was committed. You can commit this transaction using the dbcc complete_xact(“1pc”) parameter:
dbcc complete_xact("28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM000FU_00003M0000Y_:SYBBEV0A_LRM", "commit", "1pc")
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
You can remove the transaction from systransactions with the dbcc forget_xact command:
dbcc forget_xact("28_u7dAc31Wc380000000000000000000000000000000001HFpfSxkDM0
00FU_00003M0000Y_:SYBBEV0A_LRM")
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
If you run sp_transactions again, the previous transaction does not appear:
sp_transactions
xactkey type coordinator starttime state connection dbid spid loid failover srvnname namelen xactname ------------------------------ --------- ----------- -------------------- --------------- ---------- ------ ------ ----------- ------------- (0 row affected)
| Copyright © 2004. Sybase Inc. All rights reserved. |
|
|
