В SQL Server, как я могу заблокировать одну строку так же, как Oracle "SELECT FOR UPDATE WAIT"?

У меня есть программа, которая подключается к базе данных Oracle и выполняет на ней операции. Теперь я хочу адаптировать эту программу, чтобы также поддерживать базу данных SQL Server.

В версии Oracle я использую "SELECT FOR UPDATE WAIT" для блокировки определенных строк, которые мне нужны. Я использую его в ситуациях, когда обновление основано на результатах SELECT, а другие сеансы могут абсолютно не изменять его одновременно, поэтому они должны сначала вручную заблокировать его. Система очень подвержена сеансам, пытающимся получить доступ к тем же данным одновременно.

Например:
Два пользователя пытаются получить строку в базе данных с наивысшим приоритетом, пометить ее как занятую, выполнить операции над ней и пометить ее как доступную снова для последующего использования. В Oracle логика в основном будет выглядеть так:

BEGIN TRANSACTION;
SELECT ITEM_ID FROM TABLE_ITEM WHERE ITEM_PRIORITY > 10 AND ITEM_CATEGORY = 'CT1'
    ITEM_STATUS = 'available' AND ROWNUM = 1 FOR UPDATE WAIT 5;
UPDATE [locked item_id] SET ITEM_STATUS = 'unavailable';
COMMIT TRANSACTION;

Обратите внимание, что запросы строятся динамически в моем коде. Также обратите внимание, что, когда ранее наиболее благоприятная строка помечена как недоступная, второй пользователь автоматически перейдет к следующему и так далее. Кроме того, различным пользователям, работающим в разных категориях, не придется ждать, пока не будут выпущены блокировки. Хуже всего, через 5 секунд будет возвращена ошибка, и операция будет отменена.

Итак, наконец, возникает вопрос: как мне добиться тех же результатов в SQL Server? Я смотрю на блокирующие подсказки, которые, по идее, кажутся, что они должны работать. Однако единственными замками, которые предотвращают другие блокировки, являются "UPDLOCK" и "XLOCK", которые работают только на уровне таблицы.
Эти блокирующие подсказки, которые работают на уровне строк, представляют собой общие блокировки, которые также не удовлетворяют моим потребностям (оба пользователя могут одновременно блокировать одну и ту же строку, оба отмечают ее как недоступную и выполняют избыточные операции над соответствующим элементом).

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

Ответ 1

В SQL Server есть намеки на блокировку, но они не охватывают их выражения, как пример Oracle, который вы предоставили. Способ сделать это в SQL Server - установить уровень изоляции транзакции, содержащей инструкции, которые вы хотите выполнить. См. эту страницу MSDN, но общая структура будет выглядеть примерно так:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    select * from ...

    update ...

COMMIT TRANSACTION;

SERIALIZABLE - это самый высокий уровень изоляции. См. Ссылку для других опций. Из MSDN:

SERIALIZABLE Задает следующее:

Заявления не могут читать данные, которые были изменены, но еще не совершенные другими транзакциями.

Никакие другие транзакции не могут изменять данные, которые были прочитаны текущей транзакции до завершения текущей транзакции.

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

Ответ 2

Вероятно, вы ищете with (updlock, holdlock). Это сделает select захват блокировки exclusive, которая требуется для обновлений, вместо блокировки shared. Подсказка holdlock сообщает SQL Server о блокировке до завершения транзакции.

FROM TABLE_ITEM with (updlock, holdlock)

Ответ 3

Вы пробовали WITH (ROWLOCK)?

BEGIN TRAN

   UPDATE your_table WITH (ROWLOCK)
   SET your_field = a_value
   WHERE <a predicate>

COMMIT TRAN

Ответ 4

Как документация sayed:

XLOCK

Указывает, что эксклюзивные блокировки должны быть взяты и удерживаться до тех пор, пока транзакция завершена. Если указано с помощью ROWLOCK, PAGLOCK или TABLOCK, эксклюзивные блокировки применяются к соответствующему уровню детализации.

Итак, решение использует WITH(XLOCK, ROWLOCK):

BEGIN TRANSACTION;

SELECT ITEM_ID
FROM TABLE_ITEM
WITH(XLOCK, ROWLOCK)
WHERE ITEM_PRIORITY > 10 AND ITEM_CATEGORY = 'CT1' AND ITEM_STATUS = 'available' AND ROWNUM = 1;

UPDATE [locked item_id] SET ITEM_STATUS = 'unavailable';

COMMIT TRANSACTION;