Here’s a typical scenario - select about 100,000 rows from my database (say user IDs), and create a Batch Job, that includes running a job for each row.
On SQL Server (SQL Azure), if I go past ~20K jobs, batch job creation fails. What I think happens is for each BatchJob.Enqueue, 1 statement is run, which is fine. But when all the jobs are created, Hangfire does some kind of “activation” for the batch, which takes WAY too long - my guess is its trying to run 1 more statement for each job, and there’s a transaction timeout that happens here (see stack trace below).
Of course, I can increase the transaction timeout, but SQL Azure caps it at 10 mins, so that’s not a feasible option. I’m having to annoying break my batch jobs in multiple jobs, just to get this simple workflow in place.
Does anyone else using Hangfire.Pro have this issue? Smells like bad architecture to me - once all the jobs are created, the final part of the batch job should ideally just be 1 statement. Seems like something very convoluted is going on.
Hangfire.CreateBatchFailedException: An exception occurred while creating the batch. See the inner exception for details. ---> System.InvalidOperationException: The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements. at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource
1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.<>c__DisplayClass23_0.b__0(DbConnection connection, DbTransaction transaction)
at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.b__5_0(DbConnection connection, DbTransaction transaction)
at Hangfire.SqlServer.SqlServerStorage.<>c__DisplayClass19_0.b__0(DbConnection connection, DbTransaction transaction)
1.<UseTransaction>b__0(DbConnection connection) at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](Func2 func)
3 func, Nullable1 isolationLevel)
2 action) at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.Commit() at Hangfire.Batches.Client.BatchFactory.Create(BatchCreateContext context) at Hangfire.BatchJobClient.Create(Action1 createAction, IBatchState state, String description)
— End of inner exception stack trace —
1 createAction, IBatchState state, String description) at Hangfire.BatchJobClientExtensions.StartNew(IBatchJobClient client, Action1 createAction, String description)
1 createAction, String description)