Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Приветствую.

У меня есть процесс, который запускает через Service Broker два подпроцесса работающих на разных очередях. Т.е. ситуация такова - эти два подпроцесса начинают работать практически одновременно (разница 2-3 сотых секунды).
Оба подпроцесса выполняют одну и ту-же SP с разными параметрами и один подпроцесс очень редко вылетает с криками
The activated proc [dbo].[SB_Reader] running on queue MyDB.dbo.queue_3 output the following:  
'Transaction (Process ID 68) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.'
Поймать граф DeadLock_a при помощи Profiler_а не удалось (DeadLock редко возникает).
Но, на основе косвеных признаков с вероятностью 99.(99)% ясно, что вылет может возникать только в этом месте (отдельных транзакций нет):
-- Успешная запись в лог обоих подпроцессов
if XACT_STATE() = 1
  COMMIT TRAN
else if XACT_STATE() != 0
  ROLLBACK TRAN

WHILE 1=1
begin
  INSERT INTO #Results -- Место вылета
  SELECT *
  FROM dbo.Results
  WHERE (...)

  begin tran
  begin try
--- куча разного кода с записью в лог

   commit tran
  end try
  begin catch
   if XACT_STATE() != 0
    rollback tran

  -- запись в лог одного из подпроцессов - отсутствует 
  end catch
end

Непонятно, а как, собственно DeadLock мог здесь нарисоваться ?
А как бороться, когда:
WITH(NOLOCK) применить нельзя, т.к. требуется только закомиченные данные.
TRY CATCH применить можно, но очень не удобно, да и хочется странного - чтоб DeadLock не возникал

Все операции Insert/Update с таблицей dbo.Results очень короткие по времени и идут в отдельных транзакциях. Да и сама таблица dbo.Results содержит не более 100 записей.
8 июн 09, 10:57    [7275426]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
MsDatabaseru
Member

Откуда: Hobby.MsDatabase.ru
Сообщений: 10937
попробуйте увеличить гранулянтность с удержанием блокировки до окончания транзакции.


FROM dbo.Results with (tablock, holdlock)
8 июн 09, 11:25    [7275556]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
В 2005 и выше сервере ошибку 1205 можно обработать. В BOL даже пример приведен: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ru/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm - раздел Обработка взаимоблокировок.
8 июн 09, 11:36    [7275612]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
MsDatabaseru, tpg

Уффффф.... Переделал ..... пришлось почти каждый UPDATE заворачивать в WHILE 1=1 TRY CATCH ...
Помогло ж-(
Спасибо за сочувствие ...
8 июн 09, 15:44    [7277138]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Glory
Member

Откуда:
Сообщений: 104760
Greenhorn
MsDatabaseru, tpg

Уффффф.... Переделал ..... пришлось почти каждый UPDATE заворачивать в WHILE 1=1 TRY CATCH ...
Помогло ж-(
Спасибо за сочувствие ...

А настроить запись графа мертвой блокировки в лог не судьба ?
8 июн 09, 15:53    [7277200]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Glory
Greenhorn
MsDatabaseru, tpg

Уффффф.... Переделал ..... пришлось почти каждый UPDATE заворачивать в WHILE 1=1 TRY CATCH ...
Помогло ж-(
Спасибо за сочувствие ...

А настроить запись графа мертвой блокировки в лог не судьба ?
Судьба ....
<deadlock-list>
  <deadlock victim="process3c6c718">
    <process-list>
      <process id="process3c27048" taskpriority="0" logused="3216" waitresource="RID: 59:1:495:1" waittime="281" ownerId="22264575" transactionname="user_transaction" lasttranstarted="2009-06-09T13:09:34.247" XDES="0x800388f0" lockMode="U" schedulerid="4" kpid="5688" status="background" spid="69" sbid="0" ecid="0" priority="0" transcount="2">
        <executionStack>
          <frame procname="MyDB.dbo.SB_Reader" line="273" stmtstart="18900" stmtend="19098" sqlhandle="0x03003b0014ba910bb71c0401229c00000100000000000000">
            UPDATE dbo.Results SET ResCode = @Res, ResDescr = @Descr
            WHERE [ID] = @STID     </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.SB_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
      <process id="process3c6c718" taskpriority="0" logused="1208" waitresource="RID: 59:1:495:3" waittime="312" ownerId="22264780" transactionname="user_transaction" lasttranstarted="2009-06-09T13:09:34.280" XDES="0xed602c370" lockMode="U" schedulerid="11" kpid="9012" status="background" spid="65" sbid="0" ecid="0" priority="0" transcount="2">
        <executionStack>
          <frame procname="MyDB.dbo.SB_Reader" line="314" stmtstart="21878" stmtend="22938" sqlhandle="0x03003b0014ba910bb71c0401229c00000100000000000000">
            UPDATE dbo.Results SET 
            [ResData] = case @isOwnParams when 1 then @wspParams
                          else
                            case when cast(@SaveParams as nvarchar(max)) = 
                                      cast(ISNULL(@wspParams, @SaveParams) as nvarchar(max)) then 
                                NULL 
                              else @wspParams 
                            end
                        end
            WHERE [ID] = @STID     
          </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.SB_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
    </process-list>
    <resource-list>
      <ridlock fileid="1" pageid="495" dbid="59" objectname="MyDB.dbo.Results" id="lock487bf00" mode="X" associatedObjectId="72057594053591040">
        <owner-list>
          <owner id="process3c27048" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c6c718" mode="U" requestType="wait" />
        </waiter-list>
      </ridlock>
      <ridlock fileid="1" pageid="495" dbid="59" objectname="MyDB.dbo.Results" id="lock5342d00" mode="X" associatedObjectId="72057594053591040">
        <owner-list>
          <owner id="process3c6c718" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c27048" mode="U" requestType="wait" />
        </waiter-list>
      </ridlock>
    </resource-list>
  </deadlock>
</deadlock-list>

И что дает этот граф ?
Кроме того, что и так было известно:
1) DeadLock вылезает на таблице dbo.Results при Update_ах (других операций в SP нет).
2) Update_ятся разные записи (@STID уникальный глюч и в разных сессиях он разный)
3) Записи сидят на одной и той-же странице, и это довольно частая ситуевина т.к. таблица масенькая (не более 100 записей).

Бороться то как, кроме как использовать Try Catch ?
9 июн 09, 13:46    [7280707]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Glory
Member

Откуда:
Сообщений: 104760
Greenhorn

Бороться то как, кроме как использовать Try Catch ?

Хм. Блокировать всю таблицу как уже предлагали опять не судьба ?
9 июн 09, 13:54    [7280770]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Crimean
Member

Откуда:
Сообщений: 13148
может в лоб:
UPDATE dbo.Results WITH (XLOCK)
?
9 июн 09, 13:57    [7280784]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Greenhorn
3) Записи сидят на одной и той-же странице, и это довольно частая ситуевина т.к. таблица масенькая (не более 100 записей).

Бороться то как, кроме как использовать Try Catch ?
А DDL этой dbo.Results увидеть можно?
9 июн 09, 14:09    [7280878]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Glory
Greenhorn

Бороться то как, кроме как использовать Try Catch ?

Хм. Блокировать всю таблицу как уже предлагали опять не судьба ?

