Головоломка с взаимоблокировкой: Жертва не владеет никаким ресурсом, используемым для уничтожения для разрешения взаимоблокировки

ProgramBox

Головоломка с взаимоблокировкой: Жертва не владеет никаким ресурсом, используемым для уничтожения для разрешения взаимоблокировки

Post author:admin
Запись опубликована:23 апреля, 2023
Post category:Вопросы по программированию

#sql #sql-server #sql-server-2008 #deadlock #database-deadlocks

#sql #sql-сервер #sql-server-2008 #взаимоблокировка #база данных-взаимоблокировки

Вопрос:

У меня есть странный график взаимоблокировок, где жертва, выбранная MSSQL server 2008, не является частью цикла взаимоблокировки. Эта взаимоблокировка находится между select и insert . Взаимоблокировочный ресурс — это одна таблица, в которой все select хотят waitresource = "KEY: 6:72057594098810880 (ffffffffffff)"

Вопрос1: Означает ли ffffffffffff, что они хотят полную блокировку диапазона для всей таблицы? Или весь диапазон ключей? Или что-то еще?

Мы следуем правилу, по которому в таблице никогда не будет строки с идентификатором первичного ключа = 0. Есть несколько мест, где мы выполняем проверку такого рода

select foo from bar where @someId = 0 OR SomeId = @someId .

Я также узнал о том, что SQL не закорачивает выражение. Так что, если я передам @someId = 0 , это не гарантирует, что другая часть не будет оценена. Таким образом, возможно, что SQL может выполняться во время выполнения SomeId = @someId .

Вопрос2: Поскольку не удалось найти 0 в SomeId, SQL получит блокировку диапазона для всей таблицы (или строки), чтобы никто другой не вставлял идентификатор 0. Верно?

Имея в виду это предположение, я изменил предложение where на это

 (CASE
       WHEN @someId = 0 THEN 1
       WHEN SomeId = @someId THEN 1
       ELSE 0
END = 1)

надеясь, что это приведет к принудительному порядку вычисления. Но я ошибаюсь. Я снова получаю взаимоблокировку.
Я прикрепил график взаимоблокировки ниже. Я переименовал таблицы и задействованные sprocs (политика компании)

Вопрос3: Вы знаете, чего мне здесь не хватает?

введите описание изображения здесь

   <deadlock-list>
 <deadlock victim="process722c508">
  <process-list>
   <process id="process722c508" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="6217" ownerId="24219001" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.033" XDES="0x80073a40" lockMode="RangeS-S" schedulerid="13" kpid="20436" status="suspended" spid="91" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.033" lastbatchcompleted="2011-05-17T03:29:16.033" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24219001" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="fnGetTableResultAByBId" line="44" stmtstart="2246" stmtend="3566" sqlhandle="0x03000600800d7f0bda124000d99e00000000000000000000">
INSERT INTO @ReturnTable
    SELECT Foo, Bar
    FROM TheOneTable 
    WHERE ZId = @zId 
    AND (CASE
            WHEN @yId = 0 THEN 1
            WHEN YId = @yId THEN 1
            ELSE 0
        END = 1)
    AND (CASE
            WHEN @xId = 0 THEN 1
            WHEN XId = @xId THEN 1
            ELSE 0
        END = 1)     </frame>
     <frame procname="GetViewCByDId" line="9" stmtstart="272" stmtend="2984" sqlhandle="0x03000600c21629025d8f3f00d99e00000100000000000000">
    </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 36247234]    </inputbuf>
   </process>
   <process id="process7185048" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="6217" ownerId="24218992" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.030" XDES="0x179980430" lockMode="RangeS-S" schedulerid="13" kpid="30616" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.030" lastbatchcompleted="2011-05-17T03:29:16.030" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24218992" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="fnGetTableResultAByBId" line="44" stmtstart="2246" stmtend="3566" sqlhandle="0x03000600800d7f0bda124000d99e00000000000000000000">
