Бесконечные дискуссии: атомные вставки, блокировка и транзакции в SQL Server. Это заканчивается здесь

Изменить: Мой вопрос: "Почему мой первый пример кода работает?" Пожалуйста, прочитайте...

Edit1: Нет сомнений в том, что уникальное ограничение - это правильный способ гарантировать, что дубликаты не будут выполняться. Это данность. Однако когда-нибудь нам нужно знать, что мы пытаемся дублировать запись. Кроме того, этот пост выходит за рамки просто обработки дубликатов.

Это потенциально повторение, вероятно, более 100 вопросов о SO. Я читал бесконечное множество путаниц и противоречий о простых вопросах атомных обновлений, замков и concurrency.

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

Контекст: я запускаю SQL Server 2008 Express SP2.

Я создал следующую тестовую таблицу:

create table dbo.Temp (Col int)

В таблице преднамеренно нет ограничений, так как мы хотим проверить идеи кода SQL, а не ограничения.

Я запускал следующее одновременно в 2, а затем 3 окна запросов:

declare @i int
set @i = 0

while @i < 5000 begin
    set @i = @i + 1
    update dbo.temp set Col = (SELECT Col from dbo.Temp) + 1
end

Я не использовал никакой явной блокировки, как видно. Все настройки базы данных по умолчанию. Я проверил значение Col, и это было желаемое число: 25 000. Ничего не пропустило.

Поскольку SQL Server является ACID, "A" сообщает нам, что один оператор выполняется атомарно. Поэтому, исходя из вышесказанного, мы можем согласиться с теми, кто говорит, что блокировки не нужны для простого обновления, как указано выше.

Затем я запускал следующее одновременно в трех окнах запросов:

while @i < 5000 begin
    set @i = @i + 1
    insert into dbo.temp select @i where not exists
      (select 1 from dbo.temp where Col = @i)
end

Результаты были не правильными, несмотря на то, что это одно утверждение. Были отсутствующие значения, повторяющиеся значения и строки > 5k.

Затем я сразу же запустил следующее популярное решение в трех окнах запросов:

declare @i int
set @i = 0

while @i < 5000 begin
    set @i = @i + 1
    insert into dbo.temp select @i where not exists 
      (select 1 from dbo.temp with (updlock) where Col = @i)
end

Результаты были не правильными. Были отсутствующие значения, повторяющиеся значения и строки > 5k.

Далее, для тех, кто сомневается, что SQL Server неявно обертывает отдельные операторы в транзакции (иначе говоря, "A" в ACID):

declare @i int
set @i = 0

while @i < 5000 begin
    set @i = @i + 1
    begin tran
    insert into dbo.temp select @i where not exists 
      (select 1 from dbo.temp with (updlock) where Col = @i)
    commit tran
end

Тот же неверный результат.

Затем я запускал следующее одновременно в трех окнах запросов:

declare @i int
set @i = 0

while @i < 5000 begin
    set @i = @i + 1
    insert into dbo.temp select @i where not exists 
      (select 1 from dbo.temp with (XLOCK, ROWLOCK) where Col = @i)
end

Этот работал. Только 5 тыс. Уникальных значений.

Далее, в трех окнах:

declare @i int
set @i = 0

while @i < 5000 begin
    set @i = @i + 1
    merge dbo.temp as t
    using (select @i) as test (Col)
    ON (t.Col = test.Col)
    when not matched then
        insert values (@i);
end

Этот работал. Только 5 тыс. Уникальных значений.

Мои выводы:

• SQL Server является атомарным для операций с одним SQL, а не с группами операций, которые отображаются как один оператор.
• UPDLOCK не является эффективным, как показано на рисунке, и как это было предложено много, при предоставлении необходимой блокировки, необходимые для обеспечения целостности обновления в спорном среде.
• С точки зрения блокировки только XLOCK гарантирует целостность и атомарность concurrency в операциях с несколькими операторами, как показано.
• Команда MERGE является единственной командой и поэтому является атомарной.

Пожалуйста, проверьте это самостоятельно.

Теперь, может кто-нибудь объяснить мне, почему мой первый пример работал?: -)

Ответы