Стало "Иначе" (С) анекдот
<deadlock-list>
  <deadlock victim="process3c3bc18">
    <process-list>
      <process id="process3c3bc18" taskpriority="0" logused="0" waitresource="OBJECT: 59:539148966:7 " waittime="156" ownerId="22574695" transactionname="user_transaction" lasttranstarted="2009-06-09T14:17:05.750" XDES="0xb9cc2370" lockMode="SIX" schedulerid="6" kpid="4392" status="background" spid="64" sbid="0" ecid="0" priority="0" transcount="2">
        <executionStack>
          <frame procname="MyDB.dbo.WRK_Reader" line="363" stmtstart="25312" stmtend="25696" sqlhandle="0x03003b0014ba910ba9afea00239c00000100000000000000">
            UPDATE dbo.Results WITH(TABLOCK,HOLDLOCK) SET [isResProcessed] = [isResProcessed]
            WHERE ([Owner] = @STID) and ([isResProcessed] = 0) and ([ResCode] is not NULL)     
          </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.WRK_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
      <process id="process3c4e478" taskpriority="0" logused="3216" waitresource="OBJECT: 59:539148966:0 " waittime="125" ownerId="22574486" transactionname="user_transaction" lasttranstarted="2009-06-09T14:17:05.710" XDES="0x80029950" lockMode="SIX" schedulerid="8" kpid="9116" status="background" spid="69" sbid="0" ecid="0" priority="0" transcount="2">
        <executionStack>
          <frame procname="MyDB.dbo.WRK_Reader" line="273" stmtstart="19010" stmtend="19254" sqlhandle="0x03003b0014ba910ba9afea00239c00000100000000000000">
            UPDATE dbo.Results WITH(TABLOCK,HOLDLOCK) SET ResCode = @Res, ResDescr = @Descr
            WHERE [ID] = @STID     
          </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.WRK_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
    </process-list>
    <resource-list>
      <objectlock lockPartition="0" objid="539148966" subresource="FULL" dbid="59" objectname="MyDB.dbo.Results" id="lock49c8200" mode="SIX" associatedObjectId="539148966">
        <owner-list>
          <owner id="process3c3bc18" mode="SIX" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c4e478" mode="SIX" requestType="wait" />
        </waiter-list>
      </objectlock>
      <objectlock lockPartition="7" objid="539148966" subresource="FULL" dbid="59" objectname="MyDB.dbo.Results" id="lock507c780" mode="IX" associatedObjectId="539148966">
        <owner-list>
          <owner id="process3c4e478" mode="IX" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c3bc18" mode="SIX" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
  </deadlock>
</deadlock-list>

PS. Время до DeadLock_a увеличилось - нервно курил аж 20 мин.
9 июн 09, 14:34    [7281103]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Crimean
может в лоб:
UPDATE dbo.Results WITH (XLOCK)
?


Что в лоб, что полбу:
<deadlock-list>
  <deadlock victim="process3c6deb8">
    <process-list>
      <process id="process3c44718" taskpriority="0" logused="3216" waitresource="OBJECT: 59:539148966:0 " waittime="4953" ownerId="22691168" transactionname="user_transaction" lasttranstarted="2009-06-09T14:39:24.427" XDES="0x8002d120" lockMode="X" schedulerid="7" kpid="3204" status="background" spid="70" sbid="0" ecid="0" priority="0" transcount="2">
        <executionStack>
          <frame procname="MyDB.dbo.WRK_Reader" line="273" stmtstart="19046" stmtend="19302" sqlhandle="0x03003b0014ba910b1f34f100239c00000100000000000000">
            UPDATE dbo.Results WITH(TABLOCK,HOLDLOCK,XLOCK) SET ResCode = @Res, ResDescr = @Descr
            WHERE [ID] = @STID     
          </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.WRK_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
      <process id="process3c6deb8" taskpriority="0" logused="792" waitresource="OBJECT: 59:539148966:6 " waittime="5000" ownerId="22690278" transactionname="user_transaction" lasttranstarted="2009-06-09T14:39:24.170" XDES="0x6b47a5710" lockMode="X" schedulerid="11" kpid="9720" status="background" spid="66" sbid="0" ecid="0" priority="0" transcount="2">
        <executionStack>
          <frame procname="MyDB.dbo.WRK_Reader" line="180" stmtstart="12176" stmtend="12482" sqlhandle="0x03003b0014ba910b1f34f100239c00000100000000000000">
            UPDATE dbo.Results WITH(TABLOCK,HOLDLOCK,XLOCK) set [StartTime] = ISNULL([StartTime], GetDate()), [EndTime] = NULL
            WHERE [ID] = @STID     </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.WRK_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
    </process-list>
    <resource-list>
      <objectlock lockPartition="0" objid="539148966" subresource="FULL" dbid="59" objectname="MyDB.dbo.Results" id="lock4321b00" mode="X" associatedObjectId="539148966">
        <owner-list>
          <owner id="process3c6deb8" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c44718" mode="X" requestType="wait" />
        </waiter-list>
      </objectlock>
      <objectlock lockPartition="6" objid="539148966" subresource="FULL" dbid="59" objectname="MyDB.dbo.Results" id="lock5640700" mode="IX" associatedObjectId="539148966">
        <owner-list>
          <owner id="process3c44718" mode="IX" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c6deb8" mode="X" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
  </deadlock>
