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

Откуда: Жатай->Подольск
Сообщений: 137
Имеем табличку 1
+

CREATE TABLE [ref].[CAJ_Docs](
	[id] [uniqueidentifier] NOT NULL,
	[tstamp] [timestamp] NULL,
	[idOwner] [uniqueidentifier] NOT NULL,
	[idDoc] [uniqueidentifier] NULL,
	[OFG_Reg] [numeric](18, 2) NULL,
	[PFG1_Reg] [numeric](18, 2) NULL,
	[PFG2_Reg] [numeric](18, 2) NULL,
 CONSTRAINT [CAJ_Docs_PK_7c6] PRIMARY KEY CLUSTERED 
(
	[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

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Владелец' , @level0type=N'SCHEMA',@level0name=N'ref', @level1type=N'TABLE',@level1name=N'CAJ_Docs', @level2type=N'COLUMN',@level2name=N'idOwner'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Наименование документа' , @level0type=N'SCHEMA',@level0name=N'ref', @level1type=N'TABLE',@level1name=N'CAJ_Docs', @level2type=N'COLUMN',@level2name=N'idDoc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Плановый объем ФС в очередном финансовом году (из документов ПОФС), руб' , @level0type=N'SCHEMA',@level0name=N'ref', @level1type=N'TABLE',@level1name=N'CAJ_Docs', @level2type=N'COLUMN',@level2name=N'OFG_Reg'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Плановый объем ФС в первом году планового периода (из документов ПОФС), руб' , @level0type=N'SCHEMA',@level0name=N'ref', @level1type=N'TABLE',@level1name=N'CAJ_Docs', @level2type=N'COLUMN',@level2name=N'PFG1_Reg'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Плановый объем ФС во втором году планового периода (из документов ПОФС), руб' , @level0type=N'SCHEMA',@level0name=N'ref', @level1type=N'TABLE',@level1name=N'CAJ_Docs', @level2type=N'COLUMN',@level2name=N'PFG2_Reg'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Документы "Обоснование ассигнований" [ТЧ Сводное обоснование ассигнований]' , @level0type=N'SCHEMA',@level0name=N'ref', @level1type=N'TABLE',@level1name=N'CAJ_Docs'
GO

ALTER TABLE [ref].[CAJ_Docs]  WITH CHECK ADD  CONSTRAINT [idDoc_FK_041f5c0a] FOREIGN KEY([idDoc])
REFERENCES [doc].[AllocationJustification] ([id])
GO

ALTER TABLE [ref].[CAJ_Docs] CHECK CONSTRAINT [idDoc_FK_041f5c0a]
GO

ALTER TABLE [ref].[CAJ_Docs]  WITH CHECK ADD  CONSTRAINT [idOwner_FK_fa957f0d] FOREIGN KEY([idOwner])
REFERENCES [doc].[ConsolidatedAllocationJustification] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [ref].[CAJ_Docs] CHECK CONSTRAINT [idOwner_FK_fa957f0d]
GO

ALTER TABLE [ref].[CAJ_Docs] ADD  CONSTRAINT [CAJ_Docs_id_7c6]  DEFAULT (newid()) FOR [id]
GO



триггер на ней
+

CREATE TRIGGER [ref].[CAJ_Docs_InsertToReferenceItems] ON [ref].[CAJ_Docs]
WITH EXECUTE AS CALLER
FOR INSERT
AS
SET NOCOUNT ON
BEGIN

    UPDATE [cmn].[ReferenceItem]
    SET [IsDeleted]=0
    WHERE id in (select id from INSERTED) AND [IsDeleted]=1 AND [idReference]='0b44a646-4b2d-4fb9-86b6-85f51cd8b81d';
    
    if (@@rowcount=0)
    BEGIN
    INSERT  INTO [cmn].[ReferenceItem] ( [id], [idReference] )
            SELECT  [id],
                    '0b44a646-4b2d-4fb9-86b6-85f51cd8b81d'
            FROM    INSERTED ;
END
END
GO



и табличку 2 в которую пишет триггер
+

CREATE TABLE [cmn].[ReferenceItem](
	[id] [uniqueidentifier] NOT NULL,
	[idReference] [uniqueidentifier] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [ReferenceItems_pk] PRIMARY KEY CLUSTERED 
(
	[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

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'идентификатор элемента' , @level0type=N'SCHEMA',@level0name=N'cmn', @level1type=N'TABLE',@level1name=N'ReferenceItem', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Идентификатор справочника' , @level0type=N'SCHEMA',@level0name=N'cmn', @level1type=N'TABLE',@level1name=N'ReferenceItem', @level2type=N'COLUMN',@level2name=N'idReference'
GO

ALTER TABLE [cmn].[ReferenceItem]  WITH CHECK ADD  CONSTRAINT [ReferenceItems_fk] FOREIGN KEY([idReference])
REFERENCES [cmn].[Reference] ([id])
GO

ALTER TABLE [cmn].[ReferenceItem] CHECK CONSTRAINT [ReferenceItems_fk]
GO

ALTER TABLE [cmn].[ReferenceItem] ADD  CONSTRAINT [DF__ReferenceIte__id__40F9A68C]  DEFAULT (newid()) FOR [id]
GO

ALTER TABLE [cmn].[ReferenceItem] ADD  CONSTRAINT [DF_ReferenceItem_isLocked]  DEFAULT ((0)) FOR [IsDeleted]
GO



и дедлок при вставке в табличку 1
+

<deadlock-list>
 <deadlock victim="process46f2988">
  <process-list>
   <process id="process46f2988" taskpriority="0" logused="10328" waitresource="KEY: 8:72057602281701376 (0602428a7257)" waittime="857" ownerId="151597149" transactionguid="0xacca49be68800248841dec07ca69659a" transactionname="user_transaction" lasttranstarted="2012-07-30T11:05:44.147" XDES="0x1327003b0" lockMode="S" schedulerid="2" kpid="10972" status="suspended" spid="235" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-30T11:06:08.070" lastbatchcompleted="2012-07-30T11:06:08.053" clientapp=".Net SqlClient Data Provider" hostname="BISSBOR-WEB" hostpid="5460" loginname="BISSBOR1\Администратор" isolationlevel="read committed (2)" xactid="151597149" currentdb="8" lockTimeout="4294967295" clientoption1="671221856" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x0200000099014e05490d92f3ca93d279de7ca17fe3c29636">
insert bulk ref.CAJ_Docs ([id] UniqueIdentifier, [idOwner] UniqueIdentifier, [idDoc] UniqueIdentifier, [OFG_Reg] Decimal(18,2), [PFG1_Reg] Decimal(18,2), [PFG2_Reg] Decimal(18,2)) with (KEEP_NULLS, CHECK_CONSTRAINTS, FIRE_TRIGGERS)     </frame>
    </executionStack>
    <inputbuf>
insert bulk ref.CAJ_Docs ([id] UniqueIdentifier, [idOwner] UniqueIdentifier, [idDoc] UniqueIdentifier, [OFG_Reg] Decimal(18,2), [PFG1_Reg] Decimal(18,2), [PFG2_Reg] Decimal(18,2)) with (KEEP_NULLS, CHECK_CONSTRAINTS, FIRE_TRIGGERS)    </inputbuf>
   </process>
   <process id="process7844748" taskpriority="0" logused="12996" waitresource="KEY: 8:72057602281701376 (1d0289870c0b)" waittime="516" ownerId="151607657" transactionguid="0xfd7ada7e3c3c794dbe3953242cc0c900" transactionname="user_transaction" lasttranstarted="2012-07-30T11:05:57.403" XDES="0x80aece90" lockMode="S" schedulerid="31" kpid="10960" status="suspended" spid="79" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-07-30T11:06:10.550" lastbatchcompleted="2012-07-30T11:06:10.550" clientapp=".Net SqlClient Data Provider" hostname="BISSBOR-WEB" hostpid="5460" loginname="BISSBOR1\Администратор" isolationlevel="read committed (2)" xactid="151607657" currentdb="8" lockTimeout="4294967295" clientoption1="671221856" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x0200000099014e05490d92f3ca93d279de7ca17fe3c29636">
insert bulk ref.CAJ_Docs ([id] UniqueIdentifier, [idOwner] UniqueIdentifier, [idDoc] UniqueIdentifier, [OFG_Reg] Decimal(18,2), [PFG1_Reg] Decimal(18,2), [PFG2_Reg] Decimal(18,2)) with (KEEP_NULLS, CHECK_CONSTRAINTS, FIRE_TRIGGERS)     </frame>
    </executionStack>
    <inputbuf>
insert bulk ref.CAJ_Docs ([id] UniqueIdentifier, [idOwner] UniqueIdentifier, [idDoc] UniqueIdentifier, [OFG_Reg] Decimal(18,2), [PFG1_Reg] Decimal(18,2), [PFG2_Reg] Decimal(18,2)) with (KEEP_NULLS, CHECK_CONSTRAINTS, FIRE_TRIGGERS)    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057602281701376" dbid="8" objectname="sbor_test.doc.ConsolidatedAllocationJustification" indexname="ConsolidatedAllocationJustification_PK_23c" id="lock47584f900" mode="X" associatedObjectId="72057602281701376">
    <owner-list>
     <owner id="process7844748" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process46f2988" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057602281701376" dbid="8" objectname="sbor_test.doc.ConsolidatedAllocationJustification" indexname="ConsolidatedAllocationJustification_PK_23c" id="lock354185a00" mode="X" associatedObjectId="72057602281701376">
    <owner-list>
     <owner id="process46f2988" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process7844748" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>



Как его можно победить ?
30 июл 12, 14:21    [12934796]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Леша777
Guest
А что за объект sbor_test.doc.ConsolidatedAllocationJustification ?
На [cmn].[ReferenceItem] есть тригеры ?
30 июл 12, 15:25    [12935270]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Леша777
А что за объект sbor_test.doc.ConsolidatedAllocationJustification ?

еще одна табличка, на которую ссылается первая полем idOwner

Леша777
На [cmn].[ReferenceItem] есть тригеры ?

нет триггеров
30 июл 12, 15:39    [12935371]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Леша777
Guest
Расскажите подробнее о bulk insert-ах ?
Какая вообще версия сервера ?

Может дело в каскадности ?

ALTER TABLE [ref].[CAJ_Docs]  WITH CHECK ADD  CONSTRAINT [idOwner_FK_fa957f0d] FOREIGN KEY([idOwner])
REFERENCES [doc].[ConsolidatedAllocationJustification] ([id])
ON DELETE CASCADE


Конфликт ведь на ключе ConsolidatedAllocationJustification, вызванные балками.
30 июл 12, 17:42    [12936290]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Леша777,

bulk insert запускается с ключами CHECK_CONSTRAINTS, FIRE_TRIGGERS, KEEPNULLS (ADO.NET команда SqlBulkCopy)

версия Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

так каскад на удаление, а я вставляю.
30 июл 12, 17:52    [12936348]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Леша777
Guest
Я понимаю, что каскад на удаление.
Я вот только что вставил параллельно из 2 файлов по 6 млн строк с каскадным удаление и у меня все ok на 2008 r2.

Попробуйте может временно отключить foreign key ?
30 июл 12, 17:59    [12936409]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Леша777,

отключать FK не вариант
31 июл 12, 08:22    [12938227]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Леша777
Guest
Дело конечно хозяйское.

Я вам не предлагал в продакшене отключать ключи. А в тестовом окружении попробовать позаливать без него. В любом случае вам придется определить откуда берется X блокировка на ключе Owner.

У меня план запроса такой :
1) вставка данных
2) сортировка по внешнему ключу
3) merge join c owner
4) assert

А у вас ?
Какие вообще объемы вставки ? Сколько записей в таблице Owner ?
Может поиграться с опциями ROWS_PER_BATCH, TABLOCK ?
31 июл 12, 09:30    [12938413]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
в качестве эксперимента
Guest
Matroskin,

в качестве эксперимента удали один любой из двух FK в CAJ_Docs и попробуй еще раз
31 июл 12, 09:30    [12938415]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Леша777,

Весь фокус заключается в том, что в таблицу doc.ConsolidatedAllocationJustification пишется одна запись с уникальным полем id (которое является PK), которое и будет писаться в поле idOwner таблицы ref.CAJ_Docs и откуда берется пересечение - никак в толк не возьму.

Объемы от 1 до нескольких тысяч.
31 июл 12, 10:56    [12938928]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Леша777
Guest
Правильно я понимаю ?
1) открывается транзакция
2) добавляется owner
3) добавляются документы балком
4) закрывается транзакция

