Атомный UPSERT в SQL Server 2005

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
FROM <table>
WHERE NOT EXISTS
   -- race condition risk here?
   ( SELECT 1 FROM <table> WHERE <natural keys> )

UPDATE ...
WHERE <natural keys>

• в первом INSERT есть состояние гонки. Ключ может не существовать во время внутреннего запроса SELECT, но существует во время INSERT, что приводит к нарушению ключа.
• существует состояние гонки между INSERT и UPDATE. Ключ может существовать при проверке во внутреннем запросе INSERT, но исчезнет к моменту выполнения UPDATE.

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

Оптимальное решение обычно состоит в том, чтобы попробовать наиболее вероятный случай и обработать ошибку в случае сбоя (конечно, внутри транзакции):

• если ключ, вероятно, отсутствует, всегда вставляйте первым. Обработка нарушения уникального ограничения, откат к обновлению.
• если ключ, вероятно, присутствует, всегда обновляйте первым. Вставить, если строка не найдена. Обработка возможного нарушения уникального ограничения, откат к обновлению.

Помимо корректности, этот шаблон также оптимален с точки зрения скорости: более эффективно пытаться вставить и обработать исключение, чем создавать ложные блокировки. Блокировки означают чтение логической страницы (что может означать чтение физической страницы), а ввод-вывод (даже логический) дороже, чем SEH.

Обновление @Peter

Почему ни один оператор не является «атомарным»? Допустим, у нас есть тривиальная таблица:

create table Test (id int primary key);

Теперь, если бы я запустил этот единственный оператор из двух потоков в цикле, он был бы «атомарным», как вы говорите, условие отсутствия гонки может существовать:

  insert into Test (id)
    select top (1) id
    from Numbers n
    where not exists (select id from Test where id = n.id); 

Тем не менее всего за пару секунд происходит нарушение первичного ключа:

Сообщение 2627, уровень 14, состояние 1, строка 4
Нарушение ограничения PRIMARY KEY 'PK__Test__24927208'. Невозможно вставить повторяющийся ключ в объект 'dbo.Test'.

Почему? Вы правы в том, что план запроса SQL сделает «правильную вещь» на DELETE ... FROM ... JOIN , на WITH cte AS (SELECT ... FROM) DELETE FROM cte и во многих других случаях.Но в этих случаях есть принципиальное различие: «подзапрос» относится к цели операции update или delete . Для таких случаев план запроса действительно будет использовать соответствующую блокировку, на самом деле такое поведение критично в определенных случаях, например, при реализации очередей Использование таблиц как очередей .

Но в исходном вопросе, а также в моем примере, подзапрос рассматривается оптимизатором запросов как подзапрос в запросе, а не как некий специальный запрос типа «сканирование для обновления», который требует специальной защиты от блокировки. В результате параллельный наблюдатель может наблюдать выполнение подзапроса как отдельную операцию, что нарушает «атомарное» поведение оператора. Если не будут приняты особые меры предосторожности, несколько потоков могут попытаться вставить одно и то же значение, оба убеждены, что они проверили, и значение еще не существует. Только один может добиться успеха, другой попадет в нарушение ПК. QED.