INSERT INTO @ReturnTable
    SELECT Foo, Bar
    FROM TheOneTable 
    WHERE ZId = @zId 
    AND (CASE
            WHEN @yId = 0 THEN 1
            WHEN YId = @yId THEN 1
            ELSE 0
        END = 1)
    AND (CASE
            WHEN @xId = 0 THEN 1
            WHEN XId = @xId THEN 1
            ELSE 0
        END = 1)     </frame>
     <frame procname="GetViewCByDId" line="9" stmtstart="272" stmtend="2984" sqlhandle="0x03000600c21629025d8f3f00d99e00000100000000000000">
</frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 36247234]    </inputbuf>
   </process>
   <process id="process7223048" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="5330" ownerId="24235090" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.927" XDES="0x840d3b30" lockMode="RangeS-S" schedulerid="15" kpid="23452" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.927" lastbatchcompleted="2011-05-17T03:29:16.927" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24235090" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="GetOneRowEByFId" line="11" stmtstart="260" stmtend="2456" sqlhandle="0x03000600db082c08ba823f00d99e00000100000000000000">
         SELECT TOP 1
         Col1, Col2, Col3

         FROM The2ndTable
         INNER JOIN [dbo].[TheOneTable] ON [dbo].[TheOneTable].[LinkBetweenOneAndTwoId]=[The2ndTable].[LinkBetweenOneAndTwoId]
         WHERE [dbo].[TheOneTable].ZId= @ActivityId and
         [TheOneTable].[n
     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 137103579]    </inputbuf>
   </process>
   <process id="process6334088" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="5668" ownerId="24229434" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.587" XDES="0x17ea9ac90" lockMode="RangeS-S" schedulerid="12" kpid="5104" status="suspended" spid="86" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.587" lastbatchcompleted="2011-05-17T03:29:16.587" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24229434" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="GetOneRowEByFId" line="11" stmtstart="260" stmtend="2456" sqlhandle="0x03000600db082c08ba823f00d99e00000100000000000000">
SELECT TOP 1 
    Col1, Col2, Col3

    FROM The2ndTable
    INNER JOIN [dbo].[TheOneTable] ON [dbo].[TheOneTable].[LinkBetweenOneAndTwoId]=[The2ndTable].[LinkBetweenOneAndTwoId]
    WHERE [dbo].[TheOneTable].ZId= @ActivityId and
        [TheOneTable].[n</frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 137103579]    </inputbuf>
   </process>
   <process id="process8808e08" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="6652" ownerId="24217112" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:15.610" XDES="0x833b5ca0" lockMode="RangeS-S" schedulerid="1" kpid="19752" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:15.610" lastbatchcompleted="2011-05-17T03:29:15.610" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24217112" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="GetOneRowEByFId" line="11" stmtstart="260" stmtend="2456" sqlhandle="0x03000600db082c08ba823f00d99e00000100000000000000">
         SELECT TOP 1
         Col1, Col2, Col3

         FROM The2ndTable
         INNER JOIN [dbo].[TheOneTable] ON [dbo].[TheOneTable].[LinkBetweenOneAndTwoId]=[The2ndTable].[LinkBetweenOneAndTwoId]
         WHERE [dbo].[TheOneTable].ZId= @ActivityId and
         [TheOneTable].[n
     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 137103579]    </inputbuf>
   </process>
   <process id="process5c08988" taskpriority="0" logused="1644" waitresource="KEY: 6:72057594098810880 (91a0638558d2)" waittime="4889" ownerId="24214248" transactionname="user_transaction" lasttranstarted="2011-05-17T03:29:15.327" XDES="0x186609470" lockMode="RangeI-N" schedulerid="9" kpid="9000" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-05-17T03:29:15.330" lastbatchcompleted="2011-05-17T03:29:15.330" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24214248" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="InsertIntoTheOneTable" line="25" stmtstart="1334" stmtend="2608" sqlhandle="0x03000600bbbacb5d25883f00d99e00000100000000000000">
