sql - 插入大量记录而不锁定表

Question

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

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 部分只是限制插入的记录再次插入表中

我这样做对吗，这会避免表锁吗？或者有什么更好的办法。

注意:与批量和没有批量插入所花费的时间大致相同

Answer 1

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

这是一个natural consequence of inserting a large number of rows

Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

• A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
• A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
• The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

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。确切的数字可能会有所不同(取决于数据库设置)以及当前可用的资源)。通过反复试验来找到为您出现锁升级的阈值。

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 行，因此几乎肯定会尝试锁定升级。

文章How to resolve blocking problems that are caused by lock escalation in SQL Server虽然很旧，但很多建议仍然有效。

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

<小时/>

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

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

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

所以我会选择选项 1 或可能选择选项 2，而对其他选项打折扣。