Repository navigation
Using TransactionScope and EF together with raw connection might trigger idle-in-transaction timeout #3602
Description
I have a piece of code where I use both DbContext and data source to get raw sql connection, and in together with TransactionScope, it triggers idle-in-transaction timeout.
Here's a unit test that I put in TransactionNpgsqlTest class to reproduce it. If we "ping" DbContext every now and then this test passes. If we comment out the first context usage, it also passes, but if we leave it in current state, you should experience the timeout (mine is set to 5 seconds).
My expectation would be that no matter in which connection, the transaction is the same and no timeout should be triggered.
Also, prepared transaction count check is there to ensure that no distributed transaction is being used.
P.S. I am not sure if this is more of Npgsql problem or EF itself, but trying to start here.
[ConditionalFact]
public async Task Ambient_transaction_bug()
{
await using var context = CreateContext();
var timeoutInMilliseconds = await context.Database.SqlQueryRaw<int>("SELECT setting::INTEGER AS \"Value\" FROM pg_settings WHERE name = 'idle_in_transaction_session_timeout'").Order().FirstAsync();
Assert.True(timeoutInMilliseconds > 0);
timeoutInMilliseconds /= 2;
using var transactionScope = new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled);
// Adding this line causes "terminating connection due to idle-in-transaction timeout" error at the bottom. Feels like transactions are not scoped.
_ = context.Set<TransactionCustomer>().Any();
await using (var conn = new NpgsqlConnection(TestStore.ConnectionString))
{
conn.Open();
for (var i = 0; i < 3; i++) // A loop to show that this does not invoke transaction session timeout when doing some "work" in the same connection
{
await using var cmd = new NpgsqlCommand("SELECT 1", conn);
cmd.ExecuteNonQuery();
// Uncommenting this Assert would NOT cause the idle-in-transaction timeout anymore, because it "pings" EF transaction.
//Assert.Equal(0, GetNumberOfPreparedTransactions(context));
await Task.Delay(timeoutInMilliseconds);
}
}
_ = context.Set<TransactionCustomer>().Any();
Assert.Equal(0, GetNumberOfPreparedTransactions(context));
}
int GetNumberOfPreparedTransactions(DbContext context)
=> context.Database.SqlQueryRaw<int>("SELECT COUNT(*) AS \"Value\" FROM pg_prepared_xacts WHERE database = 'postgres'").Order().First();
Activity