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