Решение ситуации взаимоблокировки SQL Server

Я пытаюсь найти решение, которое разрешит повторяющуюся тупиковую ситуацию на сервере SQL. Я провел некоторый анализ графа взаимоблокировок, сгенерированного трассировкой профилировщика, и получил следующую информацию:

Первый процесс (spid 58) выполняет этот запрос:

UPDATE cds.dbo.task_core
    SET nstate = 1 
    WHERE nmboxid = 89 AND ndrawerid = 1 
        AND nobjectid IN (SELECT
                              nobjectid 
                              FROM (SELECT
                                        nobjectid, count(nobjectid) AS counting
                                        FROM cds.dbo.task_core
                                        GROUP BY nobjectid
                                   ) task_groups
                              WHERE task_groups.counting > 1
                         )

Второй процесс (spid 86) выполняет этот запрос:

INSERT INTO task_core (…) VALUES (…)

spid 58 ожидает блокировку общей страницы на CDS.dbo.task_core (spid 86 содержит конфликтующую блокировку intent Exclusive (IX))

spid 86 ожидает блокировки страницы Intent Exclusive (IX) на CDS.dbo.task_core (spid 58 удерживает конфликтующую блокировку Update)


sql-server deadlock
person Michael Hornfeck    schedule 15.03.2010    source источник

Ответы (4)


arrow_upward
2
arrow_downward

Хорошо, что вы разместили заявления и ресурсы. Чтобы полностью понять проблему, планы были бы также полезны. Но я собираюсь сделать (обоснованное) предположение и диагностировать причину взаимоблокировки как большое сканирование, происходящее в подзапросе UPDATE:

SELECT nobjectid 
    FROM (SELECT nobjectid, count(nobjectid) AS counting
          FROM cds.dbo.task_core
           GROUP BY nobjectid
    ) task_groups
    WHERE task_groups.counting > 1

Этот запрос должен просмотреть всю таблицу task_core. Всегда. Вы попадаете в тупик на странице, потому что полное сканирование таблицы оптимизировано для использования блокировки страницы, но вы можете также попасть на уровень строки, если добавите подсказку ROWLOCK.

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

Но намного лучше вообще не сканировать. Во-первых, пересмотрите требования бизнес-логики и вашу модель данных. Каждый раз, когда вы видите обновление, которое должно просмотреть всю таблицу, чтобы принять решение, это очень и очень вонючий код. Если вы действительно обнаружите, что обновление нельзя переписать более разумным способом (я сомневаюсь), вам следует рассмотреть возможность использования индексированного представления. Выражения BIG_COUNT(*) разрешены в индексированных представлениях, и они значительно ускорят выполнение запроса., в дополнение к устранению причины взаимной блокировки.

person Remus Rusanu    schedule 15.03.2010

arrow_upward
1
arrow_downward

Навскидку, я бы предположил, что ваш самый внутренний подзапрос в spid 58 ожидает INSERT (spid 86).

Предполагая, что грязные чтения в подзапросе разрешены, попробуйте добавить «WITH (NOLOCK)».

(
    SELECT nobjectid, count(nobjectid) AS counting
    FROM cds.dbo.task_core WITH (NOLOCK)
    GROUP BY nobjectid
)
person Phil Sandler    schedule 15.03.2010

arrow_upward
1
arrow_downward

Я подозреваю, как и другие здесь, что производительность частей вашего запроса может быть улучшена, чтобы уменьшить вероятность взаимоблокировок. Однако я также подозреваю, что могут быть законные и неизбежные случаи, когда действительно существует «необходимость» тупиковой ситуации. Это во многом зависит от вашего оператора вставки и от того, сколько строк вы добавляете одновременно, если используется составной ключ (например, добавляются ли записи только в конец таблицы из-за ключа идентификации, или они вставляется по таблице из-за составного ключа).

Лучший пример того, почему я думаю, что у вас может возникнуть реальная проблема: что, если вы вставляете запись, которая имеет nmboxid = 89 AND ndrawerid = 1, и возвращает счетчик objectid > 1?

Я не хочу отговаривать вас от поиска правильного решения основной причины; но, с другой стороны, мне интересно, является ли самое простое решение (по крайней мере, в качестве первого шага) правильной обработкой, когда возникает взаимоблокировка.

person Nij    schedule 15.03.2010

arrow_upward
0
arrow_downward

Вам не нужно иметь производную таблицу в подзапросе, ваш исходный запрос может быть:

UPDATE cds.dbo.task_core
    SET nstate = 1 
    WHERE nmboxid = 89 AND ndrawerid = 1 
        AND nobjectid IN (SELECT
                              nobjectid
                              FROM cds.dbo.task_core
                              GROUP BY nobjectid
                              HAVING COUNT(nobjectid)>1
                         )

но это не предотвратит тупик. Можете ли вы добавить WHERE в подзапрос? как:

UPDATE cds.dbo.task_core
    SET nstate = 1 
    WHERE nmboxid = 89 AND ndrawerid = 1 
        AND nobjectid IN (SELECT
                              nobjectid
                              FROM cds.dbo.task_core
                              WHERE nmboxid = 89 AND ndrawerid = 1   --<<<<<<<
                              GROUP BY nobjectid
                              HAVING COUNT(nobjectid)>1
                         )

это может (если можно использовать индекс) предотвратить сканирование таблицы и разрешить процесс INSERT.

person KM.    schedule 15.03.2010