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

Откуда:
Сообщений: 977
Доброго времени суток.
Отловил на базе (MSSQL 2012) деадлоки, я так понял по ключу (логи прикрепляю):
+

<deadlock-list>
 <deadlock victim="processc416e3c38">
  <process-list>
   <process id="processc416e3c38" taskpriority="0" logused="172" waitresource="RID: 7:1:415431:33" waittime="1701" ownerId="385142496" transactionname="user_transaction" lasttranstarted="2015-11-13T12:53:27.670" XDES="0x918e14d28" lockMode="U" schedulerid="1" kpid="75780" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-11-13T12:53:33.940" lastbatchcompleted="2015-11-13T12:53:33.910" lastattention="2015-11-13T12:13:43.067" clientapp="Sed-Stable" hostname="SEDAPPSERV" hostpid="12216" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="385142496" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="178" stmtend="388" sqlhandle="0x020000005e27ec2a75c46c145a69169d1072d63c20b37bdd0000000000000000000000000000000000000000">
UPDATE [dbo].[IncomingDocument] SET [Status_Id] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1);     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p1_0 uniqueidentifier,@p1_1 uniqueidentifier,@p1_2 datetime2(7),@p0_0 uniqueidentifier)UPDATE [dbo].[IncomingDocument] SET [Status_Id] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1);
UPDATE [dbo].[IncomingDocumentSpec] SET [ChangeDate] = @p1_2 WHERE ([IncomingDocumentSpec].[Id] = @p1_1);
SELECT CAST((CASE WHEN EXISTS (SELECT * FROM [dbo].[RegAttachedF] [a] WHERE ([a].[Entity_Id] = @p0_0)) THEN 1 ELSE 0 END)  AS bit) AS [c01umn];
    </inputbuf>
   </process>
   <process id="processc91433498" taskpriority="0" logused="1068" waitresource="KEY: 7:72057601359675392 (232cb749ccf5)" waittime="1654" ownerId="385142582" transactionname="user_transaction" lasttranstarted="2015-11-13T12:53:30.240" XDES="0xc1ae2d6a8" lockMode="U" schedulerid="28" kpid="544" status="suspended" spid="100" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-11-13T12:53:33.987" lastbatchcompleted="2015-11-13T12:53:33.963" lastattention="1900-01-01T00:00:00.963" clientapp="Sed-Stable" hostname="SEDAPPSERV" hostpid="12216" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="385142582" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="86" sqlhandle="0x02000000be72f207f4b3641a9dff2e5956664838042d443b0000000000000000000000000000000000000000">
UPDATE [dbo].[IncomingDocument] SET [IncomingNumber] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1);     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p1_0 nvarchar(11),@p1_1 uniqueidentifier)UPDATE [dbo].[IncomingDocument] SET [IncomingNumber] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1);
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <ridlock fileid="1" pageid="415431" dbid="7" objectname="dbo.IncomingDocument" id="lock876fe7d00" mode="X" associatedObjectId="72057594089897984">
    <owner-list>
     <owner id="processc91433498" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc416e3c38" mode="U" requestType="wait"/>
    </waiter-list>
   </ridlock>
   <keylock hobtid="72057601359675392" dbid="7" objectname="dbo.IncomingDocument" indexname="PK_IncomingDocument" id="locke3ce06800" mode="U" associatedObjectId="72057601359675392">
    <owner-list>
     <owner id="processc416e3c38" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc91433498" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>



Визуально тоже в виде графа картинку прикрепляю.


Каковы пути решения проблемы, индексы вроде есть на полях. Переписание скриптов тоже проблематично...

К сообщению приложен файл (20151113_3_example.xdl - 7Kb) cкачать
19 ноя 15, 11:07    [18439627]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

Откуда:
Сообщений: 977
Сорри картинку не ту прикрепил:

К сообщению приложен файл. Размер - 85Kb
19 ноя 15, 11:09    [18439645]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
А можно скриптик таблицы [dbo].[IncomingDocument] и её индексов?
19 ноя 15, 11:33    [18439796]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
В транзакции1 выполняется UPDATE [dbo].[IncomingDocument] SET [IncomingNumber] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1;
Причем ранее на RID, соответствующий значению ключа @p1_1, уже была наложена X.

