插入大量记录而不锁定表

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

我做得对吗，这会避免表锁吗？或者有没有更好的方法。

注意： 所用时间或多或少与批处理和没有批处理插入相同

原文由 Pரதீப் 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

锁升级不太可能与您声明的 SELECT 部分有关。

这是 插入大量行的自然结果

当未使用 ALTER TABLE SET LOCK_ESCALATION 选项在表上禁用锁升级时，并且存在以下任一情况时，将触发锁升级：

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

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

您可以通过跟踪 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 行，因此几乎可以肯定会尝试锁定升级。

这篇文章 如何解决由 SQL Server 中的锁升级引起的阻塞问题 已经很老了，但是很多建议仍然有效。

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

---

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

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

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

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

原文由 Martin Smith 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

与其检查 Destination 中是否存在数据，不如先将所有数据存储在临时表中，然后批量插入 Destination

参考： 在 INSERT 语句中使用 ROWLOCK (SQL Server)

 DECLARE @batch int = 100
DECLARE @curRecord int = 1
DECLARE @maxRecord int

-- remove (nolock) if you don't want to have dirty read
SELECT row_number over (order by s.proj_details_sid, s.period_sid) as rownum,
       s.proj_details_sid,
       s.period_sid,
       s.sales,
       s.units
INTO #Temp
FROM   [dbo].[SOURCE] s WITH (NOLOCK)
WHERE  NOT EXISTS (SELECT 1
                   FROM   dbo.Destination d WITH (NOLOCK)
                   WHERE  d.proj_details_sid = s.proj_details_sid
                          AND d.period_sid = s.period_sid)

-- change this maxRecord if you want to limit the records to insert
SELECT @maxRecord = count(1) from #Temp

WHILE @maxRecord >= @curRecord
   BEGIN
       INSERT INTO [dbo].[Destination]
              (proj_details_sid,
               period_sid,
               sales,
               units)
       SELECT proj_details_sid, period_sid, sales, units
       FROM #Temp
       WHERE rownum >= @curRecord and rownum < @curRecord + @batch

       SET @curRecord = @curRecord + @batch
   END

DROP TABLE #Temp

原文由 Prisoner 发布，翻译遵循 CC BY-SA 3.0 许可协议