插入大量记录而不锁定表

本文介绍了插入大量记录而不锁定表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试将 1,500,000 条记录插入到表中.我在插入过程中面临表锁定问题.所以我想出了下面的批量插入.

I am trying to insert 1,500,000 records into a table. Am facing table lock issues during the insertion. So I came up with the below batch insert.

DECLARE @BatchSize INT = 50000

WHILE 1 = 1
  BEGIN
      INSERT INTO [dbo].[Destination]
                  (proj_details_sid,
                   period_sid,
                   sales,
                   units)
      SELECT TOP(@BatchSize) s.proj_details_sid,
                             s.period_sid,
                             s.sales,
                             s.units
      FROM   [dbo].[SOURCE] s
      WHERE  NOT EXISTS (SELECT 1
                         FROM   dbo.Destination d
                         WHERE  d.proj_details_sid = s.proj_details_sid
                                AND d.period_sid = s.period_sid)

      IF @@ROWCOUNT < @BatchSize
        BREAK
  END

我在 Destination 表 (proj_details_sid ,period_sid) 上有一个聚集索引.NOT EXISTS 部分只是为了限制插入的记录再次插入表中

I have a clustered Index on Destination table (proj_details_sid ,period_sid ). NOT EXISTS part is just to restrict inserted records from again inserting into the table

我做得对吗，这会避免表锁吗?或者有什么更好的方法.

Am I doing it right, will this avoid table lock ? or is there any better way.

注意:使用批处理和不使用批处理插入的时间大致相同

Note : Time taken is more or less same with batch and without batch insert

推荐答案

锁升级不太可能与您语句的 SELECT 部分相关.

Lock escalation is not likely to be related to the SELECT part of your statement at all.

这是插入大文件的自然结果行数

当没有通过使用 ALTER TABLE SET LOCK_ESCALATION 选项在表上禁用锁升级，并且存在以下任一条件时，会触发锁升级:

单个 Transact-SQL 语句在单个非分区表或索引上获取至少 5,000 个锁.
单个 Transact-SQL 语句在分区表的单个分区上获取至少 5,000 个锁，并且 ALTER TABLE SET LOCK_ESCALATION 选项设置为 AUTO.
数据库引擎实例中的锁数量超过了内存或配置阈值.

如果由于锁冲突而无法升级锁，数据库引擎会在每获得 1,250 个新锁时定期触发锁升级.

If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.

您可以通过在 Profiler 中跟踪锁升级事件或简单地尝试使用不同的批处理大小来轻松地亲眼看到这一点.对我来说，TOP (6228) 显示持有 6250 个锁，但 TOP (6229) 随着锁升级开始，它突然下降到 1.确切数字可能会有所不同(取决于数据库设置和当前可用的资源).使用试错法找到为您出现锁升级的阈值.

You can easily see this for yourself by tracing the lock escalation event in Profiler or simply trying the below with different batch sizes. For me TOP (6228) shows 6250 locks held but TOP (6229) it suddenly plummets to 1 as lock escalation kicks in. The exact numbers may vary (dependant on database settings and resources currently available). Use trial and error to find the threshold where lock escalation appears for you.

CREATE TABLE [dbo].[Destination]
  (
     proj_details_sid INT,
     period_sid       INT,
     sales            INT,
     units            INT
  )

BEGIN TRAN --So locks are held for us to count in the next statement
INSERT INTO [dbo].[Destination]
SELECT TOP (6229) 1,
                  1,
                  1,
                  1
FROM   master..spt_values v1,
       master..spt_values v2

SELECT COUNT(*)
FROM   sys.dm_tran_locks
WHERE  request_session_id = @@SPID;

COMMIT

DROP TABLE [dbo].[Destination]

您要插入 50,000 行，因此几乎可以肯定会尝试锁升级.

You are inserting 50,000 rows so almost certainly lock escalation will be attempted.

文章如何解决 SQL Server 中锁升级导致的阻塞问题已经很老了，但很多建议仍然有效.

The article How to resolve blocking problems that are caused by lock escalation in SQL Server is quite old but a lot of the suggestions are still valid.

将大批量操作分解为几个较小的操作(即使用较小的批量大小)
如果不同的 SPID 当前持有不兼容的表锁，则不会发生锁升级 - 他们给出的示例是执行不同的会话

BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN

通过启用跟踪标志 1211 禁用锁升级 - 然而，这是一个全局设置，可能会导致严重的问题.有一个更新的选项 1224 问题较少，但这仍然是全球性的.

Disable lock escalation by enabling trace flag 1211 - However this is a global setting and can cause severe issues. There is a newer option 1224 that is less problematic but this is still global.

另一种选择是 ALTER TABLE blah SET (LOCK_ESCALATION = DISABLE) 但这仍然不是很有针对性，因为它影响对表的所有查询，而不仅仅是这里的单一场景.

Another option would be to ALTER TABLE blah SET (LOCK_ESCALATION = DISABLE) but this is still not very targeted as it affects all queries against the table not just your single scenario here.

所以我会选择选项 1 或可能的选项 2 并打折其他.

So I would opt for option 1 or possibly option 2 and discount the others.

这篇关于插入大量记录而不锁定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！