Если так, то причина очевидна. 2 потока добавили по owner-у. И в 2ух потоках проверяется fk. Обе транзакции еще не завершены. 1ий поток пытается ждет, чтобы прочитать owner-a из 2ого потока и наоборот.

Нужно разделять создание ownera и документов по транзакциям.
31 июл 12, 11:04    [12939009]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Леша777,

2 потока добавляют разных owner-ов, по идее каждый из них должен наложить X блокировку по ключу (поле id PK)
а потом добавлять записи в зависимую таблицу и при проверку FK запись уже залочена, сканировать внешнюю таблицу по идее не должен.

Я бы понял, если бы у меня во внешней таблице не было индекса по полю id.
31 июл 12, 11:24    [12939170]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Леша777
Guest
сканировать внешнюю таблицу по идее не должен.


Это зависит от алгоритма join.
Если бы использовался loop join, то deadloak-a бы не было.
У меня в плане, как я уже писал, merge . А он требует чтения всей таблицы.
В SqlBulCopy наверное нельзя указать хинт option (loop join). По крайней мере я не знаю как.

Еще раз : вам нужно разделить транзакции - создания owner-a и документов. Потому что это 2 разные сущности. Это не классический master-detail - не одна сущность и поэтому не нужно одной транзакции.
31 июл 12, 11:31    [12939222]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Леша777,

откуда джоины ? их там нет.

Как раз master-detail и у меня необходимость записать объект вместе со всеми связанными данными, либо откатить - иначе никак.
31 июл 12, 11:45    [12939357]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Леша777
Guest
План bulk insert-a профайлером наконец-то отловите и посмотрите как foreign key проверяется.

И если уж считаете, что это должно сохранятся в одной транзакции, то у вас 2 пути :
1) добиться loop join при провереке
2) вставлять owner-ов с TABLOCKX.
31 июл 12, 11:53    [12939412]     Ответить | Цитировать Сообщить модератору
 Re: Как победить дедлок  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Леша777,

спасибо, буду пробовать
31 июл 12, 11:58    [12939472]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить