Using TransactionScope and EF together with raw connection might trigger idle-in-transaction timeout

Aberta
  • Algo errado?
#3,602 0 comentários 0 reações 0 responsáveis Ver no GitHub

Mantenedores costumam responder em até 1 dia

Ninguém assumiu esta issue ainda.

Avaliação

Dificuldade
4/5
Tempo estimado
3-5 dias
Facilidade para iniciantes
25/100
Tipo de issue
Bug
Clareza
Razoavelmente clara
Status de atividade
Estagnada
Stack de tecnologia
csharp, postgresql
Domínio
databases

Direção de pesquisa

Start by locating and running the Ambient_transaction_bug test in the TransactionNpgsqlTest class. Trace how the test combines TransactionScope, DbContext, and a raw NpgsqlConnection, then compare behavior with the commented-out context usage and ping. Done when the test reliably demonstrates the reported timeout and the transaction behavior is corrected without using a prepared transaction.

Escrita pelo modelo de indexação a partir do texto da issue.

Descrição

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();
Linguagem predominante
C#
Estrelas
1.8k
Forks
268
Merge médio
2h 35min
PRs com merge (30d)
136

Preparar o ambiente

Este projeto não oferece contêiner de desenvolvimento, Dockerfile nem guia de contribuição, então a configuração fica por sua conta: comece pelo README e veja nosso guia da primeira contribuição para os passos gerais.

Primeiros passos

  1. Leia a issue inteira e depois o guia de contribuição do projeto.
  2. Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
  3. Faça um fork do repositório e trabalhe em uma branch.
  4. Abra um pull request que referencie o número da issue.

Mais de npgsql/efcore.pg

Todas as issues de npgsql/efcore.pg

Issues semelhantes

Mais issues de C#

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.