Unpredictable Npgsql Exception During Transaction Dispose

c# dapper npgsql postgresql-9.3 transactions

Question

I am attempting to run some commands in a transaction using Npgsql 2.1.3 (Postgres v9.3) and Dapper 1.29, but am occasionally getting some unexpected exceptions. Sometimes, the code works just fine. Other times, I am getting the following exception:

Npgsql.NpgsqlException : ERROR: 57014: canceling statement due to user request

My connection string is (I have removed the user id, password, and database):

PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;
COMPATIBLE=2.1.3.0;HOST=127.0.0.1;USER ID=...;PASSWORD=...;DATABASE=...;SSLMODE=Require

Here is an example of the code that is failing (ignoring error checking for brevity):

var connection = new NpgsqlConnection(connectionString);
if (connection.State != ConnectionState.Open)
    connection.Open();
IDbTransaction transaction = connection.BeginTransaction(
    IsolationLevel.RepeatableRead);
try {
    ...
    // Select some stuff using the connection underlying the transaction
    var query = "SELECT * FROM ...";
    var result = connection.Query<MyType>(query, dynamicParams, transaction).ToList();
    ...
    // Perform an insert using the same command indicated above
    query = "INSERT INTO ... (...) VALUES (...) RETURNING *";
    var result2 = connection.Query<MyType2>(query, dynamicParams, transaction).FirstOrDefault();
    ...
    // Delete the entry added above
    query = "DELETE FROM ... WHERE id = :id";
    var result3 = connection.Query<long>(query, dynamicParams, transaction).FirstOrDefault();
    // I Don't explicitly call transaction.Rollback(),
    //     but I have tried that and it has no effect on the outcome
} finally {
    // Note that if I put Thread.Sleep(1000) here, I never get the exception
    transaction.Dispose(); // Npgsql.NpgsqlException (sometimes)
    connection.Dispose();
}

Does anyone know why the above code would be erratically failing on me? I have verified that none of my calls to connection.Query<> is throwing an exception. Also, when I put a Thread.Sleep() command prior to transaction.Dispose(), the exception never happens. It appears that there is some asynchronous code being called to read the response of Rollback(), which is happening after the transaction is being disposed and thus throwing the exception. I have looked at all of the properties of NpgsqlConnection and NpgsqlTransaction, and none of them appear to indicate whether the result of Rollback() has been read or not. I will keep looking into it on my end, but any additional help is greatly appreciated.

Accepted Answer

To anyone else who is experiencing this issue: updating to Npgsql 2.2.3 (from 2.1.3) fixes the issue. I didn't see any fixes in the Npgsql release notes that appear to be related, but clearly something changed that fixed the glitch.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why