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

Откуда: Белокаменная
Сообщений: 260
по какой причине могут возникать дедлоки в инсертах?
есть процедура вставляющая в таблицу данные
таблица с кластерным индексом, на ней есть внешний ключ на справочник
при запуске этой процедуры в нескольких потоках, возникают дедлоки

в графе дедлока написано что ресурс который был залочен - это кластеный индекс справочника
3 июн 16, 15:34    [19255462]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
fduch f.f.,

укажите (updlock,serializable) если не сильно зхадумываться
3 июн 16, 15:38    [19255481]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
TaPaK
fduch f.f.,

укажите (updlock,serializable) если не сильно зхадумываться
А чо не tabxlock сразу?
3 июн 16, 15:40    [19255489]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
fduch f.f.
Member

Откуда: Белокаменная
Сообщений: 260
я бы хинт конечно указал, только у меня в графе дедлока только команды INSERT
3 июн 16, 15:42    [19255505]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Гавриленко Сергей Алексеевич,

так спокойнее :)
3 июн 16, 15:44    [19255518]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
fduch f.f.
я бы хинт конечно указал, только у меня в графе дедлока только команды INSERT
Сферические дедлоки в вакууме фиксятся только шарообразными командами.

Сообщение было отредактировано: 3 июн 16, 15:57
3 июн 16, 15:57    [19255605]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
fduch f.f.
по какой причине могут возникать дедлоки в инсертах?
По разным.
Показывайте граф дедлока в формате xdl, если хотите эту причину выяснить.
3 июн 16, 16:09    [19255696]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
fduch f.f.
Member

Откуда: Белокаменная
Сообщений: 260
автор

<event name="xml_deadlock_report" package="sqlserver" timestamp="2016-05-30T10:09:27.545Z">
<data name="xml_report">
<value>
<deadlock>
<victim-list>
<victimProcess id="process320525ca5" />
</victim-list>
<process-list>
<process id="process320525ca5" taskpriority="0" logused="2324" waitresource="KEY: 5:72057594046155472 (339d92f697a0)" waittime="2661" ownerId="492555592" transactionname="user_transaction" lasttranstarted="2016-05-30T06:09:24.303" XDES="0x2751f35e0" lockMode="S" schedulerid="2" kpid="12525" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-05-30T06:09:25.050" lastbatchcompleted="2016-05-30T06:09:24.947" lastattention="1900-01-01T00:00:00.947" clientapp=".Net SqlClient Data Provider" hostname="N01" hostpid="14645" loginname="sa" isolationlevel="read committed (2)" xactid="492555592" currentdb="5" lockTimeout="4294967295" clientoption1="671055672" clientoption2="125056">
<executionStack>
<frame procname="InsertProc" line="290" stmtstart="26470" stmtend="25170" sqlhandle="0x030005003f4350053c3e600016a6000001000000000000000000000000000000000000000000000000000000">
insert OrderTest (AccountId,
Status,
TransactionType,
Quantity,
IsCharge)
select @AccountId,
src.Status,
src.TransactionType,
src.Quantity,
src.IsCharge
from @OrderTests src </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 142623551] </inputbuf>
</process>
<process id="process36c050105" taskpriority="0" logused="2455" waitresource="KEY: 5:72057594046155472 (52e6ad32b15d)" waittime="2659" ownerId="492555955" transactionname="user_transaction" lasttranstarted="2016-05-30T06:09:24.327" XDES="0x25530e3b0" lockMode="S" schedulerid="3" kpid="5565" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-05-30T06:09:25.097" lastbatchcompleted="2016-05-30T06:09:24.950" lastattention="1900-01-01T00:00:00.950" clientapp=".Net SqlClient Data Provider" hostname="N01" hostpid="14645" loginname="sa" isolationlevel="read committed (2)" xactid="492555955" currentdb="5" lockTimeout="4294967295" clientoption1="671055672" clientoption2="125056">
<executionStack>
<frame procname="InsertProc" line="290" stmtstart="26470" stmtend="25170" sqlhandle="0x030005003f4350053c3e600016a6000001000000000000000000000000000000000000000000000000000000">
insert OrderTest (AccountId,
Status,
TransactionType,
Quantity,
IsCharge)
select @AccountId,
src.Status,
src.TransactionType,
src.Quantity,
src.IsCharge
from @OrderTests src </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 142623551] </inputbuf>
</process>
<process id="process2750b1055" taskpriority="0" logused="3950" waitresource="KEY: 5:72057594046155472 (339d92f697a0)" waittime="2661" ownerId="492555950" transactionname="user_transaction" lasttranstarted="2016-05-30T06:09:24.340" XDES="0x3465e63b0" lockMode="S" schedulerid="4" kpid="5752" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-05-30T06:09:24.940" lastbatchcompleted="2016-05-30T06:09:24.900" lastattention="1900-01-01T00:00:00.900" clientapp=".Net SqlClient Data Provider" hostname="N01" hostpid="14645" loginname="sa" isolationlevel="read committed (2)" xactid="492555950" currentdb="5" lockTimeout="4294967295" clientoption1="671055672" clientoption2="125056">
<executionStack>
<frame procname="InsertProc" line="290" stmtstart="26470" stmtend="25170" sqlhandle="0x030005003f4350053c3e600016a6000001000000000000000000000000000000000000000000000000000000">
insert OrderTest (AccountId,
Status,
TransactionType,
Quantity,
IsCharge)
select @AccountId,
src.Status,
src.TransactionType,
src.Quantity,
src.IsCharge
from @OrderTests src </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 142623551] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594046155472" dbid="5" objectname="Accounts" indexname="PK_Accounts" id="lock304524050" mode="X" associatedObjectId="72057594046155472">
<owner-list>
<owner id="process36c050105" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process320525ca5" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594046155472" dbid="5" objectname="Accounts" indexname="PK_Accounts" id="lock32e032450" mode="X" associatedObjectId="72057594046155472">
<owner-list>
<owner id="process2750b1055" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process36c050105" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594046155472" dbid="5" objectname="Accounts" indexname="PK_Accounts" id="lock304524050" mode="X" associatedObjectId="72057594046155472">
<owner-list>
<owner id="process320525ca5" mode="S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process2750b1055" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</value>
</data>
</event>
3 июн 16, 16:36    [19255875]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
fduch f.f.,

