Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MS SQL Server > Как обеспечить конкурентную безопасность?

Автор: infarch 4.3.2015, 11:09

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

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

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

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

Автор: Akina 4.3.2015, 11:35

Цитата(infarch @ 4.3.2015, 12:09

)

Как избежать такой ситуации?

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

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

Код


UPDATE table
SET status = 2, clientID = 123456
FROM (SELECT TOP 1 ID
      FROM table 
      WHERE status = 1 AND cliendID IS NULL) t1
WHERE table.ID = t1.ID

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

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

Автор: infarch 4.3.2015, 13:05

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

Код


UPDATE table
SET status = 2, clientID = 123456
FROM (SELECT TOP 1 ID
      FROM table 
      WHERE status = 1 AND cliendID IS NULL) t1
WHERE table.ID = t1.ID

SELECT * FROM table WHERE status=2 AND clientID=123456

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

Автор: Akina 4.3.2015, 15:38

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

Код


SELECT COUNT(1) FROM table WHERE clientID=123456

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

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

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

Автор: infarch 4.3.2015, 16:00

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

Автор: Zloxa 7.3.2015, 13:19

Цитата(infarch @ 4.3.2015, 12:09

)

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

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

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

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

Автор: Akina 7.3.2015, 23:42

Цитата(Zloxa @ 7.3.2015, 14:19)

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

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

Автор: Zloxa 8.3.2015, 01:34

МС разве не умеет возвращать rowcount у апдейта? smile

Если 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, 01:52

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

Автор: infarch 30.3.2015, 16:11

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

Код


use test;
declare @StartTime datetime = '2015-03-13 13:20';
declare @MinutesToAdd int = 1;
while 1=1
begin
    waitfor time @StartTime;
    
    UPDATE ServerTaskRequest
    SET [Status] = 2, ServerTaskManager_ID = 1
    --output deleted.id INTO @MyTableVar
    FROM (SELECT TOP 1 ID
          FROM ServerTaskRequest 
          WHERE [Status] = 1 AND ServerTaskManager_ID IS NULL) t1
    WHERE ServerTaskRequest.ID = t1.ID;

    set @StartTime=DATEADD(minute, @MinutesToAdd, @StartTime);
end

Я запустил его в десяти окнах 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.

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

Код


use test;
declare @taken table(ID int not null);
DECLARE @StartTime dateTIME = '2015-03-13 13:56';
DECLARE @MinutesToAdd INT = 1;
while 1=1
begin
    waitfor time @StartTime;

    UPDATE top(1) ServerTaskRequest
        SET [Status] = 2, ServerTaskManager_ID = 2
        output deleted.id INTO @taken
        WHERE [Status] = 1 AND ServerTaskManager_ID IS NULL;

    set @StartTime=DATEADD(minute, @MinutesToAdd, @StartTime);
end

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

Автор: Zloxa 31.3.2015, 01:15

Цитата(infarch @ 30.3.2015, 17:11

)

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.

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

Цитата(infarch @ 30.3.2015, 17:11

)

top(1)

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

Автор: infarch 31.3.2015, 11:34

Цитата(Zloxa @ 31.3.2015, 01:15

)

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

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

Автор: Zloxa 31.3.2015, 14:28

Цитата(infarch @ 31.3.2015, 12:34

)

Если ордера нет то выдаст первую запись согласно очередности добавления в базу. Или нет?..

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

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

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

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

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

Автор: Zloxa 31.3.2015, 14:53

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

Код


    UPDATE ServerTaskRequest
    SET [Status] = 2, ServerTaskManager_ID = 1
    --output deleted.id INTO @MyTableVar
    FROM (SELECT TOP 1 ID
          FROM ServerTaskRequest 
          WHERE [Status] = 1 AND ServerTaskManager_ID IS NULL) t1
    WHERE ServerTaskRequest.ID = t1.ID;

Как я понимаю как это происходит:
Сессия 1, выполняя select подзапрос накладываетhttps://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx по предикату [Status] = 1
Сессия 2, выполняя select подзапрос накладывает shared лок по предикату [Status] = 1
Сессия 1, приступая к апдейту пытается произвести захват update лока по Status, блокируется сессией 2, удерживающей шаред лок
Сессия 2, приступая к апдейту пытается произвести захват update лока по Status, блокируется сессией 1, удерживающий шаред лок, и, в свою очередь блокированной сессией 2 - дедлок

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

Ессли я правильно поинмаю, выкрутиться тут можно - перевести подзапрос в грязные чтения (https://msdn.microsoft.com/ru-ru/library/ms187373.aspx) и where апдейта дописать and status = 1. Врде б должно попустить.