</deadlock-list>

Сейчас попробую дословно следовать инструкции "WITH(XLOCK)"

PS. Свалилось, как ни странно, быстрее ...
9 июн 09, 14:45    [7281187]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
tpg
Greenhorn
3) Записи сидят на одной и той-же странице, и это довольно частая ситуевина т.к. таблица масенькая (не более 100 записей).

Бороться то как, кроме как использовать Try Catch ?
А DDL этой dbo.Results увидеть можно?

Самой таблицы ?
Можно
CREATE TABLE [dbo].[Results](
	[PID] [uniqueidentifier] NOT NULL,
	[ID] [uniqueidentifier] NOT NULL,
	[Owner] [uniqueidentifier] NULL,
	[ResID] [uniqueidentifier] NULL CONSTRAINT [DF_Results_ResID]  DEFAULT (NULL),
	[ResData] [xml] NULL,
	[ResCode] [int] NULL,
	[ResDescr] [nvarchar](512) NULL,
	[isResProcessed] [bit] NOT NULL CONSTRAINT [DF_Results_isResProcessed]  DEFAULT ((0)),
	[Service] [nvarchar](128) NULL,
	[ResMsgType] [nvarchar](512) NULL,
	[ResContract] [nvarchar](512) NULL,
	[StartTime] [datetime] NULL,
	[EndTime] [datetime] NULL
) ON [PRIMARY]
В этой таблице временно храница состояния шагов выполнения "бизнес"-процессов.
Данные - односвязанный список.
Идея в том, чтобы часть задач "бизнес"-процесса, которые можно выполнить параллельно, распихать по разным Reader_ам Service Broker_а а затем собрать и обработать результаты...
9 июн 09, 14:57    [7281262]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Crimean
Member

Откуда:
Сообщений: 13148
тогда надо смотреть ту самую "кучу кода" :)
9 июн 09, 14:59    [7281280]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Glory
Member

Откуда:
Сообщений: 104760
Greenhorn

В этой таблице временно храница состояния шагов выполнения "бизнес"-процессов.
Данные - односвязанный список.

Т.е. индексов у таблицы нет ?
9 июн 09, 15:00    [7281283]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Greenhorn
Crimean
может в лоб:
UPDATE dbo.Results WITH (XLOCK)
?


Что в лоб, что полбу:

Сейчас попробую дословно следовать инструкции "WITH(XLOCK)"

PS. Свалилось, как ни странно, быстрее ...


Те же яйки - вид с боку (с) анекдот.
9 июн 09, 15:01    [7281298]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Crimean
тогда надо смотреть ту самую "кучу кода" :)

Эта куча кода - набор SP в которых нет обращения к dbo.Results даже на select.
9 июн 09, 15:11    [7281391]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Glory
Greenhorn

В этой таблице временно храница состояния шагов выполнения "бизнес"-процессов.
Данные - односвязанный список.

Т.е. индексов у таблицы нет ?

