How does Npgsql handle failed transactions?

Question

In an ASP.NET application (C#) we are using Postgres as backend and Npgsql as data provider. A couple of days ago we had a serious problem with loss of data. I investigated in code and found code like this:

    var transaction = connection.BeginTransaction();
    //some crud operation here  
    transaction.Commit()

Someone insisted that Npgsql would handle exception by its own and would automatically rollback the transaction in case something went wrong during the transactions.

In my opinion this is quite optimistic, and the code should be wrapped in a try and catch block and call a transaction rollback explicitly:

 var transaction = connection.BeginTransaction();
 try
    {
        //some crud operation here        
        transaction.Commit
    }catch(Exception ex)
    {
        transaction.Rollback();
    }

Is that wrong?

Moreover, will a transaction rollback always work? I read somewhere that it will only work if a Postgres exception is raised, say in case of malformed sql, but it will not work in case of other kind of exceptions. Can someone clarify this point too?

Answer 1

another way could be:

using( var tx = connection.BeginTransaction())
{
    .. do som database stuff ..

    tx.Commit();
}

Dispose on a non committed transaction leads to a rollback. And resources are freed!

Answer 2

PostgreSQL will automatically abort, but not rollback, the transaction in case of an error.

The transaction is only done if you

1. disconnect

2. end the transaction with COMMIT or ROLLBACK (it will rollback in both cases)

All statements sent on the connection between the error and the end of the transaction will be ignored.

So yes, you should use a try / catch block and rollback.

A ROLLBACK will always undo the current transaction completely, no matter if there was an error or not.