Behavior of PgSqlTransaction.Dispose()

Behavior of PgSqlTransaction.Dispose()

Postby 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();
    }
}
bozr
 
Posts: 17
Joined: Mon 21 Jun 2010 12:39

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


Return to dotConnect for PostgreSQL