Нет
9 июн 09, 15:16    [7281433]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Вот кусок Reader_а SB в котором все и происходит...
------------------------------------------------------------------------------------------------------------
--                                        Основной цикл выполнения
------------------------------------------------------------------------------------------------------------
WHILE 1=1
BEGIN
  if @isCanWork != 0
  begin 
    TRUNCATE TABLE #Results

    WHILE 1=1
    BEGIN TRY
      INSERT INTO #Results([PID], [ID], [Owner], [ResID], [ResData], [ResCode], [ResDescr], 
            [isResProcessed], [StartTime], [EndTime])
      SELECT [PID], [ID], [Owner], [ResID], [ResData], [ResCode], [ResDescr], 
            1 as [isResProcessed], [StartTime], [EndTime]
      FROM dbo.Results WITH(XLOCK)
      WHERE ([Owner] = @STID) and ([isResProcessed] = 0) and ([ResCode] is not NULL)

      BREAK;
    END TRY
    BEGIN CATCH
      if XACT_STATE() = -1
        ROLLBACK TRAN

      set @Err = ERROR_NUMBER()
      set @Descr = 'INSERT INTO #Results: ' + ERROR_MESSAGE()

      exec dbo.WriteToLog @Err, 3, @Descr, @@PROCID
      if @Err = 1205
        waitfor delay '00:00:01'

    END CATCH

    set @ReqType = (@ReqType & 3) + case @@ROWCOUNT when 0 then 0 else 4 end;

    select @StepInTran = ISNULL( @wspParams.value('(/./@StepInTran)[1]', 'smallint'), 1);
    if (@StepInTran = 1)
      BEGIN TRAN

    BEGIN TRY
      set @Descr = NULL;
      -----------<<<  Run StepItem  >>>-----------
      EXEC @Res = @WSP @GroupName, @wspParams output, @Descr output, @TranStep output, @WRKContext
      --------------------------------------------
      if XACT_STATE() = -1
      begin
        ROLLBACK TRAN
        set @Res = case @Res when 0 then -1 else @Res end;
        set @Descr = ISNULL(@Descr, 'Unknown exception in "' + @WSP + '"')
      end;
      
      UPDATE dbo.Results WITH(XLOCK) SET ResCode = @Res, ResDescr = @Descr
      WHERE [ID] = @STID
    END TRY
    BEGIN CATCH
      if XACT_STATE() != 0 
        ROLLBACK TRAN

      set @Res = ERROR_NUMBER();
      set @Descr = ISNULL(ERROR_MESSAGE(),'exception') + ' in proc:' + ISNULL(ERROR_PROCEDURE(),@WSP) + 
                   ISNULL(N' on line: ' + cast( ERROR_LINE() as nvarchar), ' on batch');
      
      UPDATE dbo.Results WITH(XLOCK) SET ResCode = @Res, ResDescr = @Descr
      WHERE [ID] = @STID

      exec dbo.WriteToLog @Res, 2, @Descr, @@PROCID
    END CATCH

    set @isCanWork = case when @Res in (0,-50) then 1 else 0 end;

    if XACT_STATE() = 1
      COMMIT TRAN
    else if XACT_STATE() != 0
      ROLLBACK TRAN

    BEGIN TRAN

    if @isCanWork != 0
    begin
      if @wspParams is not NULL
      begin
        if @TranStep = 0
        begin -- Удаляем параметры Worker_а
          exec dbo.SetXMLAttribute @wspParams, '/./@TranStep', @wspParams output
          exec dbo.SetXMLAttribute @wspParams, '/./@StepInTran', @wspParams output
        end
        else -- Сохраняем параметры Worker_а
          exec dbo.SetXMLAttribute @wspParams, '/./@TranStep', @wspParams output, @TranStep
      end;

      WHILE 1=1
      BEGIN TRY
        UPDATE dbo.Results WITH(XLOCK) SET 
          [ResData] = case @isOwnParams when 1 then @wspParams
                        else
                          case when cast(@SaveParams as nvarchar(max)) = 
                                    cast(ISNULL(@wspParams, @SaveParams) as nvarchar(max)) then 
                              NULL 
                            else @wspParams 
                          end
                      end
        WHERE [ID] = @STID

        if (@ReqType & 4) != 0
          UPDATE r set [isResProcessed]=w.[isResProcessed]
          FROM dbo.Results as r WITH(XLOCK)
          INNER JOIN #Results as w on w.[ID] = r.[ID]

        BREAK;
      END TRY
      BEGIN CATCH
        if XACT_STATE() = -1
          ROLLBACK TRAN

        set @Err = ERROR_NUMBER()
        set @Descr = 'UPDATE(1) dbo.Results ' + ERROR_MESSAGE()

        exec dbo.WriteToLog @Err, 3, @Descr, @@PROCID
        if @Err = 1205
          waitfor delay '00:00:01'

        if XACT_STATE() = 0
          BEGIN TRAN
      END CATCH
    end

    if XACT_STATE() = 1
      COMMIT TRAN
    else if XACT_STATE() != 0
      ROLLBACK TRAN
  END -- if @isCanWork != 0

  BEGIN TRAN

  set @Err = -1
  if @isCanWork != 0
  begin
    WHILE @Err != 0
    BEGIN TRY
      if (@TranStep = 0)
      begin
        UPDATE dbo.Results WITH(XLOCK) SET [isResProcessed] = [isResProcessed]
        WHERE ([Owner] = @STID) and ([isResProcessed] = 0) and ([ResCode] is not NULL)

        set @Err = case @@ROWCOUNT when 0 then -1 else 0 end; -- -1 => Нет результатов для анализа
      end
      else begin
        set @MaxStepCnt = @MaxStepCnt - 1;
        if @MaxStepCnt <= 0
        begin
          set @Res = -2
          set @Descr = N'"MAX SubStep Count" on ' + @WSP + N'()" Exceeded !!!'

          UPDATE dbo.Results WITH(XLOCK) set [ResCode] = @Res, [ResDescr] = @Descr
          WHERE [ID] = @STID

        end
        else -- CONTINUE
          set @Err = 0
      end;
      
      BREAK;
    END TRY
    BEGIN CATCH
      if XACT_STATE() = -1
        ROLLBACK TRAN

      set @Err = ERROR_NUMBER()
      set @Descr = 'UPDATE(2) dbo.Results ' + ERROR_MESSAGE()

      exec dbo.WriteToLog @Err, 3, @Descr, @@PROCID
      if @Err = 1205
        waitfor delay '00:00:01'

      if XACT_STATE() = 0
        BEGIN TRAN
    END CATCH
  end;

  if (@isCanWork = 0) or (@Err = -1)
    break;

  if XACT_STATE() = 1
    COMMIT TRAN
  else if XACT_STATE() != 0
    ROLLBACK TRAN
