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?

score 11 · Answer 1 · answered Mar 30 '19 at 21:54

11

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!

answered Mar 30 '19 at 21:54

Frank Nielsen

1,546
1
10
17

2

https://github.com/npgsql/npgsql/blob/main/src/Npgsql/NpgsqlTransaction.cs#L366-L389 Should be marked as accepted answer. – Ciapeczka Sep 09 '22 at 05:29

score 6 · Accepted Answer · answered Mar 30 '19 at 19:01

6

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

The transaction is only done if you

disconnect
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.

answered Mar 30 '19 at 19:01

Laurenz Albe

209,280
17
206
263

Hi, thanks. But what happens if a .NET IO Exception is thrown during the transaction. Will the using clause rollback the transaction anyhow or will I need to call an explicit rollback()? – paolo_tn Apr 01 '19 at 06:32
1

If the error comes from the database, the transaction is *aborted* but not yet rolled back. If the exception cones from .NET, the transaction is still active (unless the provider aborted it). In both cases, you need an explicit rollback to end the transaction. – Laurenz Albe Apr 01 '19 at 06:34
Got it! Thanks a lot! – paolo_tn Apr 01 '19 at 15:36
"ABORT rolls back the current transaction and causes all the updates made by the transaction to be discarded. This command is identical in behavior to the standard SQL command ROLLBACK, and is present only for historical reasons." https://www.postgresql.org/docs/current/sql-abort.html – Artur INTECH Apr 30 '22 at 14:15
1

@ArturBeljajev I think nobody was talking about the SQL statement. – Laurenz Albe May 02 '22 at 06:00
@LaurenzAlbe I was a bit confused by this: "PostgreSQL will automatically abort, but not rollback". Maybe it is me who got it wrong... – Artur INTECH May 02 '22 at 08:50
1

@ArturBeljajev I see. Maybe I didn't describe it correctly. If you have an error inside a transaction, you go to state `idle in transaction (aborted)`, and you need to run `ROLLBACK` to get it out of that state. – Laurenz Albe May 02 '22 at 09:54

How does Npgsql handle failed transactions?

2 Answers2