dotConnect for Oracle Documentation
In This Topic
    Using Transactions
    In This Topic

    Understanding Transactions

    A transaction is one or several operations considered as a single unit of work which is completed entirely or have no effect at all ("all-or-nothing"). If a failure occurs at one point in the transaction, all of the updates can be rolled back to their pre-transaction state. A transaction must conform to the ACID properties - atomicity, consistency, isolation, and durability-in order to guarantee data consistency.

    If a transaction involves multiple tables in the same database, then explicit transactions in PL/SQL often perform better. You can use COMMIT and ROLLBACK statements in your SQL to fix and discard respectively the previous commands in your current PL/SQL block. For more information, see Oracle PL/SQL documentation.

    Otherwise, a transaction with plain SQL can be implemented via special command classes from library/assemblies designed to work with Oracle database. For example, you can use Devart.Data.Oracle.OracleCommand: start transaction on OracleCommand's connection, execute several SQL statements via this OracleCommand, and commit/rollback all operations when it is necessary. See the sample from the Local Transaction topic.

    This article describes the way to manipulate transactions from your .NET code (without envolving PL/SQL transactions) - this is the most common case of working with transactions. Concerning your task, you can choose the type of transaction to implement - local or distributed. A transaction considered to be a local transaction when it is a single-phase transaction and is handled by the database directly. A distributed transaction is a transaction that affects several resources, it is coordinated by a transaction monitor and uses fail-safe mechanisms (such as two-phase commit) for transaction resolution.

    Distributed transactions are not supported in .NET Standard 1.3 compatible assemblies. They are supported only for projects targeting full .NET Framework, .NET Core 2.0 or higher, and .NET Standard 2.0 or higher.

    Note: Be aware, promotable transactions used in SQL Server are not supported by Oracle, and implementation of distributed transactions differs in the OCI and Direct modes. Also take into account that TransactionScope (distributed transactions) is completed only on its disposing.

    Local Transactions

    dotConnect for Oracle has its OracleTransaction object for performing local transactions. OracleTransaction is used when several operations on one connection object should be performed as one transaction. The application creates a OracleTransaction object by calling BeginTransaction on the OracleConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the OracleTransaction object. The correlation between OracleConnection and OracleTransaction is always 1:1. As a result, only one OracleTransaction can be created for a separate OracleConnection in one moment of time.

    Example:

    The following example creates an OracleConnection and an OracleTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods.

    public void RunOracleTransaction(string myConnString) 
    { 
      OracleConnection myConnection = new OracleConnection(myConnString); 
      myConnection.Open(); 
      OracleCommand myCommand = new OracleCommand(); 
      OracleTransaction myTrans; 
      // Start a local transaction 
      myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); 
      // Assign transaction object for a pending local transaction 
      myCommand.Transaction = myTrans; 
      myCommand.Connection = myConnection; 
      try 
      { 
        myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')"; 
        myCommand.ExecuteNonQuery(); 
        myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(60, 'PRODUCTION')"; 
        myCommand.ExecuteNonQuery(); 
        myTrans.Commit(); 
        Console.WriteLine("Both records are written to database."); 
      } 
      catch(Exception e) 
      { 
        myTrans.Rollback(); 
        Console.WriteLine(e.ToString()); 
        Console.WriteLine("Neither record was written to database."); 
      } 
      finally 
      { 
        myConnection.Close(); 
      } 
    } 
    
    Public Sub RunOracleTransaction(ByVal myConnString As String)
      Dim myConnection As New OracleConnection(myConnString)
      myConnection.Open()
      Dim myCommand As New OracleCommand
      Dim myTrans As OracleTransaction
      ' Start a local transaction
      myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
      ' Assign transaction object for a pending local transaction
      myCommand.Transaction = myTrans
      myCommand.Connection = myConnection
      Try
        myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')"
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(60, 'PRODUCTION')"
        myCommand.ExecuteNonQuery()
        myTrans.Commit()
        Console.WriteLine("Both records are written to database.")
      Catch e As Exception
        myTrans.Rollback()
        Console.WriteLine(e.ToString())
        Console.WriteLine("Neither record was written to database.")
      Finally
        myConnection.Close()
      End Try
    End Sub
    
    

    Distributed Transactions in the OCI mode

    If you want to put operations that are executed on several connection objects in the same distributed transaction, it is necessary to enlist them in TransactionScope. It can be done with the Enlist parameter of connection string or with the EnlistTransaction method of OracleConnection class.

    The OCI mode has full support of TransactionScope with two-phase commit.

    The System.Transactions.TransactionScope class makes a code block transactional by implicitly enlisting connections in a distributed transaction. You must call the Complete method at the end of the code block marked by the TransactionScope. The Dispose method is invoked when program execution leaves a code block, causing the transaction to be discontinued if the Complete method is not called. If an exception has been thrown that causes the code to leave scope, the transaction is considered to be discontinued.

    It is recommended that you employ a using block to ensure that the Dispose method is called on the TransactionScope object when the using block is exited. Failure to commit or roll back pending transactions can seriously degrade performance because the default time-out for the TransactionScope is one minute. If you do not use a using statement, you must perform all work in a Try block and explicitly call the Dispose method in the Finally block.

    If an exception occurs within the TransactionScope, the transaction is marked as inconsistent and is abandoned. It is rolled back when the TransactionScope is disposed. If no exception occurs, participating transactions commit.

    Example:

    The following example demonstrates the usage of TransactionScope. It is necessary to add reference to the System.Transactions.dll assembly to use the System.Transactions namespace.

    using (TransactionScope transScope = new TransactionScope())
    {
        using (OracleConnection connection1 = new 
           OracleConnection(connectString1))
        {
            // Opening connection1 automatically enlists it in the 
            // TransactionScope as a distributed transaction.
            connection1.Open();
    
            // Do work in the first connection.
    
            // Assumes conditional logic in place where the second
            // connection will only be opened as needed.
            using (OracleConnection connection2 = new 
                OracleConnection(connectString2))
            {
                // Open the second connection, which enlists the 
                // second connection to a full distributed transaction. 
                connection2.Open();
    
                // Do work in the second connection.
            }
        }
        //  The Complete method commits the transaction.
        transScope.Complete();
    // The result of transaction will be available at the database after
    // disposing TransactionScope
    }
    
    Using transScope As New TransactionScope()
        Using connection1 As New OracleConnection(connectString1)
            ' Opening connection1 automatically enlists it in the 
            ' TransactionScope as a distributed transaction.
            connection1.Open()
    
            ' Do work in the first connection.
    
            ' Assumes conditional logic in place where the second
            ' connection will only be opened as needed.
            Using connection2 As New OracleConnection(connectString2)
                ' Open the second connection, which enlists the 
                ' second connection and promotes the transaction to
                ' a full distributed transaction.
                connection2.Open()
    
                ' Do work in the second connection.
    
            End Using
        End Using
    
        ' The Complete method commits the transaction.
        transScope.Complete()
    ' The result of transaction will be available at the database after
    ' disposing TransactionScope
    End Using
    
    

    Distributed Transactions in the Direct mode

    The Direct mode connections also are enlisted in distributed transaction. But there will be only emulation of TransactionScope support in this case, because two-phase commit is not supported in the direct mode. A separate OracleTransaction will be created for every connection in its scope. And these OracleTransaction's work is synchronized not completely:

    using (TransactionScope ts = new TransactionScope()) {
        using (OracleConnection connection = new OracleConnection(connStr)) {
            connection.Open();
            OracleCommand command = connection.CreateCommand();
            command.CommandText = "insert into dept(deptno,dname,loc) values (70,'Development','London')";
            command.ExecuteNonQuery();
        }
        using (OracleConnection connection2 = new OracleConnection(connStr)){
            connection2.Open();
            OracleCommand command2 = connection2.CreateCommand();
            command2.CommandText = "update dept set loc='New York' where deptno=70";
            command2.ExecuteNonQuery();
        }
        ts.Complete();
    }
    
    
    Using ts As New TransactionScope()
        Using connection As New OracleConnection(connStr)
            connection.Open()
            Dim Command As OracleCommand = connection.CreateCommand()
            Command.CommandText = "insert into dept(deptno,dname,loc) values (70,'Development','London')"
            Command.ExecuteNonQuery()
        End Using
        Using connection2 As New OracleConnection(connStr)
            connection2.Open()
            Dim Command2 As OracleCommand = connection2.CreateCommand()
            Command2.CommandText = "update dept set loc='New York' where deptno=70"
            command2.ExecuteNonQuery()
        End Using
        ts.Complete()
    End Using
    
    

    See Also

    Entity Framework Support |  Entity Framework Tutorial