Behavior of PgSqlTransaction.Dispose()

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
bozr
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Behavior of PgSqlTransaction.Dispose()

Post by bozr » Tue 06 Mar 2012 09:33

Hi,

I've got a question about the Dispose() method of the PgSqlTransaction class.

When I use the code as stated below I sometimes run into a "Connection must be opened" exception. From the stack trace it appears that PgSqlTransaction.Dispose() invokes PgSqlTransaction.Rollback(). This seems rather strange to me.

Can you elaborate on why this happens, or is my use of Dispose() illegal?

Code: Select all

DbConnection connection = null;
DbTransaction transaction = null;
DbCommand command1 = null;
DbCommand command2 = null;

try
{
    connection = OpenConnection();
    transaction = connection.BeginTransaction();

    command1 = connection.CreateCommand();
    command1.CommandText = "INSERT INTO test_data1 VALUES(1)";
    command1.ExecuteNonQuery();

    command2 = connection.CreateCommand();
    command2.CommandText = "INSERT INTO test_data2 VALUES(2)";
    command2.ExecuteNonQuery();

    transaction.Commit();
}
catch (DbException e)
{
    if (transaction != null)
    {
        transaction.Rollback();
    }

    mLog.Error(e);
}
catch (Exception e)
{
    mLog.Error(e);
}
finally
{
    if (command1 != null)
        command1.Dispose();

    if (command2 != null)
        command2.Dispose();

    if (transaction != null)
        transaction.Dispose();

    if (connection != null)
    {
        connection.Close();
        connection.Dispose();
    }
}

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

Post by Shalex » Wed 14 Mar 2012 09:35

We cannot reproduce the mentioned error in our environment with the 5.70.311 version of dotConnect for PostgreSQL using your code.
bozr wrote:From the stack trace it appears that PgSqlTransaction.Dispose() invokes PgSqlTransaction.Rollback(). This seems rather strange to me.
We think that you have encountered the following scenario:
1. Some exception, which is not caught by "catch (DbException e)" but makes connection be closed, is raised in this code:

Code: Select all

    command1 = connection.CreateCommand();
    command1.CommandText = "INSERT INTO test_data1 VALUES(1)";
    command1.ExecuteNonQuery();

    command2 = connection.CreateCommand();
    command2.CommandText = "INSERT INTO test_data2 VALUES(2)";
    command2.ExecuteNonQuery();
2. As a result, "catch (Exception e)" logs the exception and

Code: Select all

finally
{
    ...
    if (transaction != null)
        transaction.Dispose();
    ...
sees that transaction is opened (after "transaction = connection.BeginTransaction();") but not committed/rolled back. So Dispose() calls Rollback() which leads to "Connection must be opened" because connection is closed.

Post Reply