BackgroundJob.Enqueue not work in TransactionScope

[ficnar]

Using BackgroundJob.Enqueue inside TransactionScope throw exception „System.NotSupportedException: Nested/Concurrent transactions aren't supported.“ The reason is that Npgsql not supports Nested/Concurrent transactions. Problem is in the method Hangfire.PostgreSql.WriteOnlyTransaction.Commit. This method always beginning a new sql transaction. The solution may be beginning new sql transaction only if Transaction.Current is null.

Or, do exists other solution for this problem?

[ahydrax]

Hi @ficnar, thanks for report. Do you have minimal repro of this?

[ficnar]

Of course, it is simple fragment code of async method for saving data to DB + add a new job in the same transaction.

using (TransactionScope scope = new TransactionScope(
     TransactionScopeOption.Required, 
     new TransactionOptions() {IsolationLevel=IsolationLevel.ReadCommitted}, 
     TransactionScopeAsyncFlowOption.Enabled))
{
     // any sql commands - for example save changes from my entity context
     await myEntityContext.SaveChangesAsync();

     // or basic any ADO.NET command
     using (NpgsqlConnection conn = new NpgsqlConnection("<my connection string>"))
     {
          conn.Open();
          NpgsqlCommand cmd = conn.CreateCommand();
          cmd.CommandText = "insert into test.scope (inserted) values (@now)";
          cmd.Parameters.Add(new NpgsqlParameter("now", DateTime.Now));
          await cmd.ExecuteNonQueryAsync();
     }

    // this line throw discussed exception
    BackgroundJob.Enqueue(() => File.AppendAllText(
       @"d:\scope.txt",
       $"{DateTime.Now.ToLongTimeString()}\r\n"));

    scope.Complete();
}

[xumix]

@ficnar have you tried Enlist=true in your connection string?

[ficnar]

Yes, I have Enlist=true, it is default. With Enlist=false it works without exception, but in two separate sql transaction. Problem is beginning second sql transaction in method Hangfire.PostgreSql.WriteOnlyTransaction.Commit. If you add test to this method for openned transaction by Transaction.Current, than will be work properly. For example:

if (Transaction.Current == null)
   // use your current code
else
  // use your current code, but with transaction from Transaction.Current and without beginning new transaction and commit

[ahydrax]

@ficnar @xumix I was thinking about possible solution and I came up with running hangfire code always inside separate transaction. We can't use outer transaction as there is no guarantee that transaction completes. Does it sound like a solution?

[ficnar]

Unfortunately, no. I insert data to DB from my code and job must use this data. All at once I need both action (insert my data and insert job) in one transacation. My data without job is bad. Job without my data is bad too.

[xumix]

@ahydrax for me it is a solution. @ficnar you should enqueue your jobs after transaction is complete, so the jobs are reentrant.

[ficnar]

@xumix for me, it is not a solution. I have minimal two reasons:

1. I using independent business components (modules) called from the main business method. All components work in one SQL transaction. Any components can enqueue any job. For your solution, I would have to rewrite component to two parts, one for business logic (database operations) and second for enqueue jobs.

2. Your solution is not consistent, because when will be any problem with enqueue job, then the business operation will be committed and the job will be missed.

I have one question. What problem does it prevent you from implanting my code design?

[ahydrax]

@ficnar the problem is that breaks internal Hangfire machinery as it cannot control database operations which is reliying on. My suggestion is that you need to implement transaction-like code in your project. From my perspective Hangfire is not the right tool to handle such workflows.

[ficnar]

@ahydrax you may be right, but the implementation for MS SQL Server works well for this case, by contrast with PostgreSQL implementation.

[xumix]

@ficnar we had implemented a disposable UnitOfWork which has OnCommitted event and a List of added jobs, so on the event execution we enqueue jobs. As a bonus you will not have jobs that are executed in case of transaction rollback.

[schmitch]

Actually @ahydrax it would help if it would be possible to specify a Transaction to the PostgreSqlStorage constructor (i.e. NpgsqlTransaction contains the NpgsqlConnection).

i.e.:

public sealed class PostgreSqlStorage : JobStorage, IDisposable {
  public PostgreSqlStorage(NpgsqlTransaction transaction) {
   ....
  }
}

var storage = new PostgreSqlStorage(transaction);
var client = new BackgroundJobClient(storage);
client.Enqueue(() => Console.WriteLine());

The good thing would be that it would be possible to check if the storage was started with a running transaction and it would also be way better to use NpgsqlTransaction instead of TransactionScope. If a Transaction would be provided, no commit from PostgreSqlStorage should be done. Rollback will be done automatically if an exception is thrown.

usage would then be like that:

using(var transaction = await EntityFrameworkContext.Database.BeginTransactionAsync(IsolationLevel...) {
    await EntityFrameworkContext.SaveChangesAsync();
    var storage = new PostgreSqlStorage(transaction);
    var client = new BackgroundJobClient(storage);
     client.Enqueue(() => Console.WriteLine());
   transaction.Commit();
}

I would prefer that this is supported, since MSSQL provider has it, too (at least the transaction scope).

[ficnar]

@schmitch Yes. This is solution for me. But this is very similar as using direct Transaction.Current. Benefit is it working with your scenario and TransactionScope too.

[schmitch]

actually Transaction.Current is only supported in .net standard 2.0, however that project also targets 1.6, so there is no way to use it.