Behaviour of OracleTransaction when Dispose() is called

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mhess
Posts: 4
Joined: Thu 07 Sep 2006 13:19

Behaviour of OracleTransaction when Dispose() is called

Post by mhess » Fri 28 Aug 2009 14:19

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;
}
}
}

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 10 Sep 2009 14:04

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.

mhess
Posts: 4
Joined: Thu 07 Sep 2006 13:19

Perfect

Post by mhess » Thu 10 Sep 2009 14:39

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

Kind Regards
Matthias Hess

Post Reply