В транзакции2 выполняется UPDATE [dbo].[IncomingDocument] SET [Status_Id] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1); с таким же значением @p1_1, что и в транзакции1.

Итого имеем:
транзакция2 хочет U на KEY и получает ее.
транзакция1 хочет U на KEY и ждет завершения транзакции2.
транзакция2 хочет U на RID и ждет завершения транзакции1.

Результат - дедлок.

Лечение - сделать ПК кластерным.
19 ноя 15, 12:50    [18440372]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

Откуда:
Сообщений: 977
Jovanny
А можно скриптик таблицы [dbo].[IncomingDocument] и её индексов?


Вот:

+

/*Таблица*/
CREATE TABLE [dbo].[IncomingDocument](
	[Id] [uniqueidentifier] NOT NULL,
	[Type_] [int] NOT NULL,
	[OutgoingNumber] [nvarchar](32) NULL,
	[OutgoingDate] [datetime2](7) NULL,
	[IncomingNumber] [nvarchar](32) NULL,
	[IncomingDate] [datetime2](7) NULL,
	[OutNumber] [nvarchar](32) NULL,
	[OutDate] [datetime2](7) NULL,
	[Authority] [uniqueidentifier] NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000') ,
    [Status_Id] [uniqueidentifier] NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
 CONSTRAINT [PK_IncomingDocument] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IncomingDocument]  WITH CHECK ADD  CONSTRAINT [FK_IncomingDocument_Authority_Authority] FOREIGN KEY([Authority]) REFERENCES [dbo].[Authority] ([Id])
GO

/*Индексы*/
ALTER TABLE [dbo].[IncomingDocument] ADD  CONSTRAINT [PK_IncomingDocument] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IncomingDocument_FK_Authority] ON [dbo].[IncomingDocument]
(
	[Authority] ASC
)
INCLUDE ( 	[TypeId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IncomingDocument_IWithStatus_FK_Status] ON [dbo].[IncomingDocument]
(
	[Status_Id] ASC
)
INCLUDE ( 	[TypeId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


19 ноя 15, 12:55    [18440406]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

Откуда:
Сообщений: 977
invm
В транзакции1 выполняется UPDATE [dbo].[IncomingDocument] SET [IncomingNumber] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1;
Причем ранее на RID, соответствующий значению ключа @p1_1, уже была наложена X.

В транзакции2 выполняется UPDATE [dbo].[IncomingDocument] SET [Status_Id] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1); с таким же значением @p1_1, что и в транзакции1.

Итого имеем:
транзакция2 хочет U на KEY и получает ее.
транзакция1 хочет U на KEY и ждет завершения транзакции2.
транзакция2 хочет U на RID и ждет завершения транзакции1.

Результат - дедлок.

Лечение - сделать ПК кластерным.


Спасибо за описание проблемы и возможные пути решения.
Но опасаюсь 2-ух вещей:
1) Разве не упадет производительность (например при вставке), если PK с типом uniqueidentifier сделать кластерным?
2) Придется пересоздавать и переливать данные в таблицу, чтобы изменить тип PK с некластерного на кластерный, разве нет?
19 ноя 15, 12:59    [18440447]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
medoed
1) Разве не упадет производительность (например при вставке), если PK с типом uniqueidentifier сделать кластерным?
Если ключ индекса uniqueidentifier и вставляются не последовательные значения, то не важно кластерный он или нет - все равно будут расщепления страниц.
Производительность может измениться по другой причине - поменяете тип ПК и все планы запросов с участием таблицы поменяются.
medoed
2) Придется пересоздавать и переливать данные в таблицу, чтобы изменить тип PK с некластерного на кластерный, разве нет?
Нет, не придется.

Сделать ПК кластерным было предложено в связи с
medoed
Переписание скриптов тоже проблематично...
19 ноя 15, 13:15    [18440553]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
Владислав Колосов
Member

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

