Выберите/Вставьте версию Upsert: существует ли шаблон дизайна для concurrency?

Я хочу сделать версию SELECT/INSERT для UPSERT. Ниже приведен шаблон существующего кода:

// CREATE TABLE Table (RowID INT NOT NULL IDENTITY(1,1), RowValue VARCHAR(50))

IF NOT EXISTS (SELECT * FROM Table WHERE RowValue = @VALUE)
BEGIN
   INSERT Table VALUES (@Value)
   SELECT @id = SCOPEIDENTITY()
END
ELSE
   SELECT @id = RowID FROM Table WHERE RowValue = @VALUE)

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

Существует ли метод высокого уровня concurrency для этого запроса, который позволит ему поддерживать производительность, сохраняя при этом все еще существующие данные?

Ответ 1

Вы можете использовать LOCK, чтобы сделать вещи SERIALIZABLE, но это уменьшает concurrency. Почему бы не попробовать первое условие сначала ( "в основном вставить или в основном выбрать" ), за которым следует безопасная обработка "исправления"? То есть шаблон "JFDI"...

В основном ожидаются INSERT (ball park 70-80% +):

Просто попробуйте вставить. Если это не удается, строка уже создана. Не нужно беспокоиться о concurrency, потому что TRY/CATCH имеет дело с дубликатами для вас.

BEGIN TRY
   INSERT Table VALUES (@Value)
   SELECT @id = SCOPEIDENTITY()
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
    ELSE -- only error was a dupe insert so must already have a row to select
      SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
END CATCH

В основном SELECT:

Аналогично, но сначала попробуйте получить данные. Нет данных = требуется INSERT. Опять же, если 2 одновременных вызова попытаются установить INSERT, потому что они оба обнаружили, что строка не содержит дескрипторов TRY/CATCH.

BEGIN TRY
   SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
   IF @@ROWCOUNT = 0
   BEGIN
       INSERT Table VALUES (@Value)
       SELECT @id = SCOPEIDENTITY()
   END
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
    ELSE
      SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
END CATCH

Второй, кажется, повторяется, но он очень параллелен. Замки достигнут того же, но за счет concurrency...

Edit:

Почему не использовать MERGE...

Если вы используете предложение OUTPUT, оно вернет только то, что обновлено. Поэтому для создания таблицы INSERTED для предложения OUTPUT вам понадобится фиктивный UPDATE. Если вам нужно делать фиктивные обновления со многими вызовами (как это подразумевается OP), то есть много записей в журнале, чтобы использовать MERGE.

Ответ 2

// CREATE TABLE Table (RowID INT NOT NULL IDENTITY(1,1), RowValue VARCHAR(50))

- обязательно укажите некластеризованный уникальный индекс в RowValue и RowID в качестве вашего кластерного индекса.

IF EXISTS (SELECT * FROM Table WHERE RowValue = @VALUE)
   SELECT @id = RowID FROM Table WHERE RowValue = @VALUE
ELSE BEGIN
   INSERT Table VALUES (@Value)
   SELECT @id = SCOPEIDENTITY()
END

Ответ 3

Как всегда, ответ gbn правильный и в конечном итоге приведет меня туда, где мне нужно. Тем не менее, я нашел конкретный краевой случай, который не был охвачен его подходом. Это ошибка 2601, которая идентифицирует Unique Index Violation.

Чтобы компенсировать это, я изменил его код следующим образом

...
declare @errornumber int = ERROR_NUMBER()
if @errornumber <> 2627 and @errornumber <> 2601
...

Надеюсь, это поможет кому-то!