6 Understanding Distributed Transactions in TimesTen Scaleout

Status of the participants

When a participant completes the execution of a statement, it sends a message to the transaction manager. The message includes information about the number of rows affected. If the message specifies that:

• The participant modified the affected rows, such as with a INSERT, UPDATE, or DELETE operation, the transaction manager flags the participant as a write participant.

• The participant did not modify any rows, then the transaction manager flags the participant as a read participant.

The read or write status of a participant affects the way the transaction manager processes a commit operation:

• If all participants are read participants, then the transaction manager handles the commit without going through the prepare phase. In other words, read participants perform the commit operation without needing a consensus from the other participants.

• If there are one or more write participants, then the transaction manager handles the commit as a two-phase operation.

Durability set to 1

If you set the Durability attribute to 1, participants write durable prepare-to-commit log records and nondurable commit log records for distributed transactions. Having the Durability attribute set 1 ensures that committed transactions are recoverable in the case of a failure. This is the default setting of the Durability attribute when K-safety is set to 1.

For more information on the Durability attribute, see "Durability" in the Oracle TimesTen In-Memory Database Reference.

Durability set to 0

If you set the Durability attribute to 0, participants write nondurable prepare-to-commit and commit log records for distributed transactions. To ensure a measure of durability, TimesTen Scaleout provides the following new features that are generally exclusive to databases with the Durability attribute set to 0:

• Epoch transactions

• EpochInterval attribute

• CreateEpochAtCommit attribute

Command> autocommit OFF;
Command> INSERT INTO transactions VALUES (txn_seq.NEXTVAL, 189, SYSDATE, NULL,
 'A', 5.49);
Command> SELECT epoch FROM sys.v$epoch_session;
< 1023.1 >
1 row found.
Command> CALL ttEpochCreate();
Command> COMMIT;
Command> SELECT epoch FROM sys.v$epoch_session;
< 1024.1 >
1 row found.

Phase 0: Transaction

1. An application establishes a connection to a database. Every connection is associated with a specific element of the database, which becomes the transaction manager for all distributed transactions initiated from that connection.

2. The application executes one or more SQL statements. The transaction manager sends the statements to all the participants for execution. Based on the returned results of the execution of the SQL statement, the transaction manager identifies and updates the status of the participants.

3. The application issues a commit.

Phase 1: Prepare phase

1. The transaction manager sends a prepare message to all participants. The message includes the identity of the transaction manager and all the participants.

2. Each participant, once it receives the prepare message, performs either of these actions:

   • If the participant is a write participant, it writes a prepare-to-commit log record that stores information to subsequently either commit or rollback the transaction. The participant also locks the modified rows to prevent read operations.

   • If the participant is a read participant, it identifies the transaction as read-only.

3. The participant sends a prepare response to the transaction manager with its vote for the commit decision:

   • A write participant only votes 'Yes' if it was able to write the prepare-to-commit log record.

     Note:

     If Durability is set to 1, the participant writes a durable prepare-to-commit log record.

   • A read participant always votes 'Yes' and commits the transaction without waiting for the commit decision. In this case, the commit operation consists on releasing all locks and temporary resources related to the transaction.

Phase 2: Commit phase

1. Once the transaction manager receives the prepare response from at least one element in every replica set participating in the transaction, it writes a prepare-to-commit log record that includes the commit decision. The transaction manager bases the commit decision on the scenarios described in Table 6-2.

   Table 6-2 Scenarios for commit decision

   Scenarios	Decision
   All write participants send a 'Yes' vote in their prepare response and within them there is at least one element for each participating replica set. (Failed participants do not affect the commit decision once they are identified as failed as long as its replica sends a response.)	Commit
   Any write participant sends a 'No' vote in their prepare response.	Roll back

   
   

2. The transaction manager sends a message to all write participants with the commit decision.

3. All write participants, including the transaction manager, commit or rollback the transaction based on the commit decision.

Figure 6-1 shows the two-phase commit protocol as implemented for distributed transactions in TimesTen Scaleout.

Figure 6-1 Two-phase commit protocol

Description of ''Figure 6-1 Two-phase commit protocol''

Two-phase commit failure analysis

There are several types of potential failures that may affect the operation of a database for outstanding distributed transactions. Table 6-3 summarizes these failure types and describes how TimesTen Scaleout responds to them.

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

See Example 6-2 for details on how to retrieve the global transaction id of an outstanding transaction in TimesTen Scaleout.

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 the 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.

You may use the ttXactAdmin utility to verify the state of every outstanding transaction, as shown in Example 6-3. If the transaction state is in-doubt, you can externally commit or rollback the transaction with the same utility, as shown in Example 6-4 or Example 6-5, respectively.

% ttXactAdmin -connStr "DSN=database1"
2016-12-14 11:00:36.995
/disk1/databases/database1
TimesTen Release 18.1.4.1.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

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

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

For more information on the ttXactAdmin utility, see "ttXactAdmin" in the Oracle TimesTen In-Memory Database Reference.