Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите понять deadlock / 11 сообщений из 11, страница 1 из 1
06.06.2018, 18:07
    #39656794
KellyLynch
KellyLynch
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
У меня есть база на 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:

Код: sql
1.
2.
3.
4.
5.
6.
7.
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:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
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]:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
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]:

Код: sql
1.
2.
3.
4.
5.
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]:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 18:16
    #39656807
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
KellyLynch,

IntrayCodeToIPCode на ней поди и кластерный есть?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 18:17
    #39656809
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
KellyLynch,

От вашей картинки толку никакого. Показывайте граф в xml формате.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 18:45
    #39656835
KellyLynch
KellyLynch
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
invmKellyLynch,

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

Вот он:

xml_deadlock_report (2018-06-05 15_32_14.2089385) - xml_report10.xml
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 18:49
    #39656839
KellyLynch
KellyLynch
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
TaPaKKellyLynch,

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

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

Код: sql
1.
2.
3.
4.
5.
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?
Если да - то как именно?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 19:18
    #39656864
Andy_OLAP
Andy_OLAP
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
KellyLynch,

Таки вот тут кошерная статья для понимания, как могут возникать deadlock, если у Вас кластерный поверх view. Там не insert, а два update , но общий принцип думаю понятен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 19:20
    #39656866
Andy_OLAP
Andy_OLAP
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
KellyLynch,

Как вариант с потолка - попробуйте в оба insert добавлять явно TABLOCKX или TABLOCK.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 20:01
    #39656896
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
KellyLynchTaPaKKellyLynch,

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

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

Код: sql
1.
2.
3.
4.
5.
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, и + группировка, или избавиться от индексированного представление(совсем не факт что оно вам надо) или думать как разводить
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 20:01
    #39656897
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
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.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.06.2018, 18:13
    #39657763
KellyLynch
KellyLynch
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
TaPaKKellyLynchпропущено...


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

Код: sql
1.
2.
3.
4.
5.
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 " - прошу прощения, а можно то же самое, но более "разжёванно"? - не уверен что всё понял...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2018, 14:55
    #39658410
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите понять deadlock
KellyLynchTaPaKпропущено...

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


" изменение объекта ведёт к изменению представление общий объект которых получаете WorkContributor " - прошу прощения, а можно то же самое, но более "разжёванно"? - не уверен что всё понял...
в индексированном вью у вас соединение двух таблиц. изменение любой из них ведет к пересчету этого вью. в вашем случае идет вставка в IPCAEDetail, это означает, что и во вью надо пересчитать count для IpCode
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите понять deadlock / 11 сообщений из 11, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]