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

Откуда:
Сообщений: 234
Добрый день.

Есть хитрая аудитная структура на стороне заказчика созданная предыдущими разработчиками.
Таблица [history].[Audit], содержащая общую информацию об изменении.
Основнаая таблица [dbo].[ApplicantTenant]. Таблица куда логируются изменения [history].[ApplicantTenantHistory], с прикольным кластерным индексом не в порядке вставки данных в лог таблицу.
И тригерры, которые отлавливают изменения.
Вообще триггер [dbo].[ApplicantTenant_AU] приводит к deadlock-ам. Основная проблема как я понимаю с update statement в нем.
UPDATE I WITH (UPDLOCK) SET IsCurrent=0 FROM history.ApplicantTenantHistory I INNER JOIN @AuditId A ON a.ApplicantTenantId = i.ApplicantTenantId WHERE IsCurrent=1

Если триггер убрать deadlock-и исчезают. Если update закомментировать тоже исчезают.
Ранее deadlock-ов было очень много. Допили UPDLOCK statement. Стало меньше, но все равно появляются.
Было предложение вообще удалить update statement из deadlock, но заказчик не хочет.

Можно как-то еще какими-то опциями сделать так, чтобы код триггера не приводил к deadlock-у.
Пока сам не придумал как это сделать.
К сожалению, сегодня не могу быть долго на связи по семейным причинам. Завтра или сегодня поздно вечером смогу предоставить больше информации. Deadlock-графы и т.д.

Может подскажете (или как всегда покритикуете для пользы), то что написано выше и ниже.

GO
CREATE TABLE [history].[Audit](
	[AuditId] [bigint] IDENTITY(1,1) NOT NULL,
	[AuditDate] [datetime] NOT NULL,
	[AuditTable] [nvarchar](128) NOT NULL,
	[AuditUserId] [uniqueidentifier] NULL,
	[AuditAction] [int] NOT NULL,
	[AuditUserLogin] [nvarchar](128) NOT NULL,
	[HousingAuthorityId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Audit__AuditId] PRIMARY KEY CLUSTERED 
(
	[AuditId] ASC
)
)
GO
CREATE TABLE [dbo].[ApplicantTenant](
	[HousingAuthorityId] [uniqueidentifier] NOT NULL,
	[ApplicantTenantId] [uniqueidentifier] NOT NULL,
	[Version] [timestamp] NOT NULL,
	[SequentialId] [bigint] IDENTITY(1,1) NOT NULL,
	[ClientId] [nvarchar](30) NULL,
	[ContactLanguageId] [uniqueidentifier] NULL,
	[AccountingId] [nvarchar](10) NULL,
 CONSTRAINT [PK_ApplicantTenant_ApplicantTenantId] PRIMARY KEY CLUSTERED 
(
	[ApplicantTenantId] ASC
)
)
GO
CREATE TABLE [history].[ApplicantTenantHistory](
	[HousingAuthorityId] [uniqueidentifier] NULL,
	[ApplicantTenantId] [uniqueidentifier] NOT NULL,
	[Version] [varbinary](8) NULL,
	[SequentialId] [bigint] NULL,
	[ClientId] [nvarchar](30) NULL,
	[ContactLanguageId] [uniqueidentifier] NULL,
	[AccountingId] [nvarchar](10) NULL,
	[AuditId] [bigint] NOT NULL,
	[IsCurrent] [bit] NOT NULL,
 CONSTRAINT [PK_ApplicantTenantHistory_ApplicantTenantId_AuditId] PRIMARY KEY CLUSTERED 
(
	[ApplicantTenantId] ASC,
	[AuditId] 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
CREATE TRIGGER [dbo].[ApplicantTenant_AU]
  ON [dbo].[ApplicantTenant]
  AFTER UPDATE
  AS 
BEGIN 
 
--Test for PK modifications
DECLARE @rowsModified int
DECLARE @rowswithMatchingPK int

SELECT @rowsModified = COUNT(*) FROM inserted i

SELECT @rowswithMatchingPK = COUNT(*) FROM inserted i INNER JOIN deleted a ON a.ApplicantTenantId = i.ApplicantTenantId
IF @rowswithMatchingPK <> @rowsModified --can modify to capture number of bad updates
BEGIN

RAISERROR('Updates to PK values is not supported',16,1)
 
ROLLBACK TRANSACTION 

END 
 
DECLARE @Context_Info varbinary(128)

SELECT @Context_Info = CONTEXT_INFO() --Needs to be set by application for each database connection

DECLARE @AuditId TABLE (AuditId bigint NOT NULL,  ApplicantTenantId uniqueidentifier NOT NULL)

--Create audit entry, use of MERGE statement was necessary to pass in keys from outer query to allow for matching in history record
MERGE history.Audit
USING
(SELECT HA.HousingAuthorityId, AuditTable='ApplicantTenant', AuditUserId=CAST(@Context_Info as varbinary(16)), AuditUserLogin=SUSER_SNAME(), AuditAction=1, ApplicantTenantId FROM inserted HA) AS i ON 1=0
WHEN NOT MATCHED THEN
INSERT (HousingAuthorityId, AuditTable, AuditUserId, AuditUserLogin, AuditAction)
VALUES (i.HousingAuthorityId, i.AuditTable, i.AuditUserId, i.AuditUserLogin, i.AuditAction)
OUTPUT inserted.AuditID, i.ApplicantTenantId INTO @AuditId;     --Save AuditId to populate history

--Mark current rows as no longer current 
UPDATE I WITH (UPDLOCK) SET IsCurrent=0 FROM history.ApplicantTenantHistory I INNER JOIN @AuditId A ON a.ApplicantTenantId = i.ApplicantTenantId WHERE IsCurrent=1

--Create history record, default for IsCurrent = 1 
INSERT INTO history.ApplicantTenantHistory (AuditId, ApplicantTenantId, HousingAuthorityId, Version, SequentialId, ClientId, ContactLanguageId, AccountingId)
SELECT a.AuditId, i.ApplicantTenantId, HousingAuthorityId, Version, SequentialId, ClientId, ContactLanguageId, AccountingId FROM inserted i
 INNER JOIN @AuditId a ON a.ApplicantTenantId = i.ApplicantTenantId

END

GO

CREATE TRIGGER [dbo].[ApplicantTenant_AI]
  ON [dbo].[ApplicantTenant] 
  AFTER INSERT 
  AS 
BEGIN 

DECLARE @Context_Info varbinary(128)

SELECT @Context_Info = CONTEXT_INFO() --Needs to be set by application for each database connection

DECLARE @AuditId TABLE (AuditId bigint NOT NULL,  ApplicantTenantId uniqueidentifier NOT NULL)

--Create audit entry, use of MERGE statement was necessary to pass in keys from outer query to allow for matching in history record
MERGE history.Audit
USING
(SELECT HA.HousingAuthorityId, AuditTable='ApplicantTenant', AuditUserId=CAST(@Context_Info as varbinary(16)), AuditUserLogin=SUSER_SNAME(), AuditAction=0, ApplicantTenantId FROM inserted HA) AS i ON 1=0
WHEN NOT MATCHED THEN
INSERT (HousingAuthorityId, AuditTable, AuditUserId, AuditUserLogin, AuditAction)
VALUES (i.HousingAuthorityId, i.AuditTable, i.AuditUserId, i.AuditUserLogin, i.AuditAction)
OUTPUT inserted.AuditID, i.ApplicantTenantId INTO @AuditId;     --Save AuditId to populate history

--Create history record, default for IsCurrent = 1 
INSERT INTO history.ApplicantTenantHistory (AuditId, ApplicantTenantId, HousingAuthorityId, Version, SequentialId, ClientId, ContactLanguageId, AccountingId)
SELECT a.AuditId, i.ApplicantTenantId, HousingAuthorityId, Version, SequentialId, ClientId, ContactLanguageId, AccountingId FROM inserted i
 INNER JOIN @AuditId a ON a.ApplicantTenantId = i.ApplicantTenantId

END

GO


Сообщение было отредактировано: 15 май 14, 12:51
15 май 14, 11:01    [16019708]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Я пока для себя создал 2 скрипта, с помощью которых смог воспроизвести deadllock похожий на тот, что на продакшене.
Соответсвенно session1 и session2.
У меня воспроизводится. При этом если удалить update statement, то все работает хорошо.

--session 1
set nocount on

while (1=1)
begin

begin tran

DECLARE @generated_keys table([ApplicantTenantId] uniqueidentifier)
INSERT [dbo].[ApplicantTenant]([ContactLanguageId], [ClientId], [AccountingId], [HousingAuthorityId])
OUTPUT inserted.[ApplicantTenantId] INTO @generated_keys
VALUES ('906A7880-022F-4999-91E7-3D9A5B912B93', NULL, NULL, '00000000-0000-0000-0000-000000000001')

waitfor delay '00:00:00.5'

update t
set t.ClientId = 1
FROM @generated_keys AS g
JOIN [dbo].[ApplicantTenant] AS t ON g.[ApplicantTenantId] = t.[ApplicantTenantId]

commit

end

--delete from dbo.ApplicantTenant
--truncate table history.ApplicantTenantHistory

--Session 2

set nocount on

while (1=1)
begin

begin tran

declare @AppT uniqueidentifier
select top 1 @AppT = R.ApplicantTenantId
from
(
select top 2 AT.ApplicantTenantId
from dbo.ApplicantTenant AT with (nolock)
order by AT.ApplicantTenantId desc
) R
order by R.ApplicantTenantId

UPDATE [dbo].[ApplicantTenant]
SET [ClientId] = case when ClientId = 2 then 1 else 1 end
WHERE [ApplicantTenantId] = @AppT

commit

end
15 май 14, 11:05    [16019735]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Слов много, а самого главного: графа дедлока, нету.
15 май 14, 11:06    [16019738]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Заранее спасибо за все оставленные ценные советы.
Завтра утром смогу предоставить все дополнительную информацию если понадобится.
15 май 14, 11:07    [16019744]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
Павел-П
Member

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

Ну вот как пример.

<deadlock>
 <victim-list>
  <victimProcess id="process20830b868" />
 </victim-list>
 <process-list>
  <process id="process20830b868" taskpriority="0" logused="3728" waitresource="KEY: 84:72057594108772352 (8d0f92f474e3)" waittime="900" ownerId="2575776244" transactionname="user_transaction" lasttranstarted="2014-05-07T05:22:22.030" XDES="0xd6197348" lockMode="U" schedulerid="4" kpid="5048" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-05-07T05:22:22.097" lastbatchcompleted="2014-05-07T05:22:22.093" lastattention="1900-01-01T00:00:00.093" clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="2924" loginname="xxx" isolationlevel="read committed (2)" xactid="2575776244" currentdb="84" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
   <executionStack>
    <frame procname="xxx.dbo.ApplicantTenant_AU" line="39" stmtstart="3022" stmtend="3458" sqlhandle="0x03005400302a071db4eb530024a3000000000000000000000000000000000000000000000000000000000000">
UPDATE I WITH (UPDLOCK) SET IsCurrent=0 FROM history.ApplicantTenantHistory I INNER JOIN @AuditId A ON a.ApplicantTenantId = i.ApplicantTenantId WHERE IsCurrent=1

--Create history record, default for IsCurrent = 1    </frame>
    <frame procname="adhoc" line="1" stmtstart="184" stmtend="554" sqlhandle="0x02000000b5cc32062acf2d95bb74e6873d89933eec2662350000000000000000000000000000000000000000">
UPDATE [dbo].[ApplicantTenant]
SET [ContactLanguageId] = @0, [ClientId] = @1, [AccountingId] = NULL, [HousingAuthorityId] = @2
WHERE (([ApplicantTenantId] = @3) AND ([Version] = @4))    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@0 uniqueidentifier,@1 nvarchar(max) ,@2 uniqueidentifier,@3 uniqueidentifier,@4 binary(8))UPDATE [dbo].[ApplicantTenant]
SET [ContactLanguageId] = @0, [ClientId] = @1, [AccountingId] = NULL, [HousingAuthorityId] = @2
WHERE (([ApplicantTenantId] = @3) AND ([Version] = @4))
SELECT [Version]
FROM [dbo].[ApplicantTenant]
WHERE @@ROWCOUNT > 0 AND [ApplicantTenantId] = @3   </inputbuf>
  </process>
  <process id="process16a73e188" taskpriority="0" logused="17504" waitresource="KEY: 84:72057594108772352 (4430744d2f24)" waittime="873" ownerId="2575776245" transactionname="user_transaction" lasttranstarted="2014-05-07T05:22:22.047" XDES="0x2321591b8" lockMode="U" schedulerid="2" kpid="2488" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-05-07T05:22:22.130" lastbatchcompleted="2014-05-07T05:22:22.130" lastattention="1900-01-01T00:00:00.130" clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="2924" loginname="xxx" isolationlevel="read committed (2)" xactid="2575776245" currentdb="84" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
   <executionStack>
    <frame procname="xxx.dbo.ApplicantTenant_AU" line="39" stmtstart="3022" stmtend="3458" sqlhandle="0x03005400302a071db4eb530024a3000000000000000000000000000000000000000000000000000000000000">
UPDATE I WITH (UPDLOCK) SET IsCurrent=0 FROM history.ApplicantTenantHistory I INNER JOIN @AuditId A ON a.ApplicantTenantId = i.ApplicantTenantId WHERE IsCurrent=1

--Create history record, default for IsCurrent = 1    </frame>
    <frame procname="adhoc" line="1" stmtstart="104" stmtend="322" sqlhandle="0x02000000cabf901dee0e41a02ec7c7bd6c4a7aede9fb2b850000000000000000000000000000000000000000">
UPDATE [dbo].[ApplicantTenant]
SET [ClientId] = @0
WHERE (([ApplicantTenantId] = @1) AND ([Version] = @2))    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@0 nvarchar(max) ,@1 uniqueidentifier,@2 binary(8))UPDATE [dbo].[ApplicantTenant]
SET [ClientId] = @0
WHERE (([ApplicantTenantId] = @1) AND ([Version] = @2))
SELECT [Version]
FROM [dbo].[ApplicantTenant]
WHERE @@ROWCOUNT > 0 AND [ApplicantTenantId] = @1   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594108772352" dbid="84" objectname="xxx.history.ApplicantTenantHistory" indexname="1" id="lock138c1de00" mode="X" associatedObjectId="72057594108772352">
   <owner-list>
    <owner id="process16a73e188" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process20830b868" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594108772352" dbid="84" objectname="xxx.history.ApplicantTenantHistory" indexname="1" id="lock57b4b0200" mode="X" associatedObjectId="72057594108772352">
   <owner-list>
    <owner id="process20830b868" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process16a73e188" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>


Сообщение было отредактировано: 15 май 14, 12:51
15 май 14, 11:11    [16019773]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Исправьте в обоих триггерах:
DECLARE @AuditId TABLE (AuditId bigint NOT NULL, ApplicantTenantId uniqueidentifier NOT NULL, primary key (ApplicantTenantId, AuditId))
15 май 14, 12:29    [16020345]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
invm
Исправьте в обоих триггерах:
DECLARE @AuditId TABLE (AuditId bigint NOT NULL, ApplicantTenantId uniqueidentifier NOT NULL, primary key (ApplicantTenantId, AuditId))


Добрый день. А можете объяснить чем это поможет. В @AuditId всегда приходит одна строка.
18 май 14, 00:31    [16031921]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Павел-П
А можете объяснить чем это поможет.
Если там одна строка, то ничем.

Очевидно у вас в той же транзакции, где UPDATE [dbo].[ApplicantTenant] ранее есть INSERT [dbo].[ApplicantTenant] с тем же ApplicantTenantId?
Для history.ApplicantTenantHistory сочетание ApplicantTenantId, IsCurrent уникально?

ЗЫ: Хинт updlock в данном контексте не имеет смысла.
18 май 14, 11:26    [16032503]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
invm
Очевидно у вас в той же транзакции, где UPDATE [dbo].[ApplicantTenant] ранее есть INSERT [dbo].[ApplicantTenant] с тем же ApplicantTenantId?

Да это так.
invm
Для history.ApplicantTenantHistory сочетание ApplicantTenantId, IsCurrent уникально?

ЗЫ: Хинт updlock в данном контексте не имеет смысла.

Уникально только для IsCurrent = 1, т.е. уникальная активная запись будет только одна, неактивных много.
19 май 14, 13:15    [16036148]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
UPDATE I WITH (UPDLOCK) SET IsCurrent=0 FROM history.ApplicantTenantHistory I INNER JOIN @AuditId A ON a.ApplicantTenantId = i.ApplicantTenantId WHERE IsCurrent=1

А здесь случайно не скан ли будет индекса ? Может быть стоит сделать фильтрованный индекс по ApplicantTenantId,IsCurrent где IsCurrent=1
19 май 14, 13:44    [16036392]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
invm
Member

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

1. Создайте индекс
create unique index IXUQ_history.ApplicantTenantHistory__ApplicantTenantId on history.ApplicantTenantHistory (ApplicantTenantId) where IsCurrent = 1;

2.Исправьте проблемный UPDATE
UPDATE i
 SET IsCurrent=0
FROM
 @Audit a join history.ApplicantTenantHistory i with (index = IXUQ_history.ApplicantTenantHistory__ApplicantTenantId) ON i.ApplicantTenantId = a.ApplicantTenantId and i.IsCurrent=1;
19 май 14, 13:59    [16036533]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock. Trigger с Update and Insert.  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
scd2 на триггерах!! Жесть
19 май 14, 14:25    [16036751]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить