Transaction and Hangfire #5500

Hello support, We are having a problem with uow in different hangfire workers. These are long operations taking so much time for every job and we are trying to delegate it to the hangfire workers. We created custom command in custom repo deleting old records from db and after it is deleted in the same unitofwork we are trying to insert lots of records to sql db.

When only 1 job is working everything is fine it is doing its job smoothly but when lots of jobs starts at the same time then we are getting timeout exceptions from sql or if we prolong the timeout period it is suspending some sql queries when we analyze it from sql server profiler.

It seems like when one hangfire job is executing the sql queries, on the other hangfire thread it can not run any "delete or insert commands". So it is waiting for the other thread to be finished. I was wondering since every job is in one unit of work doesn't it needs to do "insert and delete commands" on the other hangfire job? It feels like one job is locking the other processes in hangfire.

Let's say i have 4 jobs in hangfire. think that they are doing lots of heavy operations in sql table. inserting 50000 records for each. When one is doing the operation the other job can not do anything cause it doesn't have any access to the table and waiting for the other one to finish? is that the scenario over here? or how should it suppose to work?

Sth like this

public void BookOrders(int offerId, DateTime startDate)
        {

            var minutes = DateTime.Now.Minute >= 30 ? 30 : 0;
            var hours = DateTime.Now.Hour;

            var beginDate = startDate.Date + new TimeSpan(hours, minutes, 0);


            _bookedOfferItemRepository.Delete(new List<int> { offerId }, beginDate);

            var offerItemList = _offerItemRepository.GetAll()
                .Include(oi => oi.Offer)
                .Include(oi => oi.Dates)
                .Include(oi => oi.Times)
                .Include(oi => oi.OfferItemScreens).ThenInclude(ois => ois.Screen)
                .Where(oi => oi.OfferId == offerId)
                .ToList();

            if (offerItemList.Any())
            {
               

                try
                {

                    _bookedOfferItemRepository.AutoDetectChanges = false;
                    
                    BookOfferItems(offerItemList, startDate);
                }
                finally 
                {
                    _bookedOfferItemRepository.AutoDetectChanges = true;
                }
            }
        }

  inside BookOfferItems method we are doing batch inserts according to some rules and this code starts from hangfire job. which is

[UnitOfWork]
        public override void Execute(BookingJobArgs args)
        {
            var startDate = DateTime.Now;
            var stopwatch = new Stopwatch();

            try
            {
                stopwatch.Start();
                Logger.Info($"################ STARTED Booking Tenant: {args.TenantId} Offer: ({args.OfferId}) {args.OfferName}");
                using (CurrentUnitOfWork.DisableFilter(AbpDataFilters.MayHaveTenant, AbpDataFilters.MustHaveTenant))
                {
                    _bookingManager.BookOrders(args.OfferId, startDate);
                }
                stopwatch.Stop();
                Logger.Info($"################ FINISHED Booking Tenant: {args.TenantId} Offer: ({args.OfferId}) {args.OfferName} Elapsed: {stopwatch.Elapsed.TotalSeconds.ToString()}");

                _appNotifier.BookingIsDone(DateTime.Now, args.TenantId, args.OfferName);
            }
            catch (Exception ex)
            {
                 
                Logger.Error($"################ ERROR Booking Tenant: {args.TenantId} Offer: ({args.OfferId}) {args.OfferName} Error: {ex.ToString()}");
                throw ex;
            }            
        }

and our custom repo method is

public void Delete(List<int> offerIdList, DateTime deleteFromDate)
        {
            string queryFormat = "DELETE FROM bbBookedOfferItem WHERE {0} and Date >= '{1}'";

            if (offerIdList == null || offerIdList.Count < 1)
                throw new Exception("BookedOfferItemRepository.Delete(offerIdList) offerIdList can not be null or empty.");



            string offerIdClause = string.Format("OfferId = {0}", offerIdList.First());
            if (offerIdList.Count > 1)
            {
                offerIdClause = string.Format("OfferId IN({0})", string.Join(",", offerIdList));
            }

            string query = string.Format(queryFormat, offerIdClause, deleteFromDate.ToString("yyyy-MM-dd HH:mm:ss"));

            Context.Database.ExecuteSqlCommand(query);
            Context.SaveChanges();

        }

Any help would be appreciated. Thank you.