Как реализовать условную хранимую процедуру Upsert?

Очень распространенная проблема. Некоторые подходы не работают при высоком уровне параллелизма. Описано и проведено стресс-тестирование здесь:

Стресс-тесты UPSERT

Защитное программирование базы данных: исключение операторов IF.

В таких случаях недостаточно просто написать какой-то код, вам нужно его раскрыть к высокому параллелизму. Например, я не уверен, что понял, что CptSkippy рекомендует, но ниже показано, как проводить стресс-тест. Настройте таблицу и процедуру:

CREATE TABLE [dbo].[TwoINTs](
      [ID] [int] NOT NULL,
      [i1] [int] NOT NULL,
      [i2] [int] NOT NULL,
      [i3] [int] NOT NULL
);
CREATE PROCEDURE dbo.SaveTwoINTs(@ID INT, @i1 INT, @i2 INT)
AS
BEGIN
      SET NOCOUNT ON;
      SET XACT_ABORT OFF;
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
      DECLARE @ret INT;
      SET @ret=0;
      BEGIN TRAN; 
IF EXISTS(SELECT 1 FROM dbo.TwoINTs WHERE ID=@ID) BEGIN
      UPDATE dbo.TwoINTs WITH (SERIALIZABLE)
         SET i1=i1+@i1, i2=i2+@i2 WHERE ID=@ID;
      SET @ret=@@ERROR;
END ELSE BEGIN
     INSERT INTO dbo.TwoINTs(ID, i1, i2, i3)VALUES(@ID, @i1, @i2, @i1);
      SET @ret=@@ERROR;
END;
COMMIT;
RETURN @ret;
END
GO

Настройте два цикла, которые выполняют эту процедуру:

CREATE PROCEDURE Testers.UpsertLoop1
AS
BEGIN
DECLARE @ID INT, @i1 INT, @i2 INT, @count INT, @ret INT;
SET @count = 0;
WHILE @count<50000 BEGIN
      SELECT @ID = COALESCE(MAX(ID),0) + 1 FROM dbo.TwoInts;
    EXEC @ret=dbo.SaveTwoINTs @ID, 1, 0;
      SET @count = @count + 1;
END;
END;
GO
CREATE PROCEDURE Testers.UpsertLoop2
AS
BEGIN
DECLARE @ID INT, @i1 INT, @i2 INT, @count INT, @ret INT;
SET @count = 0;
WHILE @count<50000 BEGIN
      SELECT @ID = COALESCE(MAX(ID),0) + 1 FROM dbo.TwoInts;
    EXEC @ret=dbo.SaveTwoINTs @ID, 0, 1;
      SET @count = @count + 1;
END;
END;

Выполните эти процедуры на двух вкладках и убедитесь сами, что вы получаете много ошибок:

Testers.UpsertLoop1 --run in one tab
Testers.UpsertLoop1 --run in one tab

Msg 2601, Level 14, State 1, Procedure SaveTwoINTs, Line 15
Cannot insert duplicate key row in object 'dbo.TwoINTs' with unique index 'UNQ_TwoInts_ID'.
The statement has been terminated.

Перейдите по ссылкам, которые я предоставил чтобы увидеть подходы, которые действительно работают в условиях параллелизма.

Я собрал следующий сценарий, чтобы проверить этот трюк, который использовал в прошлые годы. Если вы его используете, вам нужно будет изменить его в соответствии с вашими целями. Далее следуют комментарии:

/*
CREATE TABLE Item
 (
   Title      varchar(255)  not null
  ,Teaser     varchar(255)  not null
  ,ContentId  varchar(30)  not null
  ,RowLocked  bit  not null
)


UPDATE item
 set RowLocked = 1
 where ContentId = 'Test01'

*/


DECLARE
  @Check varchar(30)
 ,@pContentID varchar(30)
 ,@pTitle varchar(255)
 ,@pTeaser varchar(255)

set @pContentID = 'Test01'
set @pTitle     = 'TestingTitle'
set @pTeaser    = 'TestingTeasier'

set @check = null

UPDATE dbo.Item
 set
   @Check = ContentId
  ,Title  = @pTitle
  ,Teaser = @pTeaser
 where ContentID = @pContentID
  and RowLocked = 0

print isnull(@check, '<check is null>')

IF @Check is null
    INSERT dbo.Item (ContentID, Title, Teaser, RowLocked)
     values (@pContentID, @pTitle, @pTeaser, 0)

select * from Item

Уловка здесь в том, что вы можете устанавливать значения в локальных переменных в операторе Update. Выше значение «флага» устанавливается только в том случае, если обновление работает (т. Е. Выполняется условие обновления); в противном случае он не будет изменен (здесь, оставлен нулевым), вы можете проверить это и обработать соответствующим образом.

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

- Дополнения, продолжение второго комментария ниже -----------

Г-н. Шафран' Идеи представляют собой исчерпывающий и надежный способ реализации этой процедуры, поскольку ваши первичные ключи определяются извне и передаются в базу данных (т. е. вы не используете столбцы идентификаторов - хорошо, по мне, они часто используются чрезмерно).

еще несколько тестов (добавлено ограничение первичного ключа в столбце ContentId, оберните UPDATE и INSERT в транзакцию, добавьте сериализуемую подсказку к обновлению) и да, это должно сделать все, что вы хотите. Неудачное обновление приводит к блокировке диапазона в этой части индекса, что блокирует любые одновременные попытки вставить это новое значение в столбец. Конечно, если N запросов отправлены одновременно, «первый» создаст строку, и она будет немедленно обновлена ​​вторым, третьим и т. Д. - если вы не установите «блокировку» где-то вдоль строки. Хорошая уловка!

BEGIN TRANSACTION

IF EXISTS(SELECT 1 FROM dbo.Item WHERE ContentID = @pContentID)
     UPDATE dbo.Item WITH (SERIALIZABLE)
     SET Title = @pTitle, Teaser = @pTeaser
     WHERE ContentID = @pContentID
     AND RowLocked = false
ELSE
     INSERT INTO dbo.Item
          (ContentID, Title, Teaser)
     VALUES
          (@pContentID, @pTitle, @pTeaser)

COMMIT TRANSACTION

Вы можете изменить порядок обновления / вставки. Таким образом, вы выполняете вставку в try / catch, и если вы получаете нарушение ограничения, выполняйте обновление. Хотя он кажется немного грязным.

СОЗДАТЬ ПРОЦЕДУРУ [dbo]. [Usp_UpsertItem] - Добавьте сюда параметры хранимой процедуры @pContentID varchar (30) = ноль, @pTitle varchar (255) = ноль, @pTeaser varchar (255) = ноль КАК НАЧАТЬ - Добавлен SET NOCOUNT ON для предотвращения дополнительных наборов результатов - вмешательство в операторы SELECT. SET NOCOUNT ON;

BEGIN TRANSACTION
    IF EXISTS (SELECT 1 FROM dbo.Item WHERE ContentID = @pContentID
             AND RowLocked = false)
       UPDATE dbo.Item 
       SET Title = @pTitle, Teaser = @pTeaser
       WHERE ContentID = @pContentID
             AND RowLocked = false
    ELSE IF NOT EXISTS (SELECT 1 FROM dbo.Item WHERE ContentID = @pContentID)
            INSERT INTO dbo.Item (ContentID, Title, Teaser)
            VALUES (@pContentID, @pTitle, @pTeaser)

COMMIT TRANSACTION

END