Linq - How to prevent locks when bulk delete

Question

In my code logic, firstly i am deleting large records with multiple queries & then doing bulk insert.

Here is the Code :-

using (var scope = new TransactionScope())
{
      using (var ctx = new ApplicationDbContext(schemaName))
      {
         // Delete
         foreach (var item in queries)
         {
             // Delete queries - more than 30 - optimized already
             ctx.Database.ExecuteSqlCommand(item);                   
         }

         // Bulk Insert
         BulkInsert(ConnectionString, "Entry", "Entry", bulkTOEntry);
         BulkInsert(ConnectionString, "WrongEntry", "WrongEntry", bulkWrongEntry);
      }
      scope.Complete();          
}

The problem here is in the delete part. The delete queries are taking around 10 minutes. This results in the locking of the records, so this is holding the other users from fetching or manipulating records.

I have my code in the TransactionScope as if there is any error while deleting then it will roll back the whole transaction.

I have tried to delete the records in chunks through stored procedures, but that didn't helped here as there is still lock on the records due to the TransactionScope.

How to prevent locks on the records?

Sample of Delete Queries :-

DELETE FROM [Entry] 
WHERE CompanyId = 1 
  AND EmployeeId IN (3, 4, 6, 7, 14, 17, 20, 21, 22,....100 more) 
  AND Entry_Date = '2016-12-01' 
  AND Entry_Method = 'I'

Are you saying if you remove the `TransactionScope`, that your code runs faster? What is the SQL command you are running in your for loop? — Frank Fajardo, Jan 03 '17 at 04:40
This might give you more insight http://stackoverflow.com/questions/12751258/batch-update-delete-ef5 — Eldho, Jan 03 '17 at 05:30
@FrankFajardo Code runs similar with or without `TransactionScope`. The issue is locking of the records at the time of delete process. — Anup, Jan 03 '17 at 05:52
I was asking about the time of execution, not the result. But having seen your other SO questions for the same problem, I agree with @Eldho on using EF Extensions. — Frank Fajardo, Jan 03 '17 at 06:32

score 0 · Answer 1 · answered Jan 03 '17 at 14:00

if you need to delete the employees in chunk you can split the list of employee with this

public static List<IEnumerable<T>> Partition<T>(this IEnumerable<T> source, int length)
        {
            var count = source.Count();
            var numberOfPartitions = count / length + ( count % length > 0 ? 1 : 0);

            List<IEnumerable<T>> result= new List<IEnumerable<T>>();
            for (int i = 0; i < numberOfPartitions; i++)
            {
                result.Add(source.Skip(length*i).Take(length));
            }

            return result;
        }

you can use this method to split the list to small chunks and delete them one chunk at a time so other user can use the table between chunks

Linq - How to prevent locks when bulk delete

1 Answers1

Linked