Hangfire.SqlServer Transaction Isolation Level Serializable

question
1

We are currently experiencing an issue using Hangfire in our ASP.NET MVC 5 application. We have a scenario in our application in which we perform transactional processing of data that is persisted to our SQL Server database that may or may not also include enqueuing of a background job in Hangfire (via a call to BackgroundJob.Enqueue). For the transactional integrity of our system, we need for the commit of the data changes and the enqueue of the background job to be atomic (all or nothing). The schema for our transactional tables (dbo) is stored in the same database as the schema for the Hangfire SQL Server storage tables (Hangfire), and both Hangfire and our EF DbContext object use the same connection to the database (to avoid scaling to DTC).

The following are the Hangfire components that we utilize in our application:

  • Hangfire
  • Hangfire.Core
  • Hangfire.SqlServer
  • Hangfire.Dashboard.Authorization
  • Hangfire.Ninject

The following is (simplified) example code of our scenario:

using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted })
{
	var context = new MyDbContext();
	TransactionalProcessingOfData(context);
	context.SaveChanges();

	BackgroundJob.Enqueue<MyJob>(j => j.Run());
}

In terms of transaction scoping, the above code is equivalent to the following code:

using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted })
{
	var context = new MyDbContext();
	TransactionalProcessingOfData(context);
	context.SaveChanges();

	HangfireEnqueueLogic();

	// From Hangfire.SqlServer.SqlServerWriteOnlyTransaction.Commit()
        using (var transaction = new TransactionScope(
TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
	{
		_connection.EnlistTransaction(Transaction.Current);
		foreach (var command in _commandQueue)
		{

			command(_connection);
		}

		transaction.Complete();
	}
}

When this code executes, the following exception is produced:

Hangfire.Client.CreateJobFailedException: Job creation process has bee failed. See inner exception for details ---> System.ArgumentException: The transaction specified for TransactionScope has a different IsolationLevel than the value requested for the scope.
Parameter name: transactionOptions.IsolationLevel
   at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption, TransactionOptions transactionOptions, TransactionScopeAsyncFlowOption asyncFlowOption)
   at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.Commit()
   at Hangfire.States.StateChangeProcess.ApplyState(ApplyStateContext context, IEnumerable`1 filters)
   at Hangfire.States.StateChangeProcess.ChangeState(StateContext context, IState toState, String oldStateName)
   at Hangfire.States.StateMachine.CreateInState(Job job, IDictionary`2 parameters, IState state)
   at Hangfire.Client.CreateContext.CreateJob()
   at Hangfire.Client.JobCreationProcess.<>c__DisplayClass9.<CreateWithFilters>b__6()
   at Hangfire.Client.JobCreationProcess.InvokeClientFilter(IClientFilter filter, CreatingContext preContext, Func`1 continuation)
   at Hangfire.Client.JobCreationProcess.Run(CreateContext context)
   at Hangfire.BackgroundJobClient.Create(Job job, IState state)
   --- End of inner exception stack trace ---
   at Hangfire.BackgroundJobClient.Create(Job job, IState state)
   at Hangfire.BackgroundJobClientExtensions.Enqueue[T](IBackgroundJobClient client, Expression`1 methodCall)
   ...snip...

We have tried changing our outer transaction scope to set TransactionOptions.IsolationLevel = IsolationLevel.Serializable, but that approach causes us other errors and occasional deadlocks as the serializable isolation level is generally too restrictive for the transactional data processing of most applications (including ours).

As you can imagine, we are a bit stuck on this issue. Our user requirements dictate that the commit of our transactional data processing and the enqueue of the background job be atomic. As implementing compensation logic over a myriad of background processes and transactional commits would be both costly and error-prone, working around this issue as such is undesirable.

And with that explanation in place, onto our question:

What is the recommended approach for including both transactional data changes and background job enqueues against Hangfire in the same transaction when using Hangfire SQL Server storage?

Thank you in advance for your time and consideration.

2

I see something about using read committed or parent transaction isolation level instead of serializable. In the release notes for 1.4.0 beta. Might be worth taking a look