Здравствуйте.

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

Напимер таблица Task:
Id
Status
Data

Клиент обращается к ней и берет первую запись со статусом 1, меняет его на 2 и обрабатывает данные. Но ведь есть опасность что другой клиент в то самое время сделает свой запрос и получит ту самую строку. Как избежать такой ситуации? В моем случае абсолютно недопустимо отдавать одну строку на обработку два раза.

Ну например так.

В изменённом статусе фиксируется не (только) текущий статус, а (в том числе) уникальный идентификатор клиента. Как вариант - в специальном поле. Например:

Сразу же проверяем, что существует запись с clientID = 123456. Если да - то спокойно обрабатываем эту запись (причём тупо фильтруем её по clientID), а по завершении обработки опять обNULLяем поле clientID, если нет - значит, кто-то успел раньше, и пробуем зарезервить за собой другую запись, повторно выполняя запрос.

При таком подходе всегда можно получить сведения о текущей обработке - какие записи, кем обрабатываются, на какой стадии процесс. И даже при слёте обработки можно перезапустить клиента, который, обнаружив "свою" запись, продолжит обработку в соответствии с текущим статусом. Если же обнаружена совсем залипшая недообработанная запись - можно передать её на дообработку другому клиенту, просто изменив clientID. Только убедиться, что этот клиент в данный момент ничего не обрабатывает. Например, выполняя изменение clientID с опцией TABLELOCK.

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



Достаточно ли этого для уверенности в уникальности данных? Надо ли указать уровень изоляции транзакции?

ПРЕДВАРИТЕЛЬНО Вы делаете запрос 

Если всё в порядке - запрос обязан вернуть ноль. Если НЕ ноль - есть подвисшие недообработанные записи, и начинать надо с них.

Если запрос вернул ноль - то всё нормально, можно резервировать запись и работать с ней. 

В последнем запросе условие status=2 лишнее - потому как при нормальном течении процесса, и с учётом предварительной проверки, запись с clientID=123456 строго одна. Или, если при апдейте произошло какая-то накладка, то ни одной - и тогда надо повторно резервировать запись.

Я понял. Спасибо, буду применять на практике.

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

В вашем случае, следует использовать режим изоляции не  ниже read commited. Захват задачи считать успешным, если для нее успешно отработал update set status =2 where status = 1. Два разных клиента, работающих в режиме изоляции read commited выполнить такую операцию над одной строкой не смогут. 

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

Это сообщение отредактировал(а) Zloxa - 7.3.2015, 13:24

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

МС разве не умеет возвращать rowcount у апдейта? 
Если rowcount >0 значит захватил я. Нет?

Добавлено @ 01:38
Посмотрел. Умеет https://msdn.microsoft.com/ru-ru/library/ms187316.aspx
Добавлено @ 01:38
Вычитал ещё и по аутпут, здесь тоже будет полезен https://msdn.microsoft.com/ru-ru/library/ms177564.aspx

Это сообщение отредактировал(а) Zloxa - 8.3.2015, 02:08

А в чем есть проблема, так в том, что тут конкурирующие процессы будут выстраивается в очередь. И если обработка реализована в той же транзакции, что и захват, парллелить обработку не получится. Как реализовать на МС "Возьми из очереди первый никем пока не блокированый" я не знаю и, всамделе, очень хотел бы узнать.

Хочу написать о результатах тестирования. Вот запрос:



Я запустил его в десяти окнах SSMS и стал следить. На первой же итерации несколько окон выдали ошибку:

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Пробовал заворачивать update в тарнзакцию, указывал разные уровни изоляции. Ошибка по прежнему случалась. Единственный момент отклонения от этой грустной системы был в том, что при изоляции SNAPSHOT ошибка вышла другая:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.ServerTaskRequest' directly or indirectly in database 'Test' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Потом я запрос упростил:



В таком виде проблем не наблюдается, однако мне будет спокойнее если общественность выскажет своё "фи" )

Это капец.  Реализация write consistency через анальный запрет конкурентной записи.  

Топ 1 без ордербая означает любой 

Мне любой вполне подходит, главное чтоб только один и согласно where. Да и не такой уж он и любой... Если ордера нет то выдаст первую запись согласно очередности добавления в базу. Или нет?..

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

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

Повторюсь, я не знаю как такие вещи делать на MS SQL, потому и слежу за этой темой - интересно. 

В лоб бы я ее решал - открыаем однопроходный курсор (можно грязными чтениями) и обходя его в цикле до первого успешного апдейта (в режиме read commited и обязательно с доп условием по статусу). При этом я врядли избежал бы толкотни на блокировках.

Дедлоки из первого примера меня очень огорчили. Ну и особенности реализации снапшота тоже. Таки писать под MS это садамаза, хоть он и куда няшее оракли.... с первого взгляду.


Это сообщение отредактировал(а) Zloxa - 31.3.2015, 14:36

Не выходят у меня из головы эти дедлоки.


Как я понимаю как это происходит:
Сессия 1, выполняя select подзапрос накладывает shared лок по предикату [Status] = 1
Сессия 2, выполняя select подзапрос накладывает shared лок по предикату [Status] = 1
Сессия 1, приступая к апдейту пытается произвести захват update лока по Status, блокируется сессией 2, удерживающей шаред лок
Сессия 2,  приступая к апдейту пытается произвести захват update лока по Status, блокируется сессией 1, удерживающий шаред лок, и, в свою очередь блокированной сессией 2 - дедлок

Ребят, кто знает - правильно я полагаю? Правильно поинмаю как обеспечивает изоляцию MS? Он же блокирует по предикату?

Ессли я правильно поинмаю, выкрутиться тут можно - перевести подзапрос в грязные чтения (хинт nolock) и where апдейта дописать and status = 1. Врде б должно попустить.

Это сообщение отредактировал(а) Zloxa - 31.3.2015, 14:57