поставьте ключу в дефолт NEWSEQUENTIALID() , проблема будет менее выраженной.
19 ноя 15, 13:18    [18440583]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

Откуда:
Сообщений: 977
invm
medoed
1) Разве не упадет производительность (например при вставке), если PK с типом uniqueidentifier сделать кластерным?
Если ключ индекса uniqueidentifier и вставляются не последовательные значения, то не важно кластерный он или нет - все равно будут расщепления страниц.
Производительность может измениться по другой причине - поменяете тип ПК и все планы запросов с участием таблицы поменяются.
medoed
2) Придется пересоздавать и переливать данные в таблицу, чтобы изменить тип PK с некластерного на кластерный, разве нет?
Нет, не придется.

Сделать ПК кластерным было предложено в связи с
medoed
Переписание скриптов тоже проблематично...


Спасибо! Проблематично (переписать запросы), не значит невозможно, все же интересно, если менять сами скрипты с точки зрения кода, что в них можно поменять?
По времени разнести невозможно, так как многопользовательский режим в одномоментно таких скриптов может выполняться несколько(10 например).

Насчет PK спасибо, поэкспериментирую на тесте :)
19 ноя 15, 13:23    [18440615]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

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

поставьте ключу в дефолт NEWSEQUENTIALID() , проблема будет менее выраженной.

Попробую, к своему стыду не знал про эту опцию, спасибо! ;)
19 ноя 15, 13:24    [18440627]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
medoed
если менять сами скрипты с точки зрения кода, что в них можно поменять?
Найти место, где где накладывается X до выполнения UPDATE [dbo].[IncomingDocument] SET [IncomingNumber] = @p1_0 WHERE ([IncomingDocument].[Id] = @p1_1;
Либо обеспечить чтобы X или U накладывалась и на ключ, а не только на RID.
Либо, если возможно, перенести эти действия после проблемного UPDATE.

ЗЫ: Устранение дедлока в одном месте может привести к его появлению в другом.
Если дедлоки спорадические, возможно проще всего будет отлавливать их на клиенте и инициировать повтор транзакции.
19 ноя 15, 14:07    [18441005]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

Откуда:
Сообщений: 977
invm
Лечение - сделать ПК кластерным.

Нашел опцию чтобы по быстрому, скрипты не писать, как поменять тип PK:
create unique clustered index PK_Test_2 on dbo.Incoming_Test_2(id) WITH DROP_EXISTING;


Удобно
19 ноя 15, 17:40    [18442833]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

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

поставьте ключу в дефолт NEWSEQUENTIALID() , проблема будет менее выраженной.

А как ее налету проставить через
alter table
? Что то я не нашел, из интерфейса как то не дюже (((
19 ноя 15, 17:42    [18442844]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
medoed,
alter table add constraint name default NEWSEQUENTIALID() for column

с другой стороны, Вам же запрещено процедуры править, в них в столбец может вставка явно идти и значение по умолчанию не поможет.
19 ноя 15, 18:30    [18443110]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

Откуда:
Сообщений: 977
Владислав Колосов
medoed,
alter table add constraint name default NEWSEQUENTIALID() for column

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

Спасибо, да вы правы. Там не процедуры, там ORM
20 ноя 15, 10:51    [18445707]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
Владислав Колосов
Member

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

В этом случае для ускорения вставки сделайте fillfactor индекса как 100%- процент добавляемого объема в неделю и раз в неделю перестраивайте индекс в нерабочее время.
20 ноя 15, 11:02    [18445786]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
medoed
Member

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

В этом случае для ускорения вставки сделайте fillfactor индекса как 100%- процент добавляемого объема в неделю и раз в неделю перестраивайте индекс в нерабочее время.

А если вообще перестраивать все индексы каждую ночь принудительно (ночью нагрузка минимальна у нас)?
20 ноя 15, 11:09    [18445849]     Ответить | Цитировать Сообщить модератору
 Re: Key deadlock - help!  [new]
Владислав Колосов
Member

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

пробуйте разные варианты, если это возможно.
20 ноя 15, 11:30    [18446035]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить