dotConnect for Oracle Documentation
Devart.Data.Oracle Namespace / OracleLogicalTransaction Class
Members Example

In This Topic
    OracleLogicalTransaction Class
    In This Topic
    This class allows you to find out whether the transaction, running during the last database outage, was successfully committed and completed and determine whether your application should commit, complete, or rollback the transaction in order to avoid logical corruption because of committing duplicate transactions.
    Syntax
    'Declaration
     
    Public Class OracleLogicalTransaction 
       Implements System.IDisposable 
    public class OracleLogicalTransaction : System.IDisposable  
    Remarks

    This class requires OCI connection mode and Oracle 12c. It also requires Transaction Guard enabled on the Oracle server.

    You can use it in the following way:

    1. When an application receives a FAN down event or error, FAN automatically aborts the dead connection and OracleException generated. First, you need to check the OracleException.IsRecoverable property. If the value is true, you can re-submit the existing transaction based on the current transaction status. Otherwise, you should roll back, re-execute, and re-submit the current transaction.
    2. Retrieve the last logical transaction from the failed connection via the OracleConnection.OracleLogicalTransaction property.
    3. Execute its GetOutcome method to get the status of the transaction.
    4. After this, you should analyse the values of the Committed and UserCallCompleted properties, which mean whether the transaction was successfully committed and completed respectively, and act accordingly.

    If both properties are true, it means the transaction was successful. The result can be returned to the application.

    If both properties are false, it means the transaction was unsuccessful and it can be resubmitted again.

    If Committed is true, and UserCallCompleted is false, it means the transaction was commited, but the information returned may be incomplete and/or not all expected work was completed. The application may not continue as expected.

    Example
    The following example demonstrates how to use the OracleLogicalTransaction class and its members in order to correctly process database outages and resubmit or rollback the last running transaction depending on its status.
    using System;
    using Devart.Data.Oracle;
     
    class Test
    {
            static void Main()
            {
                    string constr = "User Id=Scott;Password=tiger;Data Source=Ora;";
                    OracleConnection connection = new OracleConnection(constr);
                    OracleTransaction transaction = null;
                    OracleCommand command = null;
     
                    try
                    {
                            connection.Open();
                            transaction = connection.BeginTransaction();
                            command = new OracleCommand(connection, "update emp set dept=10 where empno=4321");
                            command.ExecuteNonQuery();
                    }
                    catch(Exception e)
                    {
                            Console.WriteLine(e.ToString());
                            return;
                    }
     
                    try
                    {
                            transaction.Commit();
                    }
                    catch(OracleException e)
                    {
                            if (e.IsRecoverable)
                            {
                                    OracleLogicalTransaction logicalTransaction = connection.OracleLogicalTransaction;
                                    logicalTransaction.GetOutcome(); // or logicalTransaction.GetOutcome("scott", "tiger", "inst1");
             
                                    if (!logicalTransaction.Committed && !logicalTransaction.UserCallCompleted)
                                    {
                                            // any chosen processing here if a retry is desired.
                                    }
                                    else
                                    {
                                            // transaction committed, but was not fully completed
                                    }
                            }
                            else
                            {
                                    // Not recoverable transaction. Rollback (and re-execute).
                            }
                    }
            }
    }
    Imports Devart.Data.Oracle
    
    Class Test
            Private Shared Sub Main()
                    Dim constr As String = "User Id=Scott;Password=tiger;Data Source=Ora;"
                    Dim connection As New OracleConnection(constr)
                    Dim transaction As OracleTransaction = Nothing
                    Dim command As OracleCommand = Nothing
    
                    Try
                            connection.Open()
                            transaction = connection.BeginTransaction()
                            command = New OracleCommand(connection, "update emp set dept=10 where empno=4321")
                            command.ExecuteNonQuery()
                    Catch e As Exception
                            Console.WriteLine(e.ToString())
                            Return
                    End Try
    
                    Try
                            transaction.Commit()
                    Catch e As OracleException
                            If e.IsRecoverable Then
                                    Dim logicalTransaction As OracleLogicalTransaction = connection.OracleLogicalTransaction
                                    logicalTransaction.GetOutcome()
                                    ' or logicalTransaction.GetOutcome("scott", "tiger", "inst1");
    
                                    If Not logicalTransaction.Committed AndAlso Not logicalTransaction.UserCallCompleted Then
                                            ' any chosen processing here if a retry is desired.
                                    Else
                                            ' transaction committed, but was not fully completed
                                    End If
                            Else
                                    ' Not recoverable transaction. Rollback (and re-execute).
                            End If
                    End Try
            End Sub
    End Class
    Inheritance Hierarchy

    System.Object
       Devart.Data.Oracle.OracleLogicalTransaction

    Requirements

    Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

    See Also