Devart Forums

Please have a look at the code below, it demonstrates a behaviour of OracleTransaction which I would not expect.

We are using an OracleTransaction object inside of a using { ... } block. The transaction is not committed, and the using block is left by an exception.

We expect the transaction to be rolled back. Instead, we see two rows in the test table.

Kind Regards
Matthias Hess

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Devart.Data.Oracle;

namespace OracleTransactionTester
{
class Program
{
// Before running this code, create the following table in your Oracle DB:
// CREATE TABLE test (A VARCHAR2(20))

// Put your connection string in the following line:
const string connectionString = "Data Source=MYSERVER;Password=XXX;User ID=XXX;Pooling=false";

// Run this application.

// EXPECTED RESULTS:
// Because the first insert throws an exception before commiting the transaction, and because
// we are using a "using" block, we expect the transaction to be disposed and rolled back.

// WHAT WE SEE INSTEAD:
// After running this code, we see two rows in the test table.


static void Main(string[] args)
{
try
{
using (OracleConnection cn = CreateDbConnection())
{
using (IDbCommand cmd = cn.CreateCommand())
{
using (IDbTransaction tr = cn.BeginTransaction())
{
cmd.CommandText = "INSERT INTO TEST VALUES (1)";
cmd.Transaction = tr;
cmd.ExecuteNonQuery();

throw new Exception("This transaction should be rolled back");
tr.Commit(); //
}
}
}
}
catch (Exception ex)
{
}

using (OracleConnection cn = CreateDbConnection())
{
using (IDbCommand cmd = cn.CreateCommand())
{
using (IDbTransaction tr = cn.BeginTransaction())
{
cmd.CommandText = "INSERT INTO TEST VALUES (2)";
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
tr.Commit();
}
}
}
}

private static OracleConnection CreateDbConnection()
{

OracleConnection cn = new OracleConnection(connectionString);
cn.AutoCommit = false;
cn.Open();
return cn;
}
}
}

Please try the current 5.25.42 build of dotConnect for Oracle. We have implemented Rollback call when making Dispose for OracleTransaction. Please notify us about the results.

The transaction now behaves as expected.
Thank you for your excellent support and the quick problem resolution.

Kind Regards
Matthias Hess