Всегда ли изолирована транзакция, которая обновляет только одну таблицу?

Согласно UPDATE документация, UPDATE всегда приобретает эксклюзивный замок на весь стол. Однако мне интересно, установлена ​​ли монопольная блокировка до того, как будут определены строки, подлежащие обновлению, или только непосредственно перед фактическим обновлением.

Моя конкретная проблема заключается в том, что у меня есть вложенный SELECT в моем UPDATE как это:

UPDATE Tasks
SET Status = 'Active'
WHERE Id = (SELECT TOP 1 Id 
            FROM Tasks
            WHERE Type = 1
                AND (SELECT COUNT(*) 
                     FROM Tasks 
                     WHERE Status = 'Active') = 0
            ORDER BY Id)

Теперь мне интересно, действительно ли гарантировано, что существует ровно одна задача с Status = 'Active' впоследствии, если параллельно одно и то же утверждение может быть выполнено с другим типом:

UPDATE Tasks
SET Status = 'Active'
WHERE Id = (SELECT TOP 1 Id 
            FROM Tasks
            WHERE Type = 2           -- <== The only difference
                AND (SELECT COUNT(*) 
                     FROM Tasks 
                     WHERE Status = 'Active') = 0
            ORDER BY Id)

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

Если это так, как я могу предотвратить это? Могу ли я предотвратить это, не устанавливая уровень транзакции в SERIALIZABLE или возиться с подсказками блокировки?

Из ответа на вопрос: Является ли отдельный оператор SQL Server атомарным и последовательным? Я узнал, что проблема возникает, когда вложенные SELECT получает доступ к другой таблице. Однако я не уверен, должен ли я заботиться об этой проблеме, если речь идет только об обновленной таблице.

Источник

3 ответа

Решение

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

Эта статья (и серия, в которую она входит) очень хорошо объясняет тонкости параллелизма в SQL-сервере:

http://sqlperformance.com/2014/02/t-sql-queries/confusion-caused-by-trusting-acid

Источник

Если вы хотите ровно одну задачу со static = active, то настройте таблицу, чтобы убедиться, что это правда. Используйте отфильтрованный уникальный индекс:

create unique index unq_tasks_status_filter_active on tasks(status)
    where status = 'Active';

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

Полагаться на фактические планы выполнения обновлений может быть опасно. Вот почему безопаснее, чтобы база данных выполняла такие проверки. Основные детали реализации могут различаться в зависимости от среды и версии SQL Server. Например, то, что работает в однопоточной однопроцессорной среде, может не работать в параллельной среде. То, что работает с одним уровнем изоляции, может не работать с другим.

РЕДАКТИРОВАТЬ:

И я не могу устоять. В целях эффективности рассмотрим написание запроса следующим образом:

UPDATE Tasks
    SET Status = 'Active'
    WHERE NOT EXISTS (SELECT 1
                      FROM Tasks
                      WHERE Status = 'Active'
                     ) AND
          Id = (SELECT TOP 1 Id 
                FROM Tasks
                WHERE Type = 2           -- <== The only difference
                ORDER BY Id
               );

Затем разместите индексы на Tasks(Status) а также Tasks(Type, Id), На самом деле, при правильном запросе вы можете обнаружить, что запрос выполняется настолько быстро (несмотря на обновление индекса), что ваши опасения по поводу текущих обновлений значительно уменьшаются. Это не решит условия гонки, но, по крайней мере, сделает это редкостью.

И если вы фиксируете ошибки, то с уникальным отфильтрованным индексом вы можете просто сделать:

UPDATE Tasks
    SET Status = 'Active'
    WHERE Id = (SELECT TOP 1 Id 
                FROM Tasks
                WHERE Type = 2           -- <== The only difference
                ORDER BY Id
               );

Это вернет ошибку, если строка уже активна.

Примечание: все эти запросы и концепции могут быть применены к "одному активу на группу". Этот ответ касается вопроса, который вы задали. Если у вас есть проблема "один активный на группу", подумайте над тем, чтобы задать еще один вопрос.

Источник

Это не ответ на твой вопрос... Но твой запрос - боль для моих глаз:)

;WITH cte AS 
(
    SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY id)
    FROM Tasks
)
UPDATE cte
SET [Status] = 'Active'
WHERE RowNum = 1
    AND [type] = 1
    AND NOT EXISTS(
            SELECT 1
            FROM Tasks
            WHERE [Status] = 'Active'
        )
Источник
Другие вопросы по тегам