INSERT INTO [dbo].[TheOneTable] (Some,Col,Here)
    VALUES (@some,@col,@here)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 1573632699]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process722c508" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process7185048" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process7223048" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process6334088" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list>
     <owner id="process5c08988" mode="RangeI-N" />
    </owner-list>
    <waiter-list>
     <waiter id="process8808e08" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6372e80" mode="RangeS-S" associatedObjectId="72057594098810880">
    <owner-list>
     <owner id="process7223048" mode="RangeS-S" />
     <owner id="process6334088" mode="RangeS-S" />
    </owner-list>
    <waiter-list>
     <waiter id="process5c08988" mode="RangeI-N" requestType="wait" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

1. Вам действительно нужно, чтобы все это было на сериализуемом уровне изоляции?

2. Помимо Insert, rest может иметь уровень изоляции с фиксацией чтения. Позвольте мне посмотреть, изменит ли это что-нибудь (это займет некоторое время)

3. Но я скептически отношусь к решению этой проблемы. Причина в том, что это работает для transactionname="user_transaction" и не transactionname="SELECT" (я не уверен на 100% в этом утверждении)

4. Я не рассматривал график взаимоблокировок в деталях, но все задействованные ресурсы, похоже, являются блокировками диапазона, и вы получаете их только из-за уровня изоляции.

Ответ №1:

В контексте блокировки таблицы и связанные с ними индексы являются отдельными объектами. Иногда происходит мертвая блокировка между таблицей и ее индексом, а не между двумя отдельными таблицами.

Проблема, скорее всего, возникает, когда для индекса устанавливается блокировка, а затем для связанной таблицы (т. Е. bar) для выполнения поиска данных устанавливается другая блокировка. Во время вставки это произойдет в обратном порядке. Сначала таблица (т. е. панель) блокируется и обновляется, затем блокируются индексы.

 select foo 
from bar 
where @someId = 0 OR SomeId = @someId

Есть ли у вас / можете ли вы добавить покрывающий индекс (чтобы помочь с выбором), который содержит как SomeId, так и foo ? Таким образом, вы полностью избежите поиска и предотвратите возникновение проблемы.

Можете ли вы опубликовать планы запросов, а не фреймы взаимоблокировки?

1. Выяснил причину. Запрос выполнял проверку индекса таблицы на наличие данных. Я добавил идентификаторы в запросе к некластеризованному индексу, и план изменился на поиск по некластеризованному индексу и поиск ключа по кластеризованному индексу, и это было исправлено. Спасибо, что предложили охватить индекс.

2. Хорошее знание движка и хороший совет.

Ответ №2:

Не могли бы вы попробовать какие-нибудь альтернативные критерии? В последнее время я играю с этим методом (только я использую NULLs, а не 0, для обозначения всех значений):

 SET @yId = NullIf(@yId, 0);
SET @xId = NullIf(@xId, 0);

...
WHERE
   @yId BETWEEN Coalesce(@yId, 0) AND Coalesce(@yId, 2147483647)
   AND @xId BETWEEN Coalesce(@xId, 0) AND Coalesce(@xId, 2147483647)

Или вы могли бы использовать свои нули нетронутыми:

 WHERE
   @yId BETWEEN @yId AND Coalesce(NullIf(@yId, 0), 2147483647)
   AND @xId BETWEEN @xId AND Coalesce(NullIf(@xId, 0), 2147483647)

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

Итак, есть ли какой-либо способ, которым вы можете либо получить большую блокировку раньше, избежать получения большей конфликтующей блокировки, либо изменить порядок получения ресурсов?

Вы могли бы поэкспериментировать с использованием WITH (TABLOCKX) , что звучит ужасно, но если ваш @yId или @xId равен 0, что заставляет вас выбирать все строки, вам все равно понадобится вся таблица.

Вы также рассматривали возможность попробовать OPTION (MAXDOP 1) просто посмотреть, поможет ли это? Теоретически, наличие нескольких потоков для одного и того же запроса данных может увеличить вероятность одновременного получения конфликтующих блокировок.

Есть ли в таблице кластеризованный индекс? Если нет, добавьте его, и если да, используется ли он или вы можете принудительно его использовать? Это потенциально может заставить запросы обращаться к таблице другим способом, предотвращая взаимоблокировку.

Оставьте свои комментарии, и я посмотрю, появятся ли еще какие-либо идеи на основе ваших ответов.