TransactionScope: lightweight or distributed transaction

[Deefken]

Hi,

I have a question about the use of System.Transactions.TransactionScope:
does it create a distributed transaction or a lightweight transaction? What happens if multiple connections are opened (in the scope) to the same Oracle server?

For instance:

Code: Select all

Using txScope As New TransactionScope()
  Using conn As New OracleConnection("Data Source=SOMESERVER;...")
   conn.Open()
   Using conn2 As New OracleConnection("Data Source=SOMESERVER;...")
     conn.Open()
     conn.Close()
   End Using
  conn.Close()
  End Using
End Using

I would assume that the transaction remains local (lightweight)?

Thanks in advance for your answer,
Deefken

[Shalex]

We have implemented a full support of TransactionScope in the OCI mode (the Direct=false; parameter in the connection string). You can also use TransactionScope in the direct mode (Direct=true;), but there will be only emulation of TransactionScope in this case, because a separate
OracleTransaction will be created for every connection in its scope. As a result:
a) the changes of one transaction will not be visible in another transaction of current TransactionScope. For example, the 131-st record will be inserted and updated within TransactionScope in the OCI mode, but only inserted (not updated) in the direct mode:

Code: Select all

            using (TransactionScope ts = new TransactionScope()) {
                using (OracleConnection conn = new OracleConnection(conStr)) 
{
                    conn.Open();
                    OracleCommand command = conn.CreateCommand();
                    command.CommandText = "insert into dept values 
(131,'y','y')";
                    command.ExecuteNonQuery();
                }
                using (OracleConnection conn = new OracleConnection(conStr)) 
{
                    conn.Open();
                    OracleCommand command = conn.CreateCommand();
                    command.CommandText = "update dept set dname='xxxx' 
where deptno=131";
                    command.ExecuteNonQuery();
                }
                ts.Complete();
            }

b) two-phase commit is not supported in the direct mode. So it is possible the following scenario: two transaction in one TransactionScope are successfully executed, but, when committing, the second transaction fails (e.g. server failure) - the first transaction will be committed anyway, and TransactionScope will be "successfully" completed (without its 2-nd transaction).

Note TransactionScope is completed only on its disposing (not when calling Complete()). But it is necessary to call txScope.Complete() to complete your transaction.

[Deefken]

Thank you for the information...

However, it is not completely clear to me if using a Transactionscope first creates a local transaction that is only promoted to a distributed transaction if needed?

For instance, using a Transactionscope in the context of a SQL server 2005 will first create a local (lightweight) transaction and will only create a distributed transaction if a second connection is opened to another server. As such, I would assume that in the context of SQL Server

Code: Select all

Using txScope As New TransactionScope() 
  Using conn As New OracleConnection("Data Source=SOMESERVER;...") 
     conn.Open() 
     conn.Close() 
   End Using 
   ...
   entityContext.SaveChanges() '  where entityContext connects to the same SOMSERVER
End Using

will only use a lightweight transaction since two connections are opened to the same server. Is this also the case when using the Devart provider for Oracle?

[Shalex]

You are describing the promotable transactions which are supported by SQL Server. We cannot implement support for this type of transactions in dotConnect for Oracle because Oracle server itself doesn't support them.

In the case of dotConnect for Oracle and Oracle server, you can either create a transaction for every connection separately (local transactions), or enlist (explicitly or implicitly) every connection in the scope of TransactionScope to work with a distributed transaction (global transaction). But local transaction can not be started within a scope of the distributed transaction.