Мертвая блокировка SQL-сервера между ВСТАВКОЙ и оператором SELECT

У меня есть проблема с несколькими мертвыми блокировками на SQL-сервере 2005. Этот между ВСТАВКОЙ и оператором SELECT.

Существует две таблицы. Таблица 1 и Table2. Table2 имеет PK Table1 (table1_id) как внешний ключ.
Индекс на table1_id кластеризируется.

ВСТАВКА вставляет одну строку в table2 за один раз.
SELCET присоединяется к этим 2 таблицам. (это - долгий запрос, который мог бы взять до 12 secs для выполнения),

Согласно моему пониманию (и эксперименты) ВСТАВКА должна получить блокировку IS на table1 для проверки ссылочной целостности (который не должен вызывать мертвую блокировку). Но, в этом случае это получило блокировку на IX страницы

Отчет о мертвой блокировке:

<deadlock-list>
 <deadlock victim="process968898">
  <process-list>
   <process id="process8db1f8" taskpriority="0" logused="2424" waitresource="OBJECT: 5:789577851:0 " waittime="12390" ownerId="61831512" transactionname="user_transaction" lasttranstarted="2010-04-16T07:10:13.347" XDES="0x222a8250" lockMode="IX" schedulerid="1" kpid="3764" status="suspended" spid="52" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:13.350" lastbatchcompleted="2010-04-16T07:10:13.347" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read uncommitted (1)" xactid="61831512" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="DatabaseName.dbo.prcTable2_Insert" line="18" stmtstart="576" stmtend="1148" sqlhandle="0x0300050079e62d06e9307f000b9d00000100000000000000">
INSERT INTO dbo.Table2
    (
        f1,
        table1_id,
        f2
    )
    VALUES
    (
        @p1,
        @p_DocumentVersionID,
        @p1

    )     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 103671417]    </inputbuf>
   </process>
   <process id="process968898" taskpriority="0" logused="0" waitresource="PAGE: 5:1:46510" waittime="7625" ownerId="61831406" transactionname="INSERT" lasttranstarted="2010-04-16T07:10:12.717" XDES="0x418ec00" lockMode="S" schedulerid="2" kpid="1724" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-04-16T07:10:12.713" lastbatchcompleted="2010-04-16T07:10:12.713" clientapp=".Net SqlClient Data Provider" hostname="VIDEV01-B-ME" hostpid="3040" loginname="DatabaseName" isolationlevel="read committed (2)" xactid="61831406" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="DatabaseName.dbo.prcGetList" line="64" stmtstart="3548" stmtend="11570" sqlhandle="0x03000500dbcec17e8d267f000b9d00000100000000000000">
         <!-- XXXXXXXXXXXXXX...SELECT STATEMENT WITH Multiple joins including   both Table2  table 1 and .... XXXXXXXXXXXXXXX -->
    </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 2126630619]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="46510" dbid="5" objectname="DatabaseName.dbo.table1" id="lock6236bc0" mode="IX" associatedObjectId="72057594042908672">
    <owner-list>
     <owner id="process8db1f8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process968898" mode="S" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <objectlock lockPartition="0" objid="789577851" subresource="FULL" dbid="5" objectname="DatabaseName.dbo.Table2" id="lock970a240" mode="S" associatedObjectId="789577851">
    <owner-list>
     <owner id="process968898" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process8db1f8" mode="IX" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Кто-либо может объяснить, почему ВСТАВКА получает блокировку на IX страницы?
Разве я не читаю отчет о мертвой блокировке правильно?
BTW, мне не удалось воспроизвести эту проблему.

Спасибо!

Править: СОЗДАНИЕ ТАБЛИЦ:

CREATE TABLE [dbo].[Table2] (
    [Table2_id] [int] IDENTITY (1, 1) NOT NULL ,
    [f1] [int] NULL ,
    [Table1_id] [int] NOT NULL ,
    [f2] [int] NOT NULL ,
)

ALTER TABLE [dbo].[Table2] ADD 
    CONSTRAINT [FK_Table2_Table1] FOREIGN KEY 
    (
        [Table1_id]
    ) REFERENCES [dbo].[Table1] (
        [Table1_id]
    )


CREATE TABLE [dbo].[Table1] (
    [Table1_id] [int] IDENTITY (1, 1) NOT NULL ,
)

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD 
    CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED 
    (
        [Table1_id]
    ) 
7
задан dtroy 29 April 2010 в 07:21
поделиться

2 ответа

Сразу скажу, что DatabaseName.dbo.prcTable2_Insert выполняется внутри транзакции или явно открывает ее, и это (или соединение с открытая транзакция) уже выполнила вставку в Table1 заранее.

2
ответ дан 7 December 2019 в 05:19
поделиться

I означает блокировку намерения, и они всегда связаны с иерархиями. Поскольку диспетчер блокировок не понимает физическую структуру, он не может соблюдать иерархические блокировки, поэтому иерархия воссоздается в блокировках с намерением.

В вашем случае INSERT имеет блокировку намерения на странице. Это означает, что он также получил блокировку X для строки на странице, что является нормальным поведением. Теперь он пытается получить новую блокировку IX, поэтому, вероятно, ему нужно вставить строку на другую страницу. Это было бы нормальным поведением вставки в таблицу с несколькими индексами: первая IX находится в одном из индексов (возможно, в кластеризованном), а вторая IX находится в некластеризованном индексе.

SELECT, который вы говорите, возвращается через 12 секунд, поэтому это длинный запрос для большого набора данных, и в плане, вероятно, выбрана высокая степень детализации блокировки, блокировки страниц. SELECT имеет блокировку S на странице, которой INSERT требует блокировку IX, и требует еще одну блокировку S на странице, у которой INSERT есть блокировка IX.

Это банальная тупиковая ситуация, и ее очень легко исправить: убедитесь, что ваш SELECT не нуждается в S-блокировках страницы. Здесь нет ошибки INSERT. Не зная, что делает SELECt, я не могу точно сказать, оптимален он или нет. По моему опыту, почти всегда у такого SELECT есть много, много и гораздо больше возможностей для улучшения (например, сам SELECT или схема под ним).

Но, принимая, что SELECT является оптимальным, ваш самый простой способ выйти из тюрьмы - это включить управление версиями строк :

ALTER DATABASE <dbname> SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON;

Обновление:

На самом деле при втором чтении очевидно, что INSERT имеет блокировки для разных таблиц (если вы не изменили XML, который выглядит отредактированным вручную), поэтому ваше объяснение того, как действует вставка , должно быть ошибочным. INSERT является частью транзакции, чем было выполнено по крайней мере две записи, одна в Table1 и одна в Table2. Но это не сильно меняет ни проблему, ни ее решение. Верно, что у вас есть возможность разделить две записи в транзакции на отдельные транзакции, но это, очевидно, худший вариант.

6
ответ дан 7 December 2019 в 05:19
поделиться
Другие вопросы по тегам:

Похожие вопросы: