In TimesTen Scaleout, distributed transactions are processed by a two-phase commit protocol. This chapter discusses how TimesTen Scaleout maintains ACID-compliant databases through distributed transactions.
The following terminology is related to understanding the distributed transaction processing algorithms that TimesTen Scaleout employs:
Participant: An element that executes one or more SQL statements from a distributed transaction. Not all elements in a database participate in every transaction. An element only becomes a participant of a transaction if one or more operations of that transaction requires access to the data stored in the element.
Note:Each element maintains its own independent set of checkpoint and transaction log files. They behave in the same manner as the checkpoint and transaction log files of a database in TimesTen Classic. See "Checkpoint operations" and "Transaction logging" in the Oracle TimesTen In-Memory Database Operations Guide for more information.
Transaction manager: The thread of the application (or of the TimesTen Server, for a client/server application) that is connected to the database and initiates the transaction. The transaction manager coordinates the transaction operations with all participants.
Prepare-to-commit log record: A type of log record written to the transaction log of the database during the prepare phase of the two-phase commit protocol. It contains the commit decision for the transaction.
Durable log record: Participants write a prepare-to-commit or commit log record synchronously to the transaction log. Nondurable log records are asynchronously written by the participants.
Distributed transaction: A transaction with two or more participants.
Single-element transaction: A transaction with only one participant. Single-element transactions do not use the two-phase commit protocol. Single-element transactions are only possible in a grid with K-safety set to
In-doubt transaction: A transaction where a participant wrote a prepare-to-commit log record, but the commit log record is not present in the transaction log. If the transaction manager wrote the prepare-to-commit log record to the transaction log, which means there is a known commit decision, then the transaction is not in-doubt.
This chapter includes the following topics:
Applications connect to a database in TimesTen Scaleout by connecting to one element of the database. Each transaction executed by a connection requires a transaction manager. For client/server applications the transaction manager is the thread in the TimesTen Scaleout server that is acting as a proxy for the application. For direct mode applications the transaction manager is the thread in the application that connects to TimesTen Scaleout. The transaction manager coordinates the execution of statements on elements (participants), or more specifically:
If the application issues a commit or rollback, the transaction manager ensures that all participants have consistent data based on the commit or rollback decision from the two-phase commit protocol.
If a participant returns an error, such as a constraint violation, the transaction manager coordinates the response. The transaction manager ensures that TimesTen Scaleout returns the appropriate error message to the user and that all participants release the allotted resources.
If a participant fails, the transaction manager creates a state that the failed participant uses during its recovery to restore to a consistent state.
If the participant where the transaction manager resides fails, participants classify the transaction as in-doubt if they completed the prepare phase but did not receive the commit decision and are no longer able to reach the transaction manager.
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
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.
You control how durable your transactions are with the
Durability attribute. This attribute defines if transactions create durable prepare-to-commit log records. Regardless of the setting of this attribute, transactions that include DDL statements create durable prepare-to-commit and commit log records. The
Durability attribute supports two different values:
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
For more information on the
Durability attribute, see "Durability" in the Oracle TimesTen In-Memory Database Reference.
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
An epoch transaction is a distributed transaction that creates a durable commit log record that marks a globally consistent point in time across all elements of a database. Epoch transactions are durably committed on every element of the database. An epoch transaction ensures that the database is consistent up to the timestamp of the epoch transaction. In other words, an epoch transaction ensures that any transaction already in the commit phase is recoverable.
Note:TimesTen Scaleout uses Lamport timestamps to provide partial ordering for transactions that commit on different elements of a database. Each element has a Lamport timestamp that is updated by, among others, prepare and commit operations. The transaction manager logs the Lamport timestamp of every committed transaction.
Transactions in a grid with K-safety set to
2 and a database with the
Durability attribute set to
0 are durable under normal conditions, since TimesTen Scaleout writes durable prepare-to-commit log records of transactions that involve a replica set with a failed element until the failed element recovers. Only if both elements of the replica set fail simultaneously, a transaction may become nondurable. However, TimesTen Scaleout enables you to promote transactions to epoch transactions. An epoch transactions and all transactions committed before it are more resilient to catastrophic failures, since you can recover a database to the consistent point marked by the epoch commit log record of the epoch transaction.
See "Recovering a replica set after an element goes down" for more information on how to recover failed element in a replica set.
See "Recovering from a down replica set" for more information on how to recover a failed replica set.
Before promoting a transaction, consider that a commit for an epoch transaction is more expensive than a commit for a regular transaction, because it creates durable log records for both the prepare-to-commit and commit phase and involves every element of the database, including those that were not participants before the promotion of the transaction to an epoch transaction.
Use these built-in procedures and system view to promote and manage epoch transactions:
ttDurableCommit built-in procedure promotes a write transaction to an epoch transaction.
SYS.V$EPOCH_SESSION system view stores the Lamport timestamp of the latest epoch transaction that the connection created since the second-to-last checkpoint operation.
The example shows and verifies the promotion of a write transaction to an epoch transaction.
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.
For more information on the
SYS.V$EPOCH_SESSION system view, see "SYS.V$EPOCH_SESSION" in the Oracle TimesTen In-Memory Database System Tables and Views Reference.
Each epoch commit log record is associated to a specific checkpoint file on every element. In the case of an unexpected failure of an element, the recovery process must use the checkpoint file on each element that is associated with the latest epoch commit log record, which is not necessarily the latest checkpoint available on the element.
You can configure a database to generate periodic epoch transactions at an specified interval with the
EpochInterval first connection attribute. The value set for the
EpochInterval attribute must be less than one half of the value set for the
CkptFrequency first connection attribute, so that there is at least one epoch transaction for every checkpoint operation. If you set the
CkptFrequency attribute to a value greater than zero and the
EpochInterval attribute to a value greater than one half of the value set for the
CkptFrequency attribute, TimesTen Scaleout will re-adjust the
EpochInterval attribute to one half of value set for the
You can configure a connection to promote every write transaction committed by that connection to an epoch transaction with the
CreateEpochAtCommit general connection attribute. If you set the
CreateEpochAtCommit attribute to
1, you ensure that every transaction you commit during the connection is recoverable in the case of failure. However, as with any epoch transaction, commits operations are more expensive than with regular transactions, so it is recommended that you limit
CreateEpochAtCommit=1 for critical operations only.
Note:Even though the
DurableCommitsattribute is intended for databases in TimesTen Classic, the attribute emulates the behavior of the
CreateEpochAtCommitattribute when set to
1for a database in TimesTen Scaleout. See "DurableCommits" in the Oracle TimesTen In-Memory Database Reference.
Durability attribute is set to
0, the transaction manager and the participants behave differently depending of the settings of the
CreateEpochAtCommit attribute, as shown on Table 6-1.
Participants write nondurable prepare-to-commit and commit log records for every distributed transaction to commit.
Promotes every transaction to an epoch transaction.
Setting both the
CreateEpochAtCommit attributes to
0 provides the best performance. In this case, call the
ttDurableCommit built-in procedures to ensure that you have durable records of important transactions.
For more information on the
CreateEpochAtCommit attribute, see "Durability" or "CreateEpochAtCommit", respectively, in the Oracle TimesTen In-Memory Database Reference. For more information on the
ttDurableCommit built-in procedure, see "ttEpochCreate" or "ttDurableCommit", respectively, in the Oracle TimesTen In-Memory Database Reference.
As previously mentioned, distributed transactions follow a two-phase commit protocol. TimesTen Scaleout implements the two-phase commit protocol as follows:
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.
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.
The application issues a commit.
The transaction manager sends a prepare message to all participants. The message includes the identity of the transaction manager and all the participants.
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.
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.
Durabilityis 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.
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.
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.)
Any write participant sends a 'No' vote in their prepare response.
The transaction manager sends a message to all write participants with the commit decision.
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.
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.
Transaction manager fails.
If the transaction manager fails (for example, the application terminates), the main daemon for that instance catches the failure and informs the subdaemon. The subdaemon sends a commit or rollback message to all participants depending on the state of the transaction.
The host of the transaction manager fails.
If the host of the transaction manager fails, the daemon and all subdaemons fail. Each participant will recognize this failure when their TCP connection to the transaction manager closes or times out.
Once a participant recognizes the failure, the participant rolls back any transaction that has not reached the prepare phase. If the participant already sent its prepare response, it will ask other participants for the commit decision and perform one of the following actions:
All elements from a participating replica set fail before writing a prepare-to-commit log record.
The transaction manager decides to rollback the transaction.
Participant fails after writing a prepare-to-commit log record.
The participant, once it recovers, requests the commit decision from one of the other participants.
Participant is busy.
The transaction manager waits until it receives a prepare response from the participant.
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.
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.
This 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 the Oracle TimesTen In-Memory Database System Tables and Views Reference.
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.
Important: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 "Unavailability of data when a full replica set is down or fails" for more information on evicting replica sets.
You may use the
ttXactAdmin utility to verify the
state of every outstanding transaction, as shown in Example 6-3. If the transaction
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.
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 188.8.131.52.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
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
The example uses the
ttXactAdmin utility to roll back transaction 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.