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

katit · Post by **katit** » 07 Jun 2004 16:16

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

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

Задача проследить чтобы 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 · Post by **Dmitry67** » 07 Jun 2004 16:23

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

katit · Post by **katit** » 07 Jun 2004 16:37

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

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

Victor · Post by **Victor** » 07 Jun 2004 17:03

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

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

katit · Post by **katit** » 07 Jun 2004 17:06

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

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

Хотя... Во! :umnik1:

просто поставлю красивый GOTO чтобы прыгать к началу :mrgreen:

Victor · Post by **Victor** » 07 Jun 2004 17:11

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

katit · Post by **katit** » 07 Jun 2004 17:19

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

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

Dmitry67 · Post by **Dmitry67** » 07 Jun 2004 17:25

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

Victor · Post by **Victor** » 07 Jun 2004 17:50

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

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

Ситуация действительно стандартна.
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 · Post by **katit** » 07 Jun 2004 18:37

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

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

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

Victor · Post by **Victor** » 07 Jun 2004 19:54

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

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

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

Victor · Post by **Victor** » 07 Jun 2004 20:07

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

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

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

Dmitry67 · Post by **Dmitry67** » 07 Jun 2004 20:22

Victor wrote: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 · Post by **Dmitry67** » 07 Jun 2004 20:25

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

katit · Post by **katit** » 07 Jun 2004 20:35

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

Суть в связке 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