在不锁定表的情况下插入大量记录

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语句的一部分有关。

插入大量行是自然的结果

当使用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，并打折其他选项。