1. Scaleout User's Guide
  2. Understanding Distributed Transactions in TimesTen Scaleout
  3. Troubleshooting Distributed Transactions

Troubleshooting Distributed Transactions

In TimesTen Classic, a transaction may need to wait for a resource held by another transaction. If that resource is protected by a lock, the transaction waits for the lock to be released. It is possible that the other transaction is waiting on an external event that is not represented as database lock, so the deadlock detector does not resolve the problem. The following are possible resources that can cause a transaction to wait:

  • 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.18.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

Rolling Back an In-Doubt Transaction

The example uses the ttXactAdmin utility to roll back transaction 3.1.148. 

% ttXactAdmin -connStr "DSN=database1" -xactIdRollback 3.1.148