Behaviour of OracleTransaction when Dispose() is called

Behaviour of OracleTransaction when Dispose() is called

Postby 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;
}
}
}
mhess
 
Posts: 4
Joined: Thu 07 Sep 2006 13:19

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Perfect

Postby 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
mhess
 
Posts: 4
Joined: Thu 07 Sep 2006 13:19


Return to dotConnect for Oracle