END -- WHILE 
9 июн 09, 15:21    [7281469]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Greenhorn
Glory
Greenhorn

В этой таблице временно храница состояния шагов выполнения "бизнес"-процессов.
Данные - односвязанный список.

Т.е. индексов у таблицы нет ?

Нет


ээ.. а вот для этого:

 UPDATE dbo.Results SET 
            [ResData] = case @isOwnParams when 1 then @wspParams
                          else
                            case when cast(@SaveParams as nvarchar(max)) = 
                                      cast(ISNULL(@wspParams, @SaveParams) as nvarchar(max)) then 
                                NULL 
                              else @wspParams 
                            end
                        end
            WHERE [ID] = @STID   

просицо [кластерный] по ID. имхо, ессно
9 июн 09, 16:19    [7281890]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Crimean
Greenhorn
Glory
Greenhorn

В этой таблице временно храница состояния шагов выполнения "бизнес"-процессов.
Данные - односвязанный список.

Т.е. индексов у таблицы нет ?

Нет


просицо [кластерный] по ID. имхо, ессно


Это мона, но от DeadLock_а это не спасет ж-(

PS. Даже WITH(TABLOCKX) не спасает....
9 июн 09, 17:08    [7282215]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
mike909
Member

Откуда:
Сообщений: 662
Greenhorn

PS. Даже WITH(TABLOCKX) не спасает....


Против лома нет приема
Если нет другого лома => sp_getapplock ?
9 июн 09, 17:13    [7282251]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Crimean
Member

Откуда:
Сообщений: 13148


извинюсь сразу, один раз, ничо? это не "наезд", просто констатация фактов

вы не понимаете чего делаете. вам бы про дедлоки почитать. я было хотел предложить добавить индекс по ID и поставить вычитку по ID = @STID в транзакции в самое начало команды
но
потом увидел, что дедлок - не от S-X (конвертация) а от X-U-X (перекрестный доступ), то есть от порядка следования @STID в цикле
с пониманием транзакций у вас туго, иначе бы TABLOCK точно помог. он не может не помочь. да, работала бы в итоге 1 ветка, но - дедлочит, значит не держит блокировку на необходимое время, а значит у вас нет контроля транзакций в смысле понимания этого
короче, в цикле получайте свои @STID не "как попало", а "в порядке возрастания" и все у вас заработает без хинтов :)
9 июн 09, 17:27    [7282356]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Crimean


