Applications use a concept called the logical transaction identifier (LTXID) to determine the outcome of the last transaction open in a database session following an outage. The logical transaction ID is stored in the OCI session handle and in a connection object for the JDBC Thin and ODP.NET drivers. The logical transaction ID is the foundation of the at-most-once semantics.

The Transaction Guard protocol ensures that:

• Execution of each logical transaction is unique.

• Duplication is detected at supported commit time to ensure that for all commit points, the protocol must not be circumvented.

• When the transaction is committed, the logical transaction ID is persisted for the duration of the retention period for retries (default = 24 hours, maximum = 30 days).

• When obtaining the outcome, an LTXID is blocked to ensure that an earlier in-flight version of that LTXID cannot commit, by enforcing the uncommitted status. If the earlier version with the same LTXID was already committed or forced, then blocking the LTXID returns the same result.

The logical session number is automatically assigned at session establishment. It is an opaque structure that cannot be read by an application. For scalability, each LTXID carries a running number called the commit number, which is increased when a database transaction is committed for each round trip to the database. This running commit number is zero-based.

Transaction Guard uses the logical transaction identifier (LTXID) to avoid duplicate transactions. This ability to ensure at most one execution of a transaction is referred to as transaction idempotence. The LTXID is persisted on commit and is reused following a rollback. During normal runtime, an LTXID is automatically held in the session at both the client and server for each database transaction. At commit, the LTXID is persisted as part of committing the transaction.

The at-most-once protocol requires that the database maintain the LTXID for the retention period agreed for replay. The default retention period is 24 hours, although you might need a shorter or longer period, conceivably even a week or longer. The longer the retention period, the longer the at-most-once check blocks an old transaction using an old LTXID from replay. The setting is available on each service. When multiple databases are involved, as is the case when using Data Guard and Active Data Guard, the LTXID is replicated to each database involved through the use of redo.

The getLTXID API, provided for Oracle JDBC Thin (with similar APIs for OCI, OCCI, and ODP.NET clients), lets an application retrieve the logical transaction identifier that was in use on the dead session. This is needed to determine the status of this last transaction.

The DBMS_APP_CONT.GET_LTXID_OUTCOME PL/SQL subprogram lets an application find the outcome of an action for a specified logical transaction identifier. Calling DBMS_APP_CONT.GET_LTXID_OUTCOME may involve the server blocking the LTXID from committing so that the outcome is known. This is a requirement if a transaction using that LTXID is in flight or is about to commit. An application using Transaction Guard obtains the LTXID following a recoverable error, and then calls DBMS_APP_CONT.GET_LTXID_OUTCOME before attempting a replay.

You may use Transaction Guard on each database in your system, including restarting on and failing over between single instance database, Real Application Clusters, Data Guard and Active Data Guard.

Transaction Guard is supported with the following Oracle Database configurations:

• Single Instance Oracle RDBMS

• Real Application Clusters

• Data Guard

• Active Data Guard

• Multitenant including unplug/plug and relocates across the PDB/CDB, but excludes "with clone" option

• Global Data Services for the above database configurations

Transaction Guard supports the following transaction types against Oracle Database:

• Local transactions

• Data definition language (DDL) transactions

• Data control language (DCL) transactions

• Distributed transactions

• Remote transactions

• Parallel transactions

• Commit on success (auto-commit)

• PL/SQL with embedded commit-supported client drivers

• XA transactions using One Phase Optimizations including XA commit flag TMONEPHASE and read optimizations

• ALTER SESSION SET Container with Service clause, where the service uses Transaction Guard

Transaction Guard supports the following client drivers :

• 12c JDBC type 4 driver

• 12c OCI and OCCI client drivers

• 12c Oracle Data Provider for .NET (ODP.NET), Unmanaged Driver

• 12c ODP.NET, Managed Driver in ODAC 12c Release 4 or higher

Transaction Guard intentionally excludes recursive transactions and autonomous transactions so that they can be re-executed.

