Тупик между удалением и обновлением

Question

Тупик между удалением и обновлением

Первый процесс:

<deadlock-list>
 <deadlock victim="process8d9798">
  <process-list>
   <process id="process8d9798" taskpriority="0" logused="0" waitresource="PAGE:
    5:1:190354" waittime="3203" ownerId="53807810" transactionname="DELETE" 
    lasttranstarted="11:29:29.153" XDES="0x3dbb518" lockMode="U" 
    schedulerid="2" kpid="1792" status="suspended" spid="57" sbid="0" ecid="1" 
    priority="0" transcount="0" lastbatchstarted="2012-09-28T11:29:29.120" 
    lastbatchcompleted="11:29:29.120" clientapp=".Net SqlClient Data Provider" 
    hostname="xxx" hostpid="4460" isolationlevel="read uncommitted (1)" 
    xactid="53807810" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" 
    clientoption2="128056">
    <executionStack>
     <frame procname="Chooser2.dbo.DeleteUserSelections" line="15" stmtstart="360"
     stmtend="464" sqlhandle="0x030005008839117bf599a500099800000100000000000000">
   DELETE UserPlanOption
    WHERE UserID = @userId     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>

Второй процесс:

   <process id="processb84988" taskpriority="0" logused="1744" waitresource="PAGE:
    5:1:190487" waittime="3203" ownerId="53807415" transactionname="user_transaction" 
   lasttranstarted="11:29:13.513" XDES="0x2fc4e6e0" lockMode="IU" 
   schedulerid="4" kpid="4628" status="suspended" spid="52" sbid="0" ecid="0" 
   priority="0" transcount="2" lastbatchstarted="11:29:13.513" 
   lastbatchcompleted="11:29:13.513" 
   clientapp=".Net SqlClient Data Provider" hostname="xxx" 
   hostpid="4460" loginname="chooserpd" isolationlevel="read uncommitted (1)" 
   xactid="53807415" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" 
   clientoption2="128056">
    <executionStack>
     <frame procname="Eligibility" line="149" stmtstart="10566" 
   stmtend="11604" sqlhandle="0x03000500171b4f52c1a6e200ada000000100000000000000">
UPDATE  UserPlanOption 
    SET     RateID = r.ID
    FROM    [User] u WITH (NOLOCK)
        LEFT JOIN Rate r ON r.FamilyTierID = u.FamilyTierID 
    WHERE   UserPlanOption.PlanOptionID NOT IN (SELECT ppo.PlanOptionID FROM 
                    @PORACPlanOptions ppo) AND
        u.ID = @userID AND u.ID = UserPlanOption.UserID AND
        r.PlanOptionID = UserPlanOption.PlanOptionID AND
        r.Criterion1 = dbo.GetPlanOptionAreaID_36()
      </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 1380915991]    </inputbuf>
   </process>
  </process-list>

Список ресурсов:

  <resource-list>
   <pagelock fileid="1" pageid="190354" dbid="5" objectname="UserPlanOption" 
   id="lock1e482d80" mode="IX" associatedObjectId="72057594060996608">
    <owner-list>
     <owner id="processb84988" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process8d9798" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="190487" dbid="5" objectname="UserPlanOption" 
   id="lock25b32a80" mode="U" associatedObjectId="72057594060996608">
    <owner-list>
     <owner id="process8d9798" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processb84988" mode="IU" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>

Таблица "UserPlanOption имеет составной PK (UserId и PlanOptionId). Почему удаление вызывает блокировку страницы? Может кто-нибудь помочь мне понять, что происходит? Некоторое время меня смутило то, что я думал, что тупик будет вызван последующим запросы от одного и того же клиента, но это невозможно. Это должны быть разные клиенты, обращающиеся к одной и той же веб-странице.

На самом деле, я думаю, я знаю ответ на первый вопрос - удаление диапазона потребует блокировки страницы. Но как я могу обойти это?

Результаты запроса индекса:

name                type type_desc is_unique data_space_id ignore_dup_key 
------------------- ---- --------- --------- ------------- -------------- 
PK_UserPlanOption_1 1    CLUSTERED 1         1             0              


is_primary_key is_unique_constraint fill_factor is_padded is_disabled 
-------------- -------------------- ----------- --------- ----------- 
1              0                    0           0         0           

is_hypothetical allow_row_locks allow_page_locks
--------------- --------------- ----------------
0               1               1

Планы выполнения обновления и удаления.

0

sql-server-2005 deadlock database-deadlocks

Источник

user58880 28 сен '12 в 20:07

2 ответа

Решение

Я полагаю, что причина тупика заключается в том, что к таблице UserPlanOption обращаются в другом порядке, скорее всего, из-за наличия индекса в столбце FamilyTierId.

Вы можете прочитать больше о подобных проблемах здесь и здесь

Одним из возможных способов избежать этого условия взаимоблокировки является предварительное получение блокировок на основе столбца UserID перед оператором UPdate, как в

