Connection leak with unpooled connections and TransactionScope #4963
Description
With connection pooling turned off, connections that are enlisted in a transaction appear to remain open after they are disposed and the transaction is ended.
To replicate run this console app with a breakpoint on the Hello World. Make sure to adjust the connection string as appropriate.
using Npgsql;
using System.Transactions;
using (var transactionScope = new TransactionScope())
{
var connection = new NpgsqlConnection("User ID=postgres;Password=password;Server=localhost;Port=5432;Database=hangfiredemo;Integrated Security=true;Pooling=false;");
connection.Open();
connection.EnlistTransaction(Transaction.Current);
var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT 'test-query'";
cmd.ExecuteNonQuery();
connection.Dispose();
transactionScope.Complete();
}
Console.WriteLine("Hello, World!");After the breakpoint is hit run this against the PostgreSQL server:
select pid, xact_start, query from pg_stat_activity
where datname = 'hangfiredemo'
and pid <> pg_backend_pid();The expectation is that the query above returns no records. Instead it returns a record including the connection from the C# above.
Conversely this code segment seems to work fine and DOES close the connection when it is disposed:
using Npgsql;
using System.Transactions;
var connection = new NpgsqlConnection("User ID=postgres;Password=password;Server=localhost;Port=5432;Database=hangfiredemo;Integrated Security=true;Pooling=false;");
connection.Open();
using (var transactionScope = new TransactionScope())
{
connection.EnlistTransaction(Transaction.Current);
var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT 'test-query'";
cmd.ExecuteNonQuery();
transactionScope.Complete();
}
connection.Dispose();
Console.WriteLine("Hello, World!");This appears to have been introduced in release 6.0.0-preview6.