As of Oracle Database 12c Release 2, Transaction Guard also excludes:

• Two Phase XA transactions are managed externally. When using XA transactions, Transaction Guard maintains the commit outcome for one-phase XA transactions, and silently disables itself for externally-managed two-phase transactions because this outcome is owned by the TP monitor.

• Active Data Guard with read/write database links for forwarding transactions

• Golden Gate and Logical Standby for determining the outcome when failing across logical databases. Golden Gate and Logical Standby endpoints can use Transaction Guard

• Full database import cannot be executed with Transaction Guard enabled. Use an admin service without Transaction Guard for full database imports. User and object imports are not excluded.

• TAF and Application Continuity handle Transaction Guard internally. Do not code Transaction Guard in your application in the following places:

  • A failed return from TAF

  • TAF Callback for TAF or for Application Continuity for OCI and ODP.NET

  • JDBC initialization callback for Application Continuity for Java

Transaction Guard excludes failover across databases maintained by replication technology:

• Replication to Golden Gate

• Replication to Logical Standby

•  PDB clones clause (excluding PDB online relocation 12c Release 2 and later)

• All third party replication solutions

If you are using a database replica using any replication technology such as Golden Gate, or Logical Standby, or 3rd party replication, you may not use Transaction Guard between the primary and the secondary databases in this configuration.

You may use Transaction Guard on each database that participates in the replication. In this case, each database must use a different database unique identifier. Use V$DATABASE to obtain the DBID for each database.

To use Transaction Guard with an application, you must do the following:

• Use Oracle Database 12c Release 1 (12.1.0.1) or later.

• Use an application service for all database work. Create the service using the srvctl command if you are using Oracle RAC, or using the DBMS_SERVICE.CREATE_SERVICE PL/SQL subprogram if you are not using Oracle RAC.

  Do not use the default database services, because these services are for administration purposes and cannot be manipulated. That is, do not use a service name that is set to db_name or db_unique_name.

• Grant permission on the DBMS_APP_CONT package to the database users who will call GET_LTXID_OUTCOME:

  GRANT EXECUTE ON DBMS_APP_CONT TO <user-name>;
  

•  Increase DDL_LOCK_TIMEOUT if using Transaction Guard with DDL statements..

To use Transaction Guard with an application, Oracle recommends that you do the following:

• Locate and define the transaction history table for optimal performance.

• If you are using Oracle RAC or Oracle Data Guard, ensure that FAN is configured to communicate to interrupt clients fast on error.

• Set the following parameter: AQ_HA_NOTIFICATIONS = TRUE (if using OCI FAN).

The transaction history table maintains the mapping of logical transaction identifiers (LTXIDs) to database transaction. This table can be accessed only by databases users with DBA privileges. It is maintained automatically by Oracle Database, and users must not issue DDL or DML statements directly against the transaction history table.

The transaction history table (LTXID_TRANS) is created by default in the SYSAUX tablespace at database creation and upgrade. New partitions are added when instances are added, using the storage of the last partition. However, if the location of this tablespace is not optimal for performance, the DBA can move partitions to another tablespace. For example, the following statement alters the transaction history table to move it to a tablespace named FastPace:

ALTER TABLE LTXID_TRANS move partition LTXID_TRANS_4
 tablespace FastPace
 storage ( initial 10G next 10G
 minextents 1 maxextents 121 );

Configure the services for commit outcome and retention.

For example:

COMMIT_OUTCOME = TRUE
RETENTION_TIMEOUT = <retention-value>

COMMIT_OUTCOME determines whether transaction commit outcome is accessible after the commit has executed. This feature makes the outcome of the commit durable, and it is used by applications to enforce the status of the last transaction executed before an outage. The feature is used internally by the Oracle replay driver and by WebLogic Server, and it is available for use by other applications to determine an outcome. The COMMIT_OUTCOME possible values are FALSE (the default) and TRUE, and the value must be TRUE for Transaction Guard to be in effect.

The following considerations apply to COMMIT_OUTCOME:

• Using the DBMS_APP_CONT.GET_LTXID_OUTCOME procedure requires that COMMIT_OUTCOME be TRUE.

• COMMIT_OUTCOME has no effect on Active Data Guard and read-only databases. Using Transaction Guard with read/write Active Data Guard combined with database links that forward DMLs is not supported.

• COMMIT_OUTCOME is allowed on user-defined database services. Use on the database service is excluded because this service does not switch across Data Guard and cannot be started, stopped, or disabled for planned outages at the primary database.

RETENTION_TIMEOUT is used in conjunction with COMMIT_OUTCOME to set the amount of time that the commit outcome is retained. The retention timeout value is specified in seconds; the default is 86400 (24 hours), and the maximum is 2592000 (30 days). You can use the srvctl command or the DBMS_SERVICE PL/SQL package to specify the retention timeout value.

The following pseudocode shows a typical usage of Transaction Guard:

Receive a FAN down event (or recoverable error)
 
FAN aborts the dead session
 
If recoverable error  (new OCI_ATTRIBUTE for OCI, isRecoverable for JDBC)
     Get last LTXID from dead session using getLTXID or from your callback
     Obtain a new session 
     Call GET_LTXID_OUTCOME with last LTXID to obtain COMMITTED and USER_CALL_COMPLETED status
 
If COMMITTED and USER_CALL_COMPLETED  
     Then return result
 
ELSEIF COMMITTED and NOT USER_CALL_COMPLETED   
     Then return result with a warning (that details such as out binds or row count were not returned)
 
ELSEIF NOT COMMITTED 
     Cleanup and resubmit request, or return uncommitted result to the client

For replay and returning results, the application or third party container needs access to the next LTXID to be committed at the server for each session. The LTXID can be obtained using APIs (getLTXID for JDBC and OCI_ATTR_GET with LTXID for OCI) from a failed session after a recoverable outage.

The JDBC Thin driver also provides a callback that executes on each commit number change received from the database. A third party container can use this callback to save the current LTXID in preparation to use if failover is needed. Within each session, the current LTXID is in use, so the callback can override earlier ones.

If failovers cascade without completing (that is, if during recovery from one failure, another failure occurs), the application must obtain and then pass the LTXID in effect on the current session into GET_LTXID_OUTCOME.

Table 29-1 shows several conditions or situations that require some LTXID-related action, and for each the application action and next LTXID to use.

Connection pools create a different use case for managing LTXIDs because connections and sessions are preestablished and shared. In the simplest model for connection pools and middle tiers, an LTXID exists on each session handle (client-side session). It is associated with an application request at check-out from the connection pool, and is disassociated from the application request at check-in back to the pool. Between check-out and check-in, the LTXID on the session is exclusively held by that application request. After check-in, the LTXID belongs to an idle, pooled session. It is associated with the next application request that checks-out that connection.

Using Transaction Guard in this way:

• Can support duplicate detection and failover for the present HTTP request

• Allows to cancel (real Cancel operation and not Ctrl-C) timed out requests, and optional re-submission by the application

Transaction Guard is a tool for developers to use after recoverable errors to provide a known outcome. It must be used when an error is returned indicating that the last session is dead.

The Transaction Guard APIs must not be used in the following cases:

• Do not use GET_LTXID_OUTCOME on the current session. It will return an error.

• Do not use GET_LTXID_OUTCOME against a session that did not receive a recoverable error—that is, a live session. It will block that session from committing.

• Do not use GET_LTXID_OUTCOME from a different user or to a different database. It will return an error.

• Do not obtain the LTXID and save it for use later, as opposed to using it immediately. The result of GET_LTXID_OUTCOME is valid only for the last open or completed transaction. If it is used with an earlier transaction on the same session, it will return an error.

• Do not  code Transaction Guard if the application is using TAF. Use the new TAF error codes to return the results instead.

  Note:

  This rule does not apply to Application Continuity.