SELECT @userId = UserId ИЗ UserPlanOption WITH(UPDLOCK) WHERE UserID = @userId

1

Источник

user1431560 29 сен '12 в 00:08

Другие вопросы по тегам sql-server-2005 deadlock database-deadlocks

user924077 29 сен '12 в 17:33 2012-09-29 17:33 · Accepted Answer · 2012-09-29 17:33

Ответ обновлен

наблюдения

Эта диаграмма основана на графике тупиков XML:

Это показывает, что spid52 имеет блокировку IX для pageid=190354 (таблица UserPlanOption) и запрашивает блокировку IU для pageid=190487 (из той же таблицы UserPlanOption). Я думаю, что таблица UserPlanOption является таблицей кучи, что означает, что она не имеет кластеризованного индекса. Кроме того, это означает, что ваш ПК некластеризован. Если вы запустите этот запрос:

SELECT i.*
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('UserPlanOption')

вы получите список со всеми индексами из таблицы UserPlanOption (вы можете опубликовать этот список?). В этом случае, поскольку spid52 выполняет UPDATE, обе блокировки (IU и IX) показывают (с моей точки зрения) возможный оператор сканирования таблицы / индекса в плане выполнения UPDATE.

Но spid57 уже имеет блокировку U для того же ресурса (pageid=190487). Это же соединение (spid57) запрашивает еще одну блокировку U на другой странице (pageid=190354), но этот ресурс (страница) уже заблокирован spid52 (IX).

Потому что (см. Блокировка матрицы совместимости):

[i] существующий IX и запрошенные U-блокировки или

[ii] существующие U и запрошенные блокировки IU

не совместимы у вас хороший тупик.

Кэшированный план для оператора DELETE

Заметки:

Теперь оператор сканирования кластеризованного индекса (с параллелизмом) заставляет СУБД сканировать все строки из таблицы UserPlanOption,
Расчетное количество строк составляет всего 5 (расчетное количество удаленных строк), а наличие операторов * Parallelism показывает, что таблица UserPlanOption велика,
Вы можете увидеть предложение индекса от SQL Server.

Кэшированный план для оператора UPDATE

Основными проблемами этого плана являются: Сканирование кластеризованного индекса по таблице тарифов, Вычислительный скаляр с неявным преобразованием из [N][VAR]CHAR (?) в INT и фильтр перед JOIN с таблицей User.

Решения

На основании этих наблюдений решения должны быть:

[1] ~~CREATE INDEX IN_UserPlanOption_UserID_PlanOptionID ON UserPlanOption (UserID, PlanOptionID);~~

-- SQL Server's suggestion
CREATE INDEX IN_UserPlanOption_UserID
ON UserPlanOption(UserID)
INCLUDE(PlanOptionID); -- optional

Примечание 1: по моему выбору, проверка кластеризованного индекса по таблице UserPlanOption (DELETE) является основной причиной этой тупиковой ситуации.

Примечание 2: UserPlanOption имеет кластерный индекс на (PlanOptionID, UserID) колонны. Этот индекс помогает оператору UPDATE (см. Оператор Seek в PK_UserPlanOption_1: WHERE ... AND u.ID = UserPlanOption.UserID AND r.PlanOptionID = UserPlanOption.PlanOptionID AND ...) но не оператор DELETE (WHERE UserID=@UserID).

[2] Чтобы повысить производительность оператора UPDATE, вы можете создать индекс sugested:

-- SQL Server's suggestion
CREATE INDEX IN_Rate_FamilyTierID
ON dbo.Rate(FamilyTierID)
INCLUDE (PlanOptionID, Criterion1);

[3] Чтобы удалить неявное преобразование, вы можете переписать оператор DELETE следующим образом:

DECLARE @Criterion1 Criterion1_datatype? 
SET @Criterion1 = dbo.GetPlanOptionAreaID_36()

UPDATE  UserPlanOption 
    SET     RateID = r.ID
    FROM    [User] u 
        LEFT JOIN Rate r ON r.FamilyTierID = u.FamilyTierID 
    WHERE   UserPlanOption.PlanOptionID NOT IN (SELECT ppo.PlanOptionID FROM 
                    @PORACPlanOptions ppo) AND
        u.ID = @userID AND u.ID = UserPlanOption.UserID AND
        r.PlanOptionID = UserPlanOption.PlanOptionID AND
        r.Criterion1 = @Criterion1

Исходный исходный код (инструкция UPDATE) включает этот фильтр r.Criterion = dbo.GetPlanOptionAreaID_36(...), В этот момент, если эта функция вызывается для каждой строки из таблицы показателей, оператор компьютерной скалярности может стать еще одной проблемой производительности.

Является ли эта функция детерминированной функцией?

SELECT  r.IS_DETERMINISTIC, r.*
FROM    INFORMATION_SCHEMA.ROUTINES r
WHERE   r.ROUTINE_NAME='GetPlanOptionAreaID_36'

[4] Мой совет не использовать NOLOCK подсказка и / или SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, Это решение должно быть только последним решением.