• Understanding Transactions
• Local Transactions
• Distributed Transactions in the OCI mode
• Distributed Transactions in the Direct mode

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.

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:

• If operations on one of enlisted connections raise exception, you can easily handle this situation with try...catch block. Just place TransactionScope.Complete() to the last line of your try block. So the code execution in the case of any exception will not reach the Complete() line, and distributed transaction will not be commited. On the other side, the following scenario can happen: all operations on two enlisted to TransactionScope connections are successfully executed without exceptions, but if on disposing TransactionScope (when operations are actually committed to the database) the first OracleTransaction fails (e.g., server failure or killing the database session), the second OracleTransaction still is committed anyway, and TransactionScope is "successfully" completed (without its 1-st OracleTransaction).
• The changes of one OracleTransaction will NOT be visible in other OracleTransactions of current TransactionScope. For example, the 70-th record will be inserted and updated within TransactionScope in the OCI mode, but only inserted (not updated) in the direct mode:

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