извинюсь сразу, один раз, ничо? это не "наезд", просто констатация фактов

вы не понимаете чего делаете. вам бы про дедлоки почитать. я было хотел предложить добавить индекс по ID и поставить вычитку по ID = @STID в транзакции в самое начало команды
но
потом увидел, что дедлок - не от S-X (конвертация) а от X-U-X (перекрестный доступ), то есть от порядка следования @STID в цикле
с пониманием транзакций у вас туго, иначе бы TABLOCK точно помог. он не может не помочь. да, работала бы в итоге 1 ветка, но - дедлочит, значит не держит блокировку на необходимое время, а значит у вас нет контроля транзакций в смысле понимания этого
короче, в цикле получайте свои @STID не "как попало", а "в порядке возрастания" и все у вас заработает без хинтов :)


Да, Вы правы - каша в голове....
Где бы /что бы почитать для пояснения ?
А вот sp_getapplock помог .... Но только минут на 30 (рекорд !!!).
<deadlock-list>
  <deadlock victim="process3c59198">
    <process-list>
      <process id="process3c59198" taskpriority="0" logused="136" waitresource="OBJECT: 59:539148966:10 " waittime="2609" ownerId="28250496" transactionname="user_transaction" lasttranstarted="2009-06-09T17:32:18.660" XDES="0x61c169aa0" lockMode="X" schedulerid="9" kpid="10028" status="background" spid="69" sbid="0" ecid="0" priority="0" transcount="2">
        <executionStack>
          <frame procname="MyDB.dbo.WRK_Reader" line="342" stmtstart="23564" stmtend="24654" sqlhandle="0x03003b0014ba910b75eb1801239c00000100000000000000">
            UPDATE dbo.Results WITH(TABLOCKX) SET 
            [ResData] = case @isOwnParams when 1 then @wspParams
                          else
                            case when cast(@SaveParams as nvarchar(max)) = 
                                      cast(ISNULL(@wspParams, @SaveParams) as nvarchar(max)) then 
                                NULL 
                              else @wspParams 
                            end
                        end
            WHERE [ID] = @STID     
          </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.WRK_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
      <process id="process3c6d978" taskpriority="0" logused="3216" waitresource="APPLICATION: 59:0:[Results]:(36129cd0)" waittime="2546" ownerId="28250235" transactionname="user_transaction" lasttranstarted="2009-06-09T17:32:18.613" XDES="0xe0cb4a4c0" lockMode="X" schedulerid="11" kpid="9784" status="background" spid="62" sbid="0" ecid="0" priority="0" transcount="1">
        <executionStack>
          <frame procname="mssqlsystemresource.sys.xp_userlock" line="1" sqlhandle="0x0400ff7f722a782701000000000000000000000000000000">
            xp_userlock     
          </frame>
          <frame procname="mssqlsystemresource.sys.sp_getapplock" line="56" stmtstart="2762" stmtend="2958" sqlhandle="0x0300ff7f459c46090790ea005e9b00000100000000000000">
            exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout     
          </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="289" stmtstart="19742" stmtend="20144" sqlhandle="0x03003b0014ba910b75eb1801239c00000100000000000000">
            EXEC sp_getapplock 
            @Resource = 'Results'
            ,@LockMode = 'Exclusive'
            ,@LockOwner  = 'Transaction'
            ,@LockTimeout = -1     
          </frame>
          <frame procname="MyDB.dbo.WRK_Reader" line="28" stmtstart="1602" stmtend="1700" sqlhandle="0x03003b004dde850cf1b53201159c00000100000000000000">
            exec dbo.WRK_Reader @QueueName, @Service;     
          </frame>
        </executionStack>
        <inputbuf />
      </process>
    </process-list>
    <resource-list>
      <applicationlock hash="Results36129cd0" databasePrincipalId="0" dbid="59" id="lock487e880" mode="X">
        <owner-list>
          <owner id="process3c59198" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c6d978" mode="X" requestType="wait" />
        </waiter-list>
      </applicationlock>
      <objectlock lockPartition="10" objid="539148966" subresource="FULL" dbid="59" objectname="MyDB.dbo.Results" id="lock4e07600" mode="IX" associatedObjectId="539148966">
        <owner-list>
          <owner id="process3c6d978" mode="IX" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c59198" mode="X" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
  </deadlock>