Вы в той же транзакции, где происходит insert, ранее меняете строку в Accounts, которая используется для проверки FK в другой транзакции.
И наоборот. В результате - дедлок.
Хинтами на таблицу OrderTest не вылечить.
3 июн 16, 18:02    [19256295]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
fduch f.f.
Member

Откуда: Белокаменная
Сообщений: 260
invm
fduch f.f.,

Вы в той же транзакции, где происходит insert, ранее меняете строку в Accounts, которая используется для проверки FK в другой транзакции.
И наоборот. В результате - дедлок.
Хинтами на таблицу OrderTest не вылечить.


Спасибо за ответ. А как же тогда быть?
Может ли помоч запуск в транзакции с уровнем serializable?

блокировать таблицы полностью я не могу
7 июн 16, 11:47    [19266375]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
fduch f.f.
Может ли помоч запуск в транзакции с уровнем serializable?
Нет.
fduch f.f.
А как же тогда быть?
Варианты:
1. Если позволяют бизнес-правила, не изменять Accounts в одной транзакции с проблемным insert.
2. Организовать повтор транзакции, если она стала жертвой дедлока.
3. Сделать PK в Accounts некластерным, предварительно проверив как это повлияет на другие запросы.
4. Сделать для Accounts уникальный индекс по AccountId и пересоздать FK в OrderTest.
7 июн 16, 12:44    [19266638]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
fduch f.f.
Member

Откуда: Белокаменная
Сообщений: 260
invm
4. Сделать для Accounts уникальный индекс по AccountId и пересоздать FK в OrderTest.


этот вариант кажется наиболее подходящим
однако если я пересоздам FK и сделаю уникальный индекс
как сделать так что бы FK для проверки целостности использовал именно этот новый индекс?
17 июн 16, 12:07    [19304442]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
fduch f.f.
как сделать так что бы FK для проверки целостности использовал именно этот новый индекс?
Должно автоматом получиться, т.к. индекс уже, чем PK. Проверить можно посмотрев значение столбца key_index_id в sys.foreign_keys.
Если же вдруг не получилось, то убить PK, создать индекс и FK, заново создать PK.
17 июн 16, 12:42    [19304599]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки при инсертах  [new]
fduch f.f.
Member

Откуда: Белокаменная
Сообщений: 260
invm
fduch f.f.
как сделать так что бы FK для проверки целостности использовал именно этот новый индекс?
Должно автоматом получиться, т.к. индекс уже, чем PK. Проверить можно посмотрев значение столбца key_index_id в sys.foreign_keys.
Если же вдруг не получилось, то убить PK, создать индекс и FK, заново создать PK.


Спасибо
17 июн 16, 13:32    [19304878]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить