Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SQL Server. Foreign key constraint как причина Deadlock  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Добрый день.
Наткнулся на случаи, когда в SQL Server получаются deadlock-и из-за наличия (вернее проверки) foreign key constraint.

1. http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2012/07/06/deadlock-due-to-foreign-key-constraints.aspx
2. http://www.sqlmanager.net/en/articles/1681

И вот что я не могу понять.
Т.е. получается, что любая попытка вставки в одной транзакции данных в основную и вторичную таблицу (где есть FK) потенциально операция, которая приведет к deadlock-у.

И в принципе все советы, которые описаны ниже - это лишь рекоммендации, которые до конца ничего гарантировать не могут.
И если у вас система - вставляет данные в одной транзакции в основную таблицу, а затем достаточно большое число записей в зависимую таблицу со ссылкой только на одну предыдущую вставленную запись (в принципе достаточно частый вариант) и делает, это с достаточно большой нагрузкой (частотой), то можно с большой вероятностью ожидать deadlock.
Хотя в принципе чисто логически его и быть не должно.

--================================================================================
Using foreign keys results in additional work for data manipulation operations in which referential integrity is enforced. The validation of the referential integrity is verified with a join. In cases in which the data being inserted is small enough, SQL Server defaults to the use of a loop join. In cases in which the dataset being inserted is known to be large, SQL Server defaults to the use of another join strategy that necessitates a scan of the referenced table. Scanning can result in deadlocking with concurrent insert operations.

In some cases, transactions may use a key locking strategy when inserting into the referenced table and then use a page locking strategy when verifying referential integrity. This different granularity can also result in deadlocking.

SQL Server always uses shared locks when validating referential integrity. This is true even if the transactions are using read committed snapshot (read committed using row versioning) or snapshot isolation levels.

To eliminate the deadlocking when checking referential integrity, you can disable lock escalation on the referenced table, disable page locking on the referenced table, and hint OPTION (LOOP JOIN) on transactions that operate on mutually exclusive datasets. This forces SQL Server to lock the minimal amount of data and to use a more direct seek operation to access the referenced rows while checking referential integrity.
--================================================================================
23 окт 14, 11:52    [16747392]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Хотя в моем случае option (loop join) помог. Но это же надо сначала результат запроса положить в temp table, а только потом с option (loop join) вставлять.
23 окт 14, 12:23    [16747550]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8723
Вставляйте отдельными транзакциями, какая разница. Если у вас там строгая зависимость пар первичный-вторичный, то вы что-то делаете не так.
23 окт 14, 13:18    [16747996]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Владислав Колосов,

А мне вот нужна одна транзакция, которая должна откатиться, если что-то не так.
Для этого транзакции и существуют.

Если вы читали вторую статью, то можете увидеть, что даже если вы делаете все так - то можете получить deadlock.
23 окт 14, 13:28    [16748100]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Glory
Member

Откуда:
Сообщений: 104751
Павел-П
Т.е. получается, что любая попытка вставки в одной транзакции данных в основную и вторичную таблицу (где есть FK) потенциально операция, которая приведет к deadlock-у.

Вставка даже в одну таблицу с несколькими индексами потенциально ведет к deadlock-у.
23 окт 14, 13:31    [16748135]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Glory
Павел-П
Т.е. получается, что любая попытка вставки в одной транзакции данных в основную и вторичную таблицу (где есть FK) потенциально операция, которая приведет к deadlock-у.

Вставка даже в одну таблицу с несколькими индексами потенциально ведет к deadlock-у.


Ага. В этом я с вами полностью согласен. Это хорошее дополнение моего предыдущего утверждения.
23 окт 14, 13:44    [16748269]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Glory
Павел-П
Т.е. получается, что любая попытка вставки в одной транзакции данных в основную и вторичную таблицу (где есть FK) потенциально операция, которая приведет к deadlock-у.

Вставка даже в одну таблицу с несколькими индексами потенциально ведет к deadlock-у.


Тут есть еще один интересный момент. Когда все пишут о причинах возникновения deadlock-ов, то указывают все что угодно, только не эти прикольные случаи. Нет, чтобы написать, как ни старайтесь deadlock-и можно только минимизироват...
25 окт 14, 00:39    [16757265]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31912
Павел-П
Когда все пишут о причинах возникновения deadlock-ов, то указывают все что угодно, только не эти прикольные случаи. Нет, чтобы написать, как ни старайтесь deadlock-и можно только минимизироват...
Хорошая практика - повторить операцию при возникновении дедлока. Естественно, разработку приложения нужно вести сразу исходя из этого (иначе потом будет вечное "а чо, это нам всё переписывать???")
Принципиально, в реальных системах, в общем случае, дедлоки никак не исключить, что не делай.
25 окт 14, 01:49    [16757470]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Crimean
Member

Откуда:
Сообщений: 13147
alexeyvg
Принципиально, в реальных системах, в общем случае, дедлоки никак не исключить, что не делай.


sad but true. но некоторые извести или минимизировать - возможно
27 окт 14, 00:52    [16761937]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
NickAlex66
Member

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

Известное дело. Есть же вполне определенный набор правил, соблюдая которые, можно извести и минимизировать. Одно уже тут озвучено.
27 окт 14, 00:59    [16761943]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Павел-П
Member

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

Известное дело. Есть же вполне определенный набор правил, соблюдая которые, можно извести и минимизировать. Одно уже тут озвучено.


Вы имеете ввиду не создавать FK :-).
29 окт 14, 17:34    [16774315]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
Павел-П,

Нет, ни это.
Одно из правил озвучил alexeyvg.
Другое, пожалуй я бы поставил его на первое место, состоит в том, что бы делать "обход" таблиц(ы) в транзакциях в одном направлении.
Второе, по значимости, делать транзакции как можно более короткими (в плане вовлекаемых ресурсов), насколько позволяет бизнес логика.
Это общие и давно известные, есть и др, но они под конкретные ситуации.
30 окт 14, 00:48    [16775536]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
МуМу
Member

Откуда:
Сообщений: 1134
В оправдание разработчикам скажу, что архитектору системы нужно это планировать заранее! Как можно интуитивно делать апдейт таблиц Х и Y в определенной последовательности?
30 окт 14, 05:56    [16775826]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Ну а так то, есть инструкции. В текущей транзакции апдейтим строго в определеннном порядке (Таблицы, Порядок индекса)
30 окт 14, 05:59    [16775828]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6192
МуМу
Как можно интуитивно делать апдейт таблиц Х и Y в определенной последовательности?

Дедлоки вполне могут возникать на сочетании select в одной сессии, и update в другой.
30 окт 14, 06:04    [16775830]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Я не хочу спорить на эту тему. Можно от деадлоков избавится как минимум поставив одну блокировку "ОБЩУЮ" и применив уровень параллелизма системы в 1! Идея в чем - блокировки в одном порядке - гораздо меньше вероятность деадлоков.
30 окт 14, 06:13    [16775834]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server. Foreign key constraint как причина Deadlock  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Все общие рекоммендации, я считаю, всем понятны.
Думаю, пока эту тему можно закрывать.
Вообщем придется продолжать бороться с ними - deadlock-ами.

alexeyvg
Принципиально, в реальных системах, в общем случае, дедлоки никак не исключить, что не делай.
30 окт 14, 08:40    [16775980]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить