Troubleshooting Distributed Transactions
-
A semaphore wait
-
A latch wait
-
An I/O event
-
An unattended open transaction
-
A long running operation
In TimesTen Scaleout, these cases still apply, and there is an additional possible case. When an element fails, all the transactions initiated from that element have lost their transaction manager. If the remote participants did not receive the commit decision for a transaction after sending their prepare response, then the participants must wait to commit or rollback the now in-doubt transaction. Also, if a participant fails after sending its prepare response but before receiving the commit decision, the transaction becomes an in-doubt transaction for the failed participant.
Global Transaction ID
The global transaction ID uniquely identifies a transaction across all the elements of a database. The global transaction ID is composed of these parameters:
-
The element ID of the transaction manager
-
The connection ID of the transaction manager or local transaction ID
-
A counter for the transactions issued from the connection
The following example shows how to retrieve the global transaction ID from within the
connection issuing the transaction. The SYS.V$XACT_ID
system view
stores all the parameters necessary to construct the global transaction ID of a
transaction.
Command> autocommit 0;
Command> INSERT INTO transactions VALUES (txn_seq.NEXTVAL, 342, SYSDATE, NULL, 'A', 8.33);
1 row inserted.
Command> SELECT elementId, xactId, counter FROM sys.v$xact_id;
< 3, 1, 148 >
1 row found.
For more information on the SYS.V$XACT_ID
system view, see SYS.V$XACT_ID in Oracle TimesTen In-Memory Database System Tables
and Views Reference.
Managing In-Doubt Transactions
TimesTen Scaleout resolves in-doubt transactions automatically during element recovery. The prepare-to-commit log record of the transaction contains the information about other participants. To resolve the in-doubt transaction, the recovering element requests the commit decision from one of the participants listed in the prepare-to-commit log record.
In the case of a transaction manager failure, TimesTen Scaleout should be able to
resolve an in-doubt transaction as long as one participant from each write replica set
is available. However, if none of the participants have the commit decision and not all
write replica sets are available, TimesTen Scaleout cannot resolve the in-doubt
transaction. If TimesTen Scaleout failed to resolve an in-doubt transaction, use the
ttXactAdmin
utility to force the commit or rollback of the
transaction.
Note:
For most cases, you should always roll back an unresolved in-doubt transaction. However, if you decide to externally commit the transaction, you first will need to evict any unreachable participating replica set to ensure a consistent database. Evicting a replica set implies losing all the data stored in that replica set. See Recovering When the Replica Set Has a Permanently Failed Element.
You may use the ttXactAdmin
utility to verify the state
of every outstanding transaction, as shown in Verifying the State of Every Outstanding Transaction. If the transaction state
is in-doubt
, you can externally commit or rollback the transaction with the same utility, as shown in Committing an In-Doubt Transaction or Rolling Back an In-Doubt Transaction, respectively.
For more information on the ttXactAdmin
utility, see ttXactAdmin
in Oracle TimesTen In-Memory Database
Reference.
Verifying the State of Every Outstanding Transaction
This example shows how to retrieve the status of every outstanding transaction that the element of the data instance running the command is a participant. The ttXactAdmin
utility only retrieves information related to the element of the data instance executing the command.
% ttXactAdmin -connStr "DSN=database1"
2016-12-14 11:00:36.995
/disk1/databases/database1
TimesTen Release 22.1.1.27.0
ElementID 3
Program File Name: _ttIsql
XactID PID Context State Loghold Last ID
3.1.148 26247 0x13b3ff0 Active -1.-1 [-1:2]
Resource ResourceID Mode SqlCmdID Name
Database 0x01312d0001312d00 IX 0
HashedKey ffffffffe5a341d5 SF 284478280 PAT.ACCOUNTS
Table 2367304 IRC 284478280 PAT.ACCOUNTS
EndScan AAAVVUAAAA9AAAAGjO En 284478280 PAT.TRANSACTIONS
Table 2367320 IRC 284478280 PAT.TRANSACTIONS
Begin Time: 10:59:21.695
Committing an In-Doubt Transaction
The example uses the ttXactAdmin
utility to commit transaction 3.1.148. This command can only be successfully run if the transaction manager is down and its replica set is evicted from the database. See Recovering from a Down Replica Set for more information on when and how to evict a failed replica set.
% ttXactAdmin -connStr "DSN=database1" -xactIdCommit 3.1.148