Конкурентный upsert в SQL Server без гонки проверки

Upsert выглядит просто: обновить строку, если бизнес-ключ существует, иначе вставить новую. Проблема возникает, когда два сеанса одновременно проверяют один отсутствующий ключ. Оба решают вставлять. При уникальном ограничении один получает ошибку, а без него обе вставки могут пройти и нарушить модель данных.

Сначала определите смысл обновления. Замена предпочитаемого языка отличается от увеличения баланса или отклонения редактирования по устаревшим данным. Upsert задаёт бизнес-правило записи, а не только удобную форму SQL. Выбор блокировок должен следовать из этого правила.

Защищаем бизнес-ключ

Учебная таблица допускает одну настройку данного имени на клиента. Создавайте её только в отдельной одноразовой базе.

-- Create only in a disposable practice database.
CREATE TABLE dbo.PreferenceDemo (
    CustomerId int NOT NULL,
    PreferenceKey nvarchar(50) NOT NULL,
    PreferenceValue nvarchar(200) NOT NULL,
    CONSTRAINT PK_PreferenceDemo PRIMARY KEY (CustomerId, PreferenceKey)
);

Уникальный ключ остаётся последней защитой целостности, даже если все приложения должны использовать правильную процедуру. Включите все измерения уникальности, например TenantId для локальных идентификаторов арендатора. Для текстовых ключей согласуйте нормализацию и чувствительность к регистру: сравнение зависит от collation.

Отдельные IF NOT EXISTS и INSERT под обычным read committed подвержены гонке. Простое помещение этих инструкций в транзакцию не обязательно защищает отсутствующий ключ. Нужно защитить диапазон, в который он мог бы попасть.

Следующий пакет сначала обновляет и удерживает защиту поиска до завершения транзакции.

DECLARE @CustomerId int = 42;
DECLARE @Key nvarchar(50) = N'language';
DECLARE @Value nvarchar(200) = N'en';
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'This batch owns its transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.PreferenceDemo WITH (UPDLOCK, HOLDLOCK)
    SET PreferenceValue = @Value
    WHERE CustomerId = @CustomerId AND PreferenceKey = @Key;

    IF @@ROWCOUNT = 0
        INSERT dbo.PreferenceDemo(CustomerId, PreferenceKey, PreferenceValue)
        VALUES (@CustomerId, @Key, @Value);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

HOLDLOCK задаёт serializable-поведение для данного обращения к таблице. UPDLOCK запрашивает блокировки, ориентированные на обновление. Подходящий уникальный индекс позволяет защитить ключ либо соответствующий диапазон. Это не обещание блокировать только одну физическую строку: план доступа и окружающие операции влияют на объём блокировок.

Проверка @@ROWCOUNT должна идти сразу после UPDATE. Нельзя вставлять между ними отдельную инструкцию журналирования. Найденная строка учитывается и тогда, когда новое значение совпадает со старым, поэтому дополнительная вставка не выполняется.

Определяем допустимый конфликт

Две конкурентные записи разных языков могут выполниться последовательно. Значение определит последнее завершённое сериализованное присваивание. Это не гарантирует победу последнего запроса, поступившего веб-серверу, и не обнаруживает перезапись чужого редактирования.

Если устаревшие изменения нужно отклонять, используйте ожидаемую rowversion в предикате UPDATE и рассматривайте отсутствие совпадения как конфликт для проверки. rowversion является маркером изменения, а не датой. Создание и условная замена могут потребовать разных операций API.

Повторное присваивание того же значения обычно идемпотентно на уровне данных. Повтор команды прибавить десять уже не идемпотентен. Триггеры, аудит и внешние сообщения также могут создавать дополнительные эффекты. Для применения бизнес-запроса строго один раз нужен долговечный идентификатор запроса и правило его обработки.

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

Проверяем конкурирующие сеансы

Выполните пакет из двух соединений к одной учебной таблице, в том числе для ещё отсутствующего ключа. В управляемом тесте временно остановите один сеанс после UPDATE с открытой транзакцией и наблюдайте ожидание второго. Уберите искусственную паузу из прикладного кода.

Проверьте разные ключи, одинаковые повторные значения, нарушения ограничений и обрыв связи около COMMIT. Клиент может не знать, состоялось ли подтверждение. Слепой повтор способен продублировать эффекты; разрешайте неопределённость через идентификатор запроса или чтение авторитетного состояния.

Защита диапазона способна увеличить конкуренцию и не исключает взаимоблокировки. Обращайтесь к нескольким ключам в одинаковом порядке и сокращайте транзакции. Для жертвы deadlock повторяйте всю транзакцию с ограниченной задержкой, а не только INSERT внутри повреждённой транзакции. Отдельно проверяйте, что обработчик ошибок действительно возвращает управление вызывающей стороне, а не сообщает ложный успех. Корректный upsert сохраняет и уникальность, и согласованную политику конфликтов.

Техническая документация: Microsoft Learn: Table hints · Microsoft Learn: Transaction locking guide.