</deadlock-list>

PS. Попробуем с кластерным индексом ....
9 июн 09, 17:48    [7282522]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Crimean
Member

Откуда:
Сообщений: 13148
условный пример

у вас первый процесс открывает транзакцию, работает с записью с ИД = 5, хочет работать с записью с ИД = 7, которую уже держит второй процесс
второй процесс открывает транзакцию, работает с записью ИД 7, хочет работать с записью ИД = 5, занавес, пат
такая ситуация и называется "дедлоком" и не возникает только если у вас сервер идеально-мега-супер-быстрый и процессы напоминают неуловимого джо, который реально никому не нужен

это все вы и можете увидеть в графе. смотрим первый граф, внизу:

<resource-list>
      <ridlock fileid="1" pageid="495" dbid="59" objectname="MyDB.dbo.Results" id="lock487bf00" mode="X" associatedObjectId="72057594053591040">
        <owner-list>
          <owner id="process3c27048" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c6c718" mode="U" requestType="wait" />
        </waiter-list>
      </ridlock>
      <ridlock fileid="1" pageid="495" dbid="59" objectname="MyDB.dbo.Results" id="lock5342d00" mode="X" associatedObjectId="72057594053591040">
        <owner-list>
          <owner id="process3c6c718" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process3c27048" mode="U" requestType="wait" />
        </waiter-list>
      </ridlock>
    </resource-list>

два ресурса, на каждый есть овнер-лист - кто уже держит блокировку и вайт - лист - кто хочет получить блокировку. видно что 2 ресурса + 2 процесса, каждый процесс держит X на свой ресурс и хочет U на чужой. X и U несовместимы, так что детектируем дедлок

что как раз 1:1 соответствует написанному. если вы поставите сортировку при обработке своего @STID то ситуация изменится: оба процесса начнут работать с ИД = 5, кто-то первый поставит X на него, второй тупо будет ждать первого. первый обработает ИД = 7 и отпустит транзакцию. после чего начнет работать второй или к тому времени отвалится по лок - таймауту

почитать - да загугли уже по "дедлок", чего проще!
9 июн 09, 20:05    [7283085]     Ответить | Цитировать Сообщить модератору
 Re: Как защитится от Dead Lock  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Crimean
условный пример

у вас первый процесс открывает транзакцию, работает с записью с ИД = 5,

У меня КАЖДЫЙ процесс получает свой уникальный ИД (@STID) через очередь Srvice Brocker_а (проверено неоднократно - мин нет).
Из кода, приведенного выше, видно, что ИД (@STID) НИКОГДА НЕ МЕНЯЕТСЯ отсюда следует:
1) каждый процесс работает только с одной записью
2) У каждлго процесса своя собственная запись
Crimean
хочет работать с записью с ИД = 7, которую уже держит второй процесс
второй процесс открывает транзакцию, работает с записью ИД 7, хочет работать с записью ИД = 5, занавес, пат
такая ситуация и называется "дедлоком" и не возникает только если у вас сервер идеально-мега-супер-быстрый и процессы напоминают неуловимого джо, который реально никому не нужен

А вот с какого .... этот процесс с ИД = 5 .... вылетающий по DeadLock_у "хочет работать" с ИД = 7 ?????

Спасибо, что разжевали класический пример, хорошо описанный в BOL_е.
Кстати, пример из BOL_а на том-же серваке 2 раза из трех отрабатывает без DeadLock_а
Crimean

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

На скорость не жалуюсь - мастодонт DL580G4 64GB упакованный по полной...
10 июн 09, 05:27    [7283750]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить