Using Transaction Guard to Prevent Logical Corruption

Transaction Guard allows ODP.NET applications to use at-most-once execution in case of planned and unplanned outages and repeated submissions.

ODP.NET returns a logical transaction object, OracleLogicalTransaction, which is used to determine the outcome of the last open transaction in a database session following an outage. Without Transaction Guard, applications that attempt to retry operations following outages can cause logical corruption by committing duplicate transactions.

After an outage, one of the traditional problems for recovering applications had been the non-durable commit message sent back to the client. If there is a break between the client and the server, the client sees an error message indicating that the communication failed (also known as a recoverable error). This error does not inform the application if the submission executed any commit operations, or if a procedural call ran to completion while executing all expected commits. The error also does not indicate session state changes or intermittent failures. The client is left wondering if the transaction committed and if it fully completed.

These recoverable errors can cause end users or applications to attempt replay by issuing duplicate transaction submissions or other forms of logical corruption. The transaction cannot be validly resubmitted if the non-transactional state is incorrect or if it is committed. Continuing to process a committed but not completed call can result in the application using a database session that is in the wrong state.

Transaction Guard allows ODP.NET to eliminate duplicate transactions automatically and transparently, and in a manner that scales. ODP.NET uses the logical transaction object to identify the last open transaction. At runtime, Oracle retains the transaction status automatically. At commit, the logical transaction object persists. The database maintains the logical transaction status post-commit for as long as the administrator has set the retention period. ODP.NET can then use the logical transaction object to track the next transaction.

When a failure occurs, such as a node, network, or database failure, ODP.NET applications can deterministically conclude whether the transaction committed by querying its status using the logical transaction object. If not committed, the ODP.NET application can learn what state the transaction is in and whether it is recoverable and can be retried through the returned OracleException. ODP.NET applications can then take the appropriate action, such as resubmitting the transaction if it is not committed.

Note:

ODP.NET, Unmanaged Driver supports Transaction Guard and recoverable error detection. ODP.NET, Managed Driver currently does not support either.
Transaction Guard supports only local transactions. It does not support distributed transactions.

The Transaction Guard feature is enabled or disabled through the Oracle service-level configuration through the COMMIT_OUTCOME setting. By default, it is not enabled. This setting can be changed without bringing down the database. Only new connections created against the service will use the new setting.

The following is an example ODP.NET Transaction Guard application scenario:

An ODP.NET application receives a FAN down event or error. FAN automatically aborts the dead session and the application receives an OracleException. A Transaction Guard application built to handle errors transparently would do the following:

Check the value of the OracleException.IsRecoverable property. If the value is true, that application can chose to re-submit the existing transaction based on the current transaction status as described in the following steps. If the value is false, the application should roll-back, re-execute, and re-submit the current transaction.
The application retrieves the last logical transaction from the failed session.

OracleConnection.OracleLogicalTransaction
The application retrieves the transaction status, OracleLogicalTransaction.GetOutcome().

Note:
Grant the EXECUTE privilege on the DBMS_APP_CONT package to the database users that retrieve the transaction status.
The transaction status indicates two aspects of the outcome:
- Did it commit successfully? OracleLogicalTransaction.Committed
- Did it complete successfully? OracleLogicalTransaction.UserCallCompleted

Committed Value	UserCallCompleted Value	Outcome
`True`	`True`	The transaction was successful. The result can be returned to the application.
`False`	`False`	The transaction was not successful. The application can resubmit the transaction again.
`True`	`False`	The transaction committed, but there may be additional state, such as row counts or nested PL/SQL logic, that prevents the application from continuing as expected.

Sample Code

using System;
using Oracle.DataAccess.Client;
 
class Test
{
  static void Main()
  {
    bool bReadyToCommit = false;
    string constr = "user id=scott;password=tiger;data source=inst1";
    OracleConnection con = new OracleConnection(constr);
    OracleTransaction txn = null;
    OracleCommand cmd = null;
 
    try
    {
      con.Open();
      txn = con.BeginTransaction();
      cmd = new OracleCommand(con, "update emp set dept=10 where empno=7654");
      cmd.ExecuteNonQuery();
      bReadyToCommit = true;
    }
    catch(Exception ex)
    {
      Console.WriteLine(ex.ToString());
    }
 
    try
    {
      if (bReadyToCommit)
        txn.Commit();
    }
    catch(Exception ex)
    {
      if (ex.IsRecoverable)
      {
        OracleLogicalTransaction olt = con.OracleLogicalTransaction;
        olt.GetOutcome(); // or olt.GetOutcome("scott", "tiger", "inst1");
         
        if (!olt.Committed && !olt.UserCallCompleted)
        {
          // any chosen processing here if a retry is desired.
        }
        else
        {
        // transaction committed, but was not full completed
        }
      }
      else
      {
        // Not recoverable transaction. Rollback (and re-execute).
      }
    }
    finally
    {
      txn.Dispose();
      cmd.Dispose();
      con.Dispose();
    }
  }
}