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

Откуда:
Сообщений: 87
У меня есть база на SQL Server 2012.
На ней работает несколько разных процессов, к ней обращающихся.
И вот у меня начали регулярно происходить ошибки “Transaction (Process ID xx) was deadlocked on lock resources with another process”.
Вот как они случаются.
Сам deadlock chart см в приложенном файле DeadLock.zip\DeadLock.png.

Process который victim:
Процедура под названием [IP.InsertIPCAEDetail].

Её полный код см в приложенном файле DeadLock.zip\[IP.InsertIPCAEDetail].sql.

Её участок вызвавший deadlock:

INSERT INTO [dbo].[IPCAEDetail]
	   ([IPCode], [TypeNo], [CAENumber], [IPNameNumber], [LastModifiedDate],
		[NameIP], [FirstName], [CAEName], [Status],
		[DummyCAENumber]) --SRX08649
	VALUES
	   (@IPCode, @TypeNo, @CAENumber, @IPNameNumber, GETDATE(),
	   @NameIP, @FirstName, @CAEName, 1, @DummyCAENumber) 


Process который не-victim:
Процедура под названием [SRD.MergeWork].

Её полный код см в приложенном файле DeadLock.zip\[SRD.MergeWork].sql.

Её участок вызвавший deadlock:

INSERT INTO [Work.WorkContributor]
		(
			WorkCode
			,SetTypeNo
			,TypeNo
			,PerformingShare
			,MechanicalShare
			,LastModifiedDate
			,Status
			,USALicence
			,IPCAEDetailID
			,IntrayPK
			,SourceTypeNo
			,WriterPublisherShare
			,TerritoryCode
			,RegistrationDate
			,SetTypeGroupID
		)
		output inserted.IntrayPK, inserted.WorkContributorID
			into @TempContributor (IntrayID, WorkID)
		SELECT 			
			@WorkCode as WorkCode
			,wc.[SetTypeNo] as SetTypeNo
			,wct.[TypeNo]  as TypeNo
			,wc.[PerformingShare]
			,wc.[MechanicalShare]
			,wc.[LastModifiedDate]
			,wc.[Status]
			,wc.[USALicence]
			,wc.IPCAEDetailID as IPCAEDetailID
			,WorkContributorID as IntrayPK		
			,wc.SourceTypeNo
			,wc.WriterPublisherShare
			,wc.TerritoryCode
			,wc.RegistrationDate
			,wc.SetTypeGroupID
		FROM @WorkContributorsToProceed wc
		inner join WorkContributorTypes wct on
		wc.TypeCode = wct.TypeCode
		order by wc.WorkContributorID 



Объекты данных вовлечённые в deadlock:

Таблица [Work.WorkContributor]:


CREATE TABLE [dbo].[Work.WorkContributor](
	[WorkContributorID] [int] IDENTITY(1,1) NOT NULL,
	[WorkCode] [int] NOT NULL,
	[SetTypeNo] [int] NOT NULL,
	[TypeNo] [int] NOT NULL,
	[PerformingShare] [decimal](9, 4) NOT NULL,
	[MechanicalShare] [decimal](9, 4) NOT NULL,
	[LastModifiedDate] [datetime] NOT NULL,
	[Status] [int] NOT NULL,
	[USALicence] [int] NULL,
	[IPCAEDetailID] [int] NOT NULL,
	[IntrayPK] [int] NULL,
	[Concurrency] [timestamp] NOT NULL,
	[SourceTypeNo] [int] NULL,
	[WriterPublisherShare] [decimal](9, 4) NULL,
	[SenderRep] [int] NULL,
	[SetTypeGroupID] [int] NOT NULL,
	[TerritoryCode] [int] NULL,
	[RegistrationDate] [datetime] NULL,
 CONSTRAINT [Work.PK_WorkContributor] PRIMARY KEY CLUSTERED 
(
	[WorkContributorID] 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].[Work.WorkContributor] ADD  DEFAULT ((1)) FOR [SetTypeGroupID]
GO

ALTER TABLE [dbo].[Work.WorkContributor]  WITH NOCHECK ADD  CONSTRAINT [FK_Work.WorkContributor_IPCAEDetail] FOREIGN KEY([IPCAEDetailID])
REFERENCES [dbo].[IPCAEDetail] ([IPCAEDetailID])
GO

ALTER TABLE [dbo].[Work.WorkContributor] NOCHECK CONSTRAINT [FK_Work.WorkContributor_IPCAEDetail]
GO

ALTER TABLE [dbo].[Work.WorkContributor]  WITH CHECK ADD  CONSTRAINT [Work.Work_Work.WorkContributor_FK1] FOREIGN KEY([WorkCode])
REFERENCES [dbo].[Work.Work] ([WorkCode])
GO

ALTER TABLE [dbo].[Work.WorkContributor] CHECK CONSTRAINT [Work.Work_Work.WorkContributor_FK1]
GO



Её индекс (упомянутый в DeadLock) [idxIPCAEDetailID_Index]:


CREATE NONCLUSTERED INDEX [idxIPCAEDetailID_Index] ON [dbo].[Work.WorkContributor]
(
	[IPCAEDetailID] ASC
)
INCLUDE ( 	[WorkCode]) 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 [IndexesStorage]


View [WS.IntrayCodeToIPCode]:

CREATE VIEW [dbo].[WS.IntrayCodeToIPCode]
WITH SCHEMABINDING
AS
SELECT wc.WorkCode, ipcd.IPCode, COUNT_BIG(*) AS TotalNumber
FROM dbo.[Intray.WorkContributor] wc
INNER JOIN dbo.IPCAEDetail ipcd
    ON ipcd.IPCAEDetailID = wc.IPCAEDetailID
    AND ipcd.Status <> 0
WHERE wc.Status<>0
GROUP BY wc.WorkCode, ipcd.IPCode


Таблица [Intray.WorkContributor], на которой основано это View:

CREATE TABLE [dbo].[Intray.WorkContributor](
	[WorkContributorID] [int] IDENTITY(1,1) NOT NULL,
	[WorkCode] [int] NOT NULL,
	[SetTypeNo] [int] NOT NULL,
	[TypeCode] [varchar](10) NOT NULL,
	[PerformingShare] [decimal](9, 2) NOT NULL,
	[MechanicalShare] [decimal](9, 2) NOT NULL,
	[Status] [int] NOT NULL,
	[USALicence] [int] NULL,
	[FirstName] [nvarchar](135) NULL,
	[NameIP] [nvarchar](135) NULL,
	[CAEName] [nvarchar](170) NOT NULL,
	[LastModifiedDate] [datetime] NOT NULL,
	[CAENumber] [varchar](9) NULL,
	[OrigIPCode] [int] NULL,
	[Concurrency] [timestamp] NOT NULL,
	[IPCAEDetailID] [int] NULL,
	[OnlineContributorInfo] [xml] NULL,
	[SourceTypeNo] [int] NULL,
	[WriterPublisherShare] [decimal](9, 4) NULL,
	[InterestedPartyNo] [varchar](9) NULL,
	[SenderRep] [int] NULL,
	[TerritoryCode] [int] NULL,
	[RegistrationDate] [datetime] NULL,
	[SetTypeGroupID] [int] NOT NULL,
 CONSTRAINT [Intray.PK_WorkContributor] PRIMARY KEY CLUSTERED 
(
	[WorkContributorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Intray.WorkContributor] ADD  DEFAULT ((1)) FOR [SetTypeGroupID]
GO

ALTER TABLE [dbo].[Intray.WorkContributor]  WITH NOCHECK ADD  CONSTRAINT [FK_Intray.WorkContributor_IPCAEDetail] FOREIGN KEY([IPCAEDetailID])
REFERENCES [dbo].[IPCAEDetail] ([IPCAEDetailID])
GO

ALTER TABLE [dbo].[Intray.WorkContributor] NOCHECK CONSTRAINT [FK_Intray.WorkContributor_IPCAEDetail]
GO

ALTER TABLE [dbo].[Intray.WorkContributor]  WITH CHECK ADD  CONSTRAINT [Intray.Work_WorkContributor_FK] FOREIGN KEY([WorkCode])
REFERENCES [dbo].[Intray.Work] ([WorkCode])
GO

ALTER TABLE [dbo].[Intray.WorkContributor] CHECK CONSTRAINT [Intray.Work_WorkContributor_FK]
GO


К сообщению приложен файл (DeadLock.zip - 83Kb) cкачать
6 июн 18, 18:07    [21473655]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
KellyLynch,

IntrayCodeToIPCode на ней поди и кластерный есть?
6 июн 18, 18:16    [21473687]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
KellyLynch,

От вашей картинки толку никакого. Показывайте граф в xml формате.
6 июн 18, 18:17    [21473689]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
KellyLynch
Member

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

От вашей картинки толку никакого. Показывайте граф в xml формате.


Вот он:

xml_deadlock_report (2018-06-05 15_32_14.2089385) - xml_report10.xml

К сообщению приложен файл (xml_deadlock_report (2018-06-05 15_32_14.2089385) - xml_report10.xml - 4Kb) cкачать
6 июн 18, 18:45    [21473763]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
KellyLynch
Member

Откуда:
Сообщений: 87
TaPaK
KellyLynch,

IntrayCodeToIPCode на ней поди и кластерный есть?


Да - view [WS.IntrayCodeToIPCode] имеет кластерный индекс:

CREATE UNIQUE CLUSTERED INDEX [PK_WorkCodeIPCode] ON [dbo].[WS.IntrayCodeToIPCode]
(
	[WorkCode] ASC,
	[IPCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Этот факт и объясняет deadlock?
Если да - то как именно?
6 июн 18, 18:49    [21473770]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
KellyLynch,

Таки вот тут кошерная статья для понимания, как могут возникать deadlock, если у Вас кластерный поверх view. Там не insert, а два update, но общий принцип думаю понятен.
6 июн 18, 19:18    [21473865]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
KellyLynch,

Как вариант с потолка - попробуйте в оба insert добавлять явно TABLOCKX или TABLOCK.
6 июн 18, 19:20    [21473871]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
KellyLynch
TaPaK
KellyLynch,

IntrayCodeToIPCode на ней поди и кластерный есть?


Да - view [WS.IntrayCodeToIPCode] имеет кластерный индекс:

CREATE UNIQUE CLUSTERED INDEX [PK_WorkCodeIPCode] ON [dbo].[WS.IntrayCodeToIPCode]
(
	[WorkCode] ASC,
	[IPCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Этот факт и объясняет deadlock?
Если да - то как именно?

изменение объекта ведёт к изменению представление общий объект которых получаете WorkContributor, и + группировка, или избавиться от индексированного представление(совсем не факт что оно вам надо) или думать как разводить
6 июн 18, 20:01    [21473951]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
KellyLynch
Этот факт и объясняет deadlock?
Отчасти.
KellyLynch
Если да - то как именно?
1. В транзакции, где вызывается [IP.InsertIPCAEDetail], ранее была прочитана таблица [Work.WorkContributor] с хинтом serializable или holdlock - это причина наличия RangeS-S в графе.
При выполнении вставки в [IPCAEDetail] нужно обновить инднексированное представление [WS.IntrayCodeToIPCode] - отсюда ожидание U в графе

2. В транзакции, где вызывается [SRD.MergeWork], ранее были действия вызвавшие обновление представления [WS.IntrayCodeToIPCode] - отсюда U в графе
Вставка в [Work.WorkContributor] порождает RangeI-N, которая конфликтует с RangeS-S из п.1

Для начала выясните зачем используется чтение с serializable или holdlock.
6 июн 18, 20:01    [21473952]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
KellyLynch
Member

Откуда:
Сообщений: 87
TaPaK
KellyLynch
пропущено...


Да - view [WS.IntrayCodeToIPCode] имеет кластерный индекс:

CREATE UNIQUE CLUSTERED INDEX [PK_WorkCodeIPCode] ON [dbo].[WS.IntrayCodeToIPCode]
(
	[WorkCode] ASC,
	[IPCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



Этот факт и объясняет deadlock?
Если да - то как именно?

изменение объекта ведёт к изменению представление общий объект которых получаете WorkContributor, и + группировка, или избавиться от индексированного представление(совсем не факт что оно вам надо) или думать как разводить



"изменение объекта ведёт к изменению представление общий объект которых получаете WorkContributor" - прошу прощения, а можно то же самое, но более "разжёванно"? - не уверен что всё понял...
7 июн 18, 18:13    [21477158]     Ответить | Цитировать Сообщить модератору
 Re: Помогите понять deadlock  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
KellyLynch
TaPaK
пропущено...

изменение объекта ведёт к изменению представление общий объект которых получаете WorkContributor, и + группировка, или избавиться от индексированного представление(совсем не факт что оно вам надо) или думать как разводить



"изменение объекта ведёт к изменению представление общий объект которых получаете WorkContributor" - прошу прощения, а можно то же самое, но более "разжёванно"? - не уверен что всё понял...

в индексированном вью у вас соединение двух таблиц. изменение любой из них ведет к пересчету этого вью. в вашем случае идет вставка в IPCAEDetail, это означает, что и во вью надо пересчитать count для IpCode
8 июн 18, 14:55    [21479384]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить