Вставка большого количества записей без блокировки таблицы

Расширение блокировки вряд ли вообще связано с SELECT частью вашего заявления.

Это естественное следствие вставки большого количества строк.

Расширение блокировки запускается, если расширение блокировки не отключено для таблицы с помощью параметра ALTER TABLE SET LOCK_ESCALATION и при наличии одного из следующих условий:

• Одна инструкция Transact-SQL устанавливает не менее 5000 блокировок на одну неразделенную таблицу или индекс.
• Одна инструкция Transact-SQL устанавливает не менее 5000 блокировок на один раздел многораздельной таблицы, а для параметра ALTER TABLE SET LOCK_ESCALATION установлено значение AUTO.
• Количество блокировок в экземпляре компонента Database Engine превышает пороговые значения памяти или конфигурации.

Если укрупнение блокировок невозможно из-за конфликтов блокировок, Компонент Database Engine периодически инициирует укрупнение блокировок после получения каждых 1250 новых блокировок.

Вы можете легко убедиться в этом сами, отследив событие эскалации блокировки в 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 

3. Отключите расширение блокировки, включив флаг трассировки 1211. Однако это глобальный параметр, который может вызвать серьезные проблемы. Существует более новая опция 1224, которая менее проблематична, но по-прежнему глобальна. .

Другим вариантом может быть ALTER TABLE blah SET (LOCK_ESCALATION = DISABLE), но это все еще не очень целенаправленно, поскольку влияет на все запросы к таблице, а не только на ваш единственный сценарий здесь.

Так что я бы выбрал вариант 1 или, возможно, вариант 2 и обесценил бы остальные.

Вместо того, чтобы проверять наличие данных в Destination, лучше сначала сохранить все данные во временной таблице, а затем вставить их в Destination.

Ссылка: Использование ROWLOCK в инструкции INSERT (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

Я добавил (NOLOCK) вашу целевую таблицу -> dbo.Destination (NOLOCK). Теперь, Вы не будете запирать свой стол.

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(NOLOCK) d
                         WHERE  d.proj_details_sid = s.proj_details_sid
                                AND d.period_sid = s.period_sid)

      IF @@ROWCOUNT < @BatchSize
        BREAK
  END 

Для этого вы можете использовать WITH (NOLOCK) в своем операторе выбора. НО NOLOCK не рекомендуется для баз данных OLTP.