SQL Server - проблема дизайна

[katit]

Вообщем есть такое дело:

Предистория:
Имеется склад, на нем люди ходят и собирают товар (пакуют в коробочки).
Все это они делают сканерами. Суть такая что все это проишодит без бумажки.

Задача проследить чтобы 2 человека не собирали один и тот-же заказ. Система не позволит, конечно, одно и то-же двум людям собрать и ввести, но она позволит это начать делать.

Для того чтобы такого не проишодило, я сделал таблицу где собираю "locks" - a попросту комбинацию пользователь/заказ и timestamp.

Vse rabotalo prekrasno, no tam ochen' nehilaya nagruzka (ya s takimi ob'emami ne stalkivalsya)...

Проишодит вот что: Когда пользователь входит, запускается SP которая выбирает первый "not locked" заказ и дает его пользователю. Естественно после первой проверки идет еще немного кода, потом уже втыкается новый "lock" для этого пользователя.

Работает все путем, но вот иногда появляется такая беда(см картинку первые два):


Я-то понимаю что это проишодит т.к. проверка прошла у первого пользователя и пока я что-то другое делал в SP(перед тем как вставить lock) другой пользователь сделал то-же.

Как это лечить ? Есть-ли общие методы?
Ключ сделан так что он позволяет двум пользователям сделать это. Но если даже его изменить то не понимаю как ошибки будет ловить ?

[Dmitry67]

Если нет connection pooling используйте sp_getapplock
Если есть то жтими функциями воспользоваться неудасться а надо просто четко отрабатывать 'критическую секцию'; то есть ВНАЧАЛЕ вставлять блокирувку, а потом проверять не стала ли она ВТОРОЙ

[katit]

А есть ли возможность блокировать таблицу ?
Т.е. когда моя SP запускается то каким-то образом блокировать эту одну таблицу и в конце отпускать. Чтобы остальные вызовы стояли в очереди. Это будет нормальным решением в данной ситуации


P.S. Там нет connection pooling. Но я не знаю что будет завтра. Все постоянно меняется, хотелось-бы по возможности попроще.

[Victor]

Блокировать одну только запись с которой вы работаете не достаточно?
Если да, то можно использовать SELECT c locking hint UPDLOCK при выборке записи с которой работаете.

Разумеется для того, чтою все это работало надо явно открывать и закрывать транзакцию

[katit]

Да нет, там записи то нет как таковой...

Вобщем буду наверное еще одну проверку вставлят непосредственно перед блокировкой. Единственное что если проверка будет не проходить то правильно было-бы циклить пока не найдется подходящий заказ....

Хотя... Во! просто поставлю красивый GOTO чтобы прыгать к началу

[Victor]

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

[katit]

но что-то там с дизайном похоже не в порядке...

Ну так про это и вопрос. Наверняка ситуация стандартная когда надо такое делать. Во меня и интересует как это делается

[Dmitry67]

Вам не надо блокировать таблицу
Вам надо блокировать ресурс
Выдумайте имя ресурсу 'MyLineOfRequests' и блокируйте его в sp_getapplock

[Victor]

но что-то там с дизайном похоже не в порядке...

Ну так про это и вопрос. Наверняка ситуация стандартная когда надо такое делать. Во меня и интересует как это делается

Ситуация действительно стандартна.
0) Имеем таблицу заказов. В ней есть поле - начало упаковки или Id упаковшика
1) BEGIN TRAN
2) SELECT TOP 1 OrderID INTO @OrderId FROM Orders WITH (UPDLOCK) WHERE PackagerId IS NULL
3) UPDATE Orders SET PackagerId = @EmploeeId WHERE OrderId = @OrderId
4) Делаем сопутствующие операции
5) COMMIT TRAN

В результате 2 упаковщика никогда не смогут получить один заказ.

[katit]

У меня немного по другому..
А именно - отдельная таблица хранит данные об упаковщике. Т.е. я и INSERT и UPDATE

Не понимаю смысл WITH (UPDLOCK) в вашем примере?
Или это как-то с транзакцией работает?

Думаю что моей главной проблемой было то что у меня 3 и 4 местами поменяны. Я переставил и думаю что проблема должна уйти

[Victor]

У меня немного по другому..
А именно - отдельная таблица хранит данные об упаковщике. Т.е. я и INSERT и UPDATE

Не понимаю смысл WITH (UPDLOCK) в вашем примере?
Или это как-то с транзакцией работает?

Это не дает сделать этот же SELECT (получить update lock) и внести изменения для той же самой записи из другого SQL процесса пока не закончится транзакция
#4 является не обязательным. Вы можете получить OrderId, закончить транзакцию и, например, распечатать сожержимое заказа для упаковщика.

[Victor]

Думаю что моей главной проблемой было то что у меня 3 и 4 местами поменяны. Я переставил и думаю что проблема должна уйти

Она не уйдет - просто будет возникать гораздо реже.

Поставьте WITH (UPDLOCK) и сделайте транзакцию максимально короткой. Все долгие манипуляции с OrderId лучше из нее вынести.

[Dmitry67]

1) BEGIN TRAN
2) SELECT TOP 1 OrderID INTO @OrderId FROM Orders WITH (UPDLOCK) WHERE PackagerId IS NULL
3) UPDATE Orders SET PackagerId = @EmploeeId WHERE OrderId = @OrderId
4) Делаем сопутствующие операции
5) COMMIT TRAN

В результате 2 упаковщика никогда не смогут получить один заказ.

Я понял что Вы хотите

Code: Select all

BEGIN TRAN
  SET ROWCOUNT 1
  -- get one record randomly
  UPDATE Orders set PackagerId = @EmploeeId,spid=@@spid 
    WHERE PackagerId IS NULL
  if @@rowcount=0 -- nothing to update ? dont forget to set rowcount 0
    goto somewhere
  SET ROWCOUNT 0
  -- get the updated id
  select ... from Orders where spid=@@spid
  -- dont forget to reset spid later

[Dmitry67]

ps
Если @PackageId уникален то колонку spid можно не вводить

[katit]

Так еще раз уточню..

Суть в связке BEGIN/END TRAN + WITH (UPDLOCK) ? Верно?

Просто я здесь не использую транзакцию.

Вот код который сейчас там:

Code: Select all

TryAgain:

   SELECT   @_oSalesOrderID = MIN(SrcTranNo)
   FROM   vwlPicking_DSC
   WHERE   WhseKey = @_iWhseKey
      AND   CompanyID = @_iCompanyID
      AND   TranType = 801
      AND   @_iPickListKey = PickListKey
      AND SrcTranKey IN (SELECT SOKey FROM twlRFPickLock 
                  WHERE   WhseKey = @_iWhseKey
                  AND   RFUserID = @_iRFuserID)
      AND   SrcTranKey IN (SELECT DISTINCT SOL.SOKey 
                     FROM tsoSOLine SOL(NOLOCK) INNER JOIN tsoShipLine SHL(NOLOCK)
                           ON SOL.SOLineKey = SHL.SOLineKey
                     WHERE   SHL.ShipKey IS NULL
                     AND      NOT SHL.InvtTranKey IS NULL)

   

------ OTHER CODE -----


      -- Place Lock or update:
      -- (check for lock again, to eliminate small possibility
      -- of locking during times taken for lookups above)
      IF NOT EXISTS(SELECT 1 FROM twlRFPickLock 
               WHERE   PickListKey = @_oPickListKey
                  AND   SOKey = @_oSOKey
                  AND   WhseKey = @_iWhseKey
                  AND   RFUserID <> @_iRFUserID)
      BEGIN
         UPDATE    twlRFPickLock
         SET   TimeLastActivity = GetDate(),
            TimeLockRelease = DATEADD(Minute, @_iTimeOut, GetDate())
         WHERE   PickListKey = @_oPickListKey
         AND   SOKey = @_oSOKey
         AND   WhseKey = @_iWhseKey
         AND   RFUserID = @_iRFUserID
   
         IF @@ROWCOUNT = 0
         INSERT   twlRFPickLock
            (SOKey,      PickListKey,      WhseKey,
            RFUserID,   TimeLocked,      TimeLastActivity,
            TimeLockRelease)
         VALUES   (@_oSOKey,   @_oPickListKey,      @_iWhseKey,
            @_iRFUserID,   GetDate(),      GetDate(),
            DATEADD(Minute, @_iTimeOut, GetDate()))
      END
      ELSE   -- if locked by another user, start from beginning...
         GOTO TryAgain
   END

[Strannik223]

Полностью в код не въехал, было бы неплохо еще привести определения таблиц которые учавствуют, поэтому покритикую по мелочам

Code: Select all

AND   SrcTranKey IN (SELECT DISTINCT SOL.SOKey 

По моему distinct здесь излишен если не вреден. IN сам решит как ему отсеять данные и стоит ли это вообще делать.

Code: Select all

SELECT 1 FROM twlRFPickLock

Это уже придирки, но если я не ошибаюсь более распространено where exists( select *....
Оптимизатор сам разберет как это эффективней сделать.

[Strannik223]

Ой!!!

Code: Select all

 IF NOT EXISTS(SELECT 1 FROM twlRFPickLock
...
...
      BEGIN
         UPDATE    twlRFPickLock 

Так у вас же действия с блокировочной таблицей не атомарны!!!
Если 2 потока ломанутся одновременно проверять блокировку и получат успешный результат то они оба залочат один и тот же ресурс.

[katit]

Т.е. если завернуть последнюю часть в транзакцию то будет путем?

Так и сделаю. Уже сейчас практически нереально будет залочить.
Вообще мы прикалывались с того менеджера. Они нашу систему испытывают:
Ставит 25 человек со сканерами в колонну и говорит типа "Шагом марш!"
они дружно включают сканеры и поперли

[Strannik223]

У меня немного по другому..
А именно - отдельная таблица хранит данные об упаковщике. Т.е. я и INSERT и UPDATE

Не понимаю смысл WITH (UPDLOCK) в вашем примере?
Или это как-то с транзакцией работает?

Думаю что моей главной проблемой было то что у меня 3 и 4 местами поменяны. Я переставил и думаю что проблема должна уйти

Я бы отдал предпочтение варианту Виктора. Он более DB way что ли.

Смысл следующий. Между проверкой или выборкой первого свободного заказа (select) и update проходит некоторое количество времени, или квантов процессора. За это время планировщик задач операционки может отдать управление другому потоку, который может выполнить те же действия проверки ресурса. Так как ни один поток еще не добрался до update, но оба уже выполнили select они оба решат что ресурс свободен и оба выполнят update. Чем это чревато понятно.

Дело в том что DB обеспечивает атомарность одного sql statement но не обеспечивает по умалчанию что последовательность statements будет атомарной. Транзакции именно для того и применяются что бы обеспечить атомарность последовательности операторов. Далее в целях улучшения производительности применяются различные уровни изоляции транзакций.
Гуглить: transaction isolation level.
Далее. Можно еще повысить производительность если вы четко знаете места потенциальных конфликтов (как в вашем случае). Можно ручками указать какие таблицы лочить и с каким уровнем изоляции. А остльные таблицы будут лочится с уровнем по умолчанием.

Смысл WITH (UPDLOCK) в том что прочитав значение - кандидат вы лочите эту строчку уже тем фактом что вы ее прочитали. И далее вы можете безопасно производить update зная что строка залочена и никто на нее уже не позарится. Эта ситуация является классикой DB и больше вариантов граболей вы найдете опять же в serialization isolation levels.

[Strannik223]

Т.е. если завернуть последнюю часть в транзакцию то будет путем?

Так и сделаю. Уже сейчас практически нереально будет залочить.
Вообще мы прикалывались с того менеджера. Они нашу систему испытывают:
Ставит 25 человек со сканерами в колонну и говорит типа "Шагом марш!"
они дружно включают сканеры и поперли

Это условие необходимое но недостаточное.
Надо либо ставить
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
либо ручками в select-e лочить.

[Dmitry67]

Поэтому для атомарности я и предложил делать один update в режиме set rowcount 1

'Заворачивание' if exists ... update
в транзакцию НИКАК само по себе не гарантирует атомрности
Важно поставить правильные locking Hints у select

[Victor]

Dmitry67
...Я понял что Вы хотите...

Второй вариант чем-то лучше или это просто для внесения ясности?
Там вроде минус в том, что встает задача идентификации записи с которой работаете, например в случае если упаковшику будет выдано больше одного задания и @@spid будет одинаков.

katit
Суть в связке BEGIN/END TRAN + WITH (UPDLOCK) ? Верно?

Да, верно

Просто я здесь не использую транзакцию.

Это вы напрасно