Linq-批量删除时如何防止锁定 [英] Linq - How to prevent locks when bulk delete

问题描述

按照我的代码逻辑，首先我要删除具有多个查询的大型记录&然后进行批量插入.

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

以下是代码:-

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();          
}

问题出在删除部分.删除查询大约需要10分钟.这会导致记录被锁定，因此这会阻止其他用户获取或处理记录.

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.

我的代码在TransactionScope中，好像删除时有任何错误一样，它将回滚整个事务.

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

我试图通过存储过程删除大块中的记录，但这并没有帮助，因为TransactionScope仍然使记录处于锁定状态.

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.

如何防止记录上的锁?

删除查询的示例:-

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'

推荐答案

如果您需要分块删除员工，则可以使用此方法拆分员工列表

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-批量删除时如何防止锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！