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

Откуда:
Сообщений: 122
Всем доброго времени суток.

В одной хранимой процедуре встречаются подряд 2 следующих запроса:
+
	;WITH VD as (
		SELECT
			MetadataSourceValueDomainId,
			MetadataSourceId,
			NamespaceId,
			UniqueName,
			MaxLength,
			MinLength,
			DecimalPlaces,
			MaxValue,
			MinValue,
			DefaultValue,
			IsActive,
			MetadataSourceIdentifierTypeId,
			RegularExpression,
			EnumerationTypeId
		FROM ValueDomain
		WHERE MetadataSourceId = @MetadataSourceId)
	MERGE VD as T
	USING (
		SELECT
			S_VD.ValueDomainId,
			MetadataSourceId = @MetadataSourceId,
			MN.NamespaceId,
			S_VD.UniqueName,
			S_VD.MaxLength,
			S_VD.MinLength,
			S_VD.DecimalPlaces,
			S_VD.MaxValue,
			S_VD.MinValue,
			S_VD.DefaultValue,
			S_VD.IsActive,
			IdentifierTypeId = VD_IT.ValueDomainEnumerationId,
			ET.EnumerationTypeId,
			S_VD.RegularExpression
		FROM Staging.dbo.Service_ValueDomain S_VD
			INNER JOIN ValueDomainEnumeration VD_IT ON VD_IT.MetadataSourceValueDomainEnumerationId = S_VD.ValueDomainIdType
			INNER JOIN MetadataNamespace MN ON S_VD.NamespaceUniqueName = MN.UniqueName
			INNER JOIN EnumerationType ET ON et.UniqueName = S_VD.EnumerationType
		  ) as SS
	ON T.MetadataSourceValueDomainId = CAST(SS.ValueDomainId AS NVARCHAR(255))
		AND T.MetadataSourceIdentifierTypeId = SS.IdentifierTypeId



+
	;WITH VDE as (
		SELECT
			MetadataSourceValueDomainEnumerationId,
			MetadataSourceId,
			ValueDomainId,
			NamespaceId,
			UniqueName,
			Value,
			DecimalPlaces,
			IsActive,
			MetadataSourceIdentifierTypeId
		FROM ValueDomainEnumeration
		WHERE MetadataSourceId = @MetadataSourceId)
	MERGE VDE as T
	USING (
	SELECT
			S_VDE.ValueDomainEnumerationId,
			MetadataSourceId = @MetadataSourceId,
			VD.ValueDomainId,
			MN.NamespaceId,
			S_VDE.UniqueName,
			S_VDE.Value,
			S_VDE.DecimalPlaces,
			S_VDE.IsActive,
			IdentifierTypeId = VDE_IT.ValueDomainEnumerationId
		FROM Staging.dbo.Service_ValueDomainEnumeration S_VDE
			INNER JOIN ValueDomainEnumeration VDE_IT ON VDE_IT.MetadataSourceValueDomainEnumerationId = S_VDE.ValueDomainEnumerationIdType
			INNER JOIN ValueDomain VD ON VD.MetadataSourceValueDomainId = CAST(S_VDE.ValueDomainId as NVARCHAR(255))
			INNER JOIN ValueDomainEnumeration VD_IT ON VD_IT.ValueDomainEnumerationId = VD.MetadataSourceIdentifierTypeId
												AND VD_IT.MetadataSourceValueDomainEnumerationId = S_VDE.ValueDomainIdType
			INNER JOIN MetadataNamespace MN ON MN.UniqueName = S_VDE.NamespaceUniqueName
		  ) as SS
	ON T.MetadataSourceValueDomainEnumerationId = CAST(SS.ValueDomainEnumerationId as NVARCHAR(255))
		AND T.MetadataSourceIdentifierTypeId = SS.IdentifierTypeId


Еще немного информации из XML, которая была получена профайлером:
+
   <keylock hobtid="72057594056015872" dbid="14" objectname="dbo.ValueDomain" indexname="UX_ValueDomain_U1" id="lock115a5d80" mode="X" associatedObjectId="72057594056015872">
    <owner-list>
     <owner id="process6676550" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process38110aa8" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594041925632" dbid="14" objectname="dbo.ValueDomain" indexname="PK_ValueDomain" id="lock136e8900" mode="X" associatedObjectId="72057594041925632">
    <owner-list>
     <owner id="process38110aa8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6676550" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>


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

Заранее спасибо за помощь.
16 июн 11, 11:48    [10820842]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Леша777
Guest
Первым делом убрать CAST(SS.ValueDomainId AS NVARCHAR(255)).
Хорошо бы описание таблицы ValueDomain столбцы, тпы данных, индексы.
И план 1 запроса.
16 июн 11, 12:04    [10820942]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
Таблица ValueDomain:
+
CREATE TABLE [dbo].[ValueDomain](
	[ValueDomainId] [bigint] IDENTITY(1,1) NOT NULL,
	[MetadataSourceValueDomainId] [nvarchar](255) NOT NULL,
	[MetadataSourceId] [bigint] NOT NULL,
	[ParentValueDomainId] [bigint] NULL,
	[NamespaceId] [bigint] NOT NULL,
	[UniqueName] [dbo].[MetadataUniqueName1] NOT NULL,
	[DataTypeId] [bigint] NULL,
	[UnitId] [bigint] NULL,
	[MaxLength] [bigint] NULL,
	[MinLength] [bigint] NULL,
	[DecimalPlaces] [bigint] NULL,
	[MaxValue] [nvarchar](4000) NULL,
	[MinValue] [nvarchar](4000) NULL,
	[DefaultValue] [nvarchar](4000) NULL,
	[ActiveTo] [datetime] NULL,
	[IsActive] [bit] NOT NULL,
	[MetadataSourceIdentifierTypeId] [bigint] NOT NULL,
	[EnumerationTypeId] [bigint] NOT NULL,
	[RegularExpression] [nvarchar](4000) NULL,
 CONSTRAINT [PK_ValueDomain] PRIMARY KEY CLUSTERED 
(
	[ValueDomainId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY],
 CONSTRAINT [UX_ValueDomain_U1] UNIQUE NONCLUSTERED 
(
	[MetadataSourceValueDomainId] ASC,
	[MetadataSourceIdentifierTypeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UX_ValueDomain_U2] UNIQUE NONCLUSTERED 
(
	[NamespaceId] ASC,
	[UniqueName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO
16 июн 11, 12:16    [10820997]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
План первого запроса:
|--Nested Loops(Inner Join, OUTER REFERENCES:([S_VD].[ValueDomainIdType]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([S_VD].[NamespaceUniqueName]))
       |    |--Hash Match(Inner Join, HASH:([ET].[UniqueName])=([S_VD].[EnumerationType]), RESIDUAL:([DB].[dbo].[EnumerationType].[UniqueName] as [ET].[UniqueName]=[Staging].[dbo].[Service_ValueDomain].[EnumerationType] as [S_VD].[EnumerationType]))
       |    |    |--Clustered Index Scan(OBJECT:([DB].[dbo].[EnumerationType].[PK_EnumerationType] AS [ET]))
       |    |    |--Table Scan(OBJECT:([Staging].[dbo].[Service_ValueDomain] AS [S_VD]))
       |    |--Index Seek(OBJECT:([DB].[dbo].[MetadataNamespace].[UX_MetadataNamespace_U2] AS [MN]), SEEK:([MN].[UniqueName]=[Staging].[dbo].[Service_ValueDomain].[NamespaceUniqueName] as [S_VD].[NamespaceUniqueName]) ORDERED FORWARD)
       |--Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[UX_ValueDomainEnumeration_U1] AS [VD_IT]), SEEK:([VD_IT].[MetadataSourceValueDomainEnumerationId]=[Staging].[dbo].[Service_ValueDomain].[ValueDomainIdType] as [S_VD].[ValueDomainIdType]) ORDERED FORWARD)


Сообщение было отредактировано: 16 июн 11, 12:24
16 июн 11, 12:20    [10821016]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
Забыл добавить:
Взаимоблокировки возникают не всегда, даже я бы сказал очень редко.
Обновления данных не происходит однозначно, то есть взаимоблокировки именно на чтение
16 июн 11, 12:31    [10821101]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Леша777
Guest
А в таблице Staging.dbo.Service_ValueDomain какой тип у столбца ValueDomainId ?
В плане не вижу соедиения ValueDomain со Staging.dbo.Service_ValueDomain.
Нужно как-то привести ты данных MetadataSourceValueDomainId из таблицы ValueDomain и столбцом ValueDomainId из Staging.dbo.Service_ValueDomain.
16 июн 11, 12:31    [10821104]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Классика жанра.
UpdLock на первом запросе или до.
Не?
16 июн 11, 12:39    [10821150]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
Полный план 1 запроса:
+
|--Assert(WHERE:(CASE WHEN NOT [Pass1336] AND [Expr1335] IS NULL THEN (0) ELSE NULL END))
|--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4) OR [DB].[dbo].[ValueDomain].[NamespaceId] IS NULL), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[NamespaceId]), DEFINE:([Expr1335] = [PROBE VALUE]))
|--Index Update(OBJECT:([DB].[dbo].[ValueDomain].[UX_ValueDomain_U2]), SET:([ValueDomainId1325] = [DB].[dbo].[ValueDomain].[ValueDomainId],[NamespaceId1326] = [DB].[dbo].[ValueDomain].[NamespaceId],[UniqueName1327] = [DB].[dbo].[ValueDomain].[UniqueName]) ACTION:([Action1042]))
| |--Collapse(GROUP BY:([DB].[dbo].[ValueDomain].[NamespaceId], [DB].[dbo].[ValueDomain].[UniqueName]))
| |--Sort(ORDER BY:([DB].[dbo].[ValueDomain].[NamespaceId] ASC, [DB].[dbo].[ValueDomain].[UniqueName] ASC, [Action1042] ASC))
| |--Filter(WHERE:(NOT [Expr1299]))
| |--Split
| |--Assert(WHERE:(CASE WHEN NOT [Pass1316] AND [Expr1315] IS NULL THEN (0) ELSE CASE WHEN NOT [Pass1318] AND [Expr1317] IS NULL THEN (1) ELSE CASE WHEN NOT [Pass1320] AND [Expr1319] IS NULL THEN (2) ELSE CASE WHEN NOT [Pass1322] AND [Expr1321] IS NULL THEN (3) ELSE CASE WHEN NOT [Pass1324] AND [Expr1323] IS NULL THEN (4) ELSE NULL END END END END END))
| |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4)), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]), DEFINE:([Expr1323] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4) OR [DB].[dbo].[ValueDomain].[DataTypeId] IS NULL), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[DataTypeId]), DEFINE:([Expr1321] = [PROBE VALUE]))
| | |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4) OR [DB].[dbo].[ValueDomain].[UnitId] IS NULL), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[UnitId]), DEFINE:([Expr1319] = [PROBE VALUE]))
| | | |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4)), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[MetadataSourceId]), DEFINE:([Expr1317] = [PROBE VALUE]))
| | | | |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]=(3)), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[EnumerationTypeId]), DEFINE:([Expr1315] = [PROBE VALUE]))
| | | | | |--Clustered Index Insert(OBJECT:([DB].[sys].[change_tracking_69575286].[sys_change_cidx]), SET:([DB].[sys].[change_tracking_69575286].[sys_change_xdes_id] = [Expr1308],[DB].[sys].[change_tracking_69575286].[sys_change_xdes_id_seq] = [Expr1309],[DB].[sys].[change_tracking_69575286].[sys_change_operation] = [Expr1310],[DB].[sys].[change_tracking_69575286].[sys_change_columns] = [Expr1311],[DB].[sys].[change_tracking_69575286].[sys_change_context] = [Expr1312],[DB].[sys].[change_tracking_69575286].[k_ValueDomainId_00000001] = [DB].[dbo].[ValueDomain].[ValueDomainId]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1308]=GetDbXdesId(), [Expr1310]=CASE WHEN [Action1042]=(3) THEN N'D' ELSE CASE WHEN [Action1042]=(4) THEN N'I' ELSE N'U' END END, [Expr1311]=CASE WHEN [Action1042]=(1) THEN 0x000000000C0000000B000000050000001300000010000000120000000D000000060000000E0000000A00000009000000 ELSE NULL END, [Expr1312]=NULL))
| | | | | | |--Compute Scalar(DEFINE:([Expr1309]=getsilentidentity((1364199910),(8),NULL)))
| | | | | | |--Clustered Index Merge(OBJECT:([DB].[dbo].[ValueDomain].[PK_ValueDomain]), OBJECT:([DB].[dbo].[ValueDomain].[IX_ValueDomain_ParentValueDomainId]), OBJECT:([DB].[dbo].[ValueDomain].[IX_ValueDomain_DataTypeId]), OBJECT:([DB].[dbo].[ValueDomain].[IX_ValueDomain_UnitId]), OBJECT:([DB].[dbo].[ValueDomain].[UX_ValueDomain_U1]), SET:(Insert, [DB].[dbo].[ValueDomain].[MaxValue] = [Expr1044],[DB].[dbo].[ValueDomain].[DecimalPlaces] = [Expr1045],[DB].[dbo].[ValueDomain].[NamespaceId] = RaiseIfNullUpdate([Expr1046]),[DB].[dbo].[ValueDomain].[RegularExpression] = [Expr1047],[DB].[dbo].[ValueDomain].[IsActive] = RaiseIfNullUpdate([Expr1048]),[DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId] = RaiseIfNullUpdate([Expr1049]),[DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId] = RaiseIfNullUpdate([Expr1050]),[DB].[dbo].[ValueDomain].[EnumerationTypeId] = RaiseIfNullUpdate([Expr1051]),[DB].[dbo].[ValueDomain].[MinValue] = [Expr1052],[DB].[dbo].[ValueDomain].[UniqueName] = RaiseIfNullUpdate([Expr1053]),[DB].[dbo].[ValueDomain].[MetadataSourceId] = RaiseIfNullUpdate([Expr1054]),[DB].[dbo].[ValueDomain].[DefaultValue] = [Expr1055],[DB].[dbo].[ValueDomain].[MinLength] = [Expr1056],[DB].[dbo].[ValueDomain].[MaxLength] = [Expr1057],[DB].[dbo].[ValueDomain].[ValueDomainId] = [Expr1058],[DB].[dbo].[ValueDomain].[ParentValueDomainId] = [Expr1059],[DB].[dbo].[ValueDomain].[DataTypeId] = [Expr1060],[DB].[dbo].[ValueDomain].[UnitId] = [Expr1061],[DB].[dbo].[ValueDomain].[ActiveTo] = [Expr1062],[DB].[dbo].[ValueDomain].[$sys_change_xdes_id] = [Expr1105]), SET:(Update, [DB].[dbo].[ValueDomain].[MaxValue] = [Expr1044],[DB].[dbo].[ValueDomain].[DecimalPlaces] = [Expr1045],[DB].[dbo].[ValueDomain].[NamespaceId] = RaiseIfNullUpdate([Expr1046]),[DB].[dbo].[ValueDomain].[RegularExpression] = [Expr1047],[DB].[dbo].[ValueDomain].[IsActive] = RaiseIfNullUpdate([Expr1048]),[DB].[dbo].[ValueDomain].[EnumerationTypeId] = RaiseIfNullUpdate([Expr1051]),[DB].[dbo].[ValueDomain].[MinValue] = [Expr1052],[DB].[dbo].[ValueDomain].[UniqueName] = RaiseIfNullUpdate([Expr1053]),[DB].[dbo].[ValueDomain].[DefaultValue] = [Expr1055],[DB].[dbo].[ValueDomain].[MinLength] = [Expr1056],[DB].[dbo].[ValueDomain].[MaxLength] = [Expr1057],[DB].[dbo].[ValueDomain].[$sys_change_xdes_id] = [Expr1105]) ACTION:([Action1042]))
| | | | | | |--Compute Scalar(DEFINE:([Action1042]=[Action1042], [Expr1299]=[Expr1299], [Expr1295]=[Expr1295], [Expr1296]=[Expr1296], [Expr1297]=[Expr1297], [Expr1298]=[Expr1298]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1295]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1296]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1297]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1298]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1299]=CASE WHEN [Expr1086] AND [Expr1093] THEN (1) ELSE (0) END))
| | | | | | |--Compute Scalar(DEFINE:([Expr1105]=GetDbXdesId()))
| | | | | | |--Assert(WHERE:(CASE WHEN [Expr1104]>(1) THEN (0) ELSE NULL END))
| | | | | | |--Sequence Project(DEFINE:([Expr1104]=conditional_row_number))
| | | | | | |--Segment
| | | | | | |--Sort(ORDER BY:([DB].[dbo].[ValueDomain].[ValueDomainId] ASC))
| | | | | | |--Compute Scalar(DEFINE:([Expr1044]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1016] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1016] ELSE [DB].[dbo].[ValueDomain].[MaxValue] END END, [Expr1045]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1015] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1015] ELSE [DB].[dbo].[ValueDomain].[DecimalPlaces] END END, [Expr1046]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1011] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1011] ELSE [DB].[dbo].[ValueDomain].[NamespaceId] END END, [Expr1047]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1022] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1022] ELSE [DB].[dbo].[ValueDomain].[RegularExpression] END END, [Expr1048]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NULL THEN (0) ELSE [Expr1019] END, [Expr1049]=CASE WHEN [Action1042]=(4) THEN [Expr1020] ELSE [DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId] END, [Expr1050]=CASE WHEN [Action1042]=(4) THEN [Expr1009] ELSE [DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId] END, [Expr1051]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1021] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1021] ELSE [DB].[dbo].[ValueDomain].[EnumerationTypeId] END END, [Expr1052]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1017] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1017] ELSE [DB].[dbo].[ValueDomain].[MinValue] END END, [Expr1053]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1012] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1012] ELSE [DB].[dbo].[ValueDomain].[UniqueName] END END, [Expr1054]=CASE WHEN [Action1042]=(4) THEN CONVERT_IMPLICIT(bigint,[Expr1010],0) ELSE [DB].[dbo].[ValueDomain].[MetadataSourceId] END, [Expr1055]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1018] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1018] ELSE [DB].[dbo].[ValueDomain].[DefaultValue] END END, [Expr1056]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1014] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1014] ELSE [DB].[dbo].[ValueDomain].[MinLength] END END, [Expr1057]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1013] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1013] ELSE [DB].[dbo].[ValueDomain].[MaxLength] END END, [Expr1058]=CASE WHEN [Action1042]=(4) THEN [Expr1043] ELSE [DB].[dbo].[ValueDomain].[ValueDomainId] END, [Expr1059]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[ParentValueDomainId] END, [Expr1060]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[DataTypeId] END, [Expr1061]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[UnitId] END, [Expr1062]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[ActiveTo] END, [Expr1086]=[Action1042]=(1) AND CASE WHEN [DB].[dbo].[ValueDomain].[NamespaceId] = CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1011] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1011] ELSE [DB].[dbo].[ValueDomain].[NamespaceId] END END THEN (1) ELSE (0) END, [Expr1093]=[Action1042]=(1) AND CASE WHEN [DB].[dbo].[ValueDomain].[UniqueName] = CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1012] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1012] ELSE [DB].[dbo].[ValueDomain].[UniqueName] END END THEN (1) ELSE (0) END))
| | | | | | |--Compute Scalar(DEFINE:([Expr1043]=getconditionalidentity((69575286),(8),NULL,[Action1042]=(4))))
| | | | | | |--Top(ROWCOUNT est 0)
| | | | | | |--Filter(WHERE:([Action1042] IS NOT NULL))
| | | | | | |--Compute Scalar(DEFINE:([Action1042]=ForceOrder(CASE WHEN [SrcPrb1023] IS NOT NULL THEN CASE WHEN [TrgPrb1040] IS NOT NULL THEN CASE WHEN [Expr1028]<>[Expr1011] OR [Expr1029]<>[Expr1012] OR [Expr1030]<>[Expr1013] AND [Expr1030] IS NOT NULL AND [Expr1013] IS NOT NULL OR [Expr1030] IS NULL AND [Expr1013] IS NOT NULL OR [Expr1030] IS NOT NULL AND [Expr1013] IS NULL OR [Expr1031]<>[Expr1014] AND [Expr1031] IS NOT NULL AND [Expr1014] IS NOT NULL OR [Expr1031] IS NULL AND [Expr1014] IS NOT NULL OR [Expr1031] IS NOT NULL AND [Expr1014] IS NULL OR [Expr1032]<>[Expr1015] AND [Expr1032] IS NOT NULL AND [Expr1015] IS NOT NULL OR [Expr1032] IS NULL AND [Expr1015] IS NOT NULL OR [Expr1032] IS NOT NULL AND [Expr1015] IS NULL OR isnull([Expr1033],N'')<>isnull([Expr1016],N'') OR isnull([Expr1034],N'')<>isnull([Expr1017],N'') OR isnull([Expr1035],N'')<>isnull([Expr1018],N'') OR [Expr1036]<>[Expr1019] OR [Expr1039]<>[Expr1021] OR isnull([Expr1038],N'')<>isnull([Expr1022],N'') THEN (1) ELSE NULL END ELSE (4) END ELSE CASE WHEN [Expr1036]=(1) THEN (1) ELSE NULL END END)))
| | | | | | |--Hash Match(Full Outer Join, HASH:([S_VD].[ValueDomainId], [VD_IT].[ValueDomainEnumerationId])=([DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId], [DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]), RESIDUAL:([DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId]=[Staging].[dbo].[Service_ValueDomain].[ValueDomainId] as [S_VD].[ValueDomainId] AND [DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]=[DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId] as [VD_IT].[ValueDomainEnumerationId]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1010]=(8), [SrcPrb1023]=(1)))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1020]=[DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId] as [VD_IT].[ValueDomainEnumerationId]))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([S_VD].[ValueDomainIdType]))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1011]=[DB].[dbo].[MetadataNamespace].[NamespaceId] as [MN].[NamespaceId]))
| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([S_VD].[NamespaceUniqueName]))
| | | | | | | | |--Hash Match(Inner Join, HASH:([ET].[UniqueName])=([S_VD].[EnumerationType]), RESIDUAL:([DB].[dbo].[EnumerationType].[UniqueName] as [ET].[UniqueName]=[Staging].[dbo].[Service_ValueDomain].[EnumerationType] as [S_VD].[EnumerationType]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1021]=[DB].[dbo].[EnumerationType].[EnumerationTypeId] as [ET].[EnumerationTypeId]))
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([DB].[dbo].[EnumerationType].[PK_EnumerationType] AS [ET]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1009]=[Staging].[dbo].[Service_ValueDomain].[ValueDomainId] as [S_VD].[ValueDomainId], [Expr1012]=[Staging].[dbo].[Service_ValueDomain].[UniqueName] as [S_VD].[UniqueName], [Expr1013]=[Staging].[dbo].[Service_ValueDomain].[MaxLength] as [S_VD].[MaxLength], [Expr1014]=[Staging].[dbo].[Service_ValueDomain].[MinLength] as [S_VD].[MinLength], [Expr1015]=[Staging].[dbo].[Service_ValueDomain].[DecimalPlaces] as [S_VD].[DecimalPlaces], [Expr1016]=[Staging].[dbo].[Service_ValueDomain].[MaxValue] as [S_VD].[MaxValue], [Expr1017]=[Staging].[dbo].[Service_ValueDomain].[MinValue] as [S_VD].[MinValue], [Expr1018]=[Staging].[dbo].[Service_ValueDomain].[DefaultValue] as [S_VD].[DefaultValue], [Expr1019]=[Staging].[dbo].[Service_ValueDomain].[IsActive] as [S_VD].[IsActive], [Expr1022]=[Staging].[dbo].[Service_ValueDomain].[RegularExpression] as [S_VD].[RegularExpression]))
| | | | | | | | | |--Table Scan(OBJECT:([Staging].[dbo].[Service_ValueDomain] AS [S_VD]))
| | | | | | | | |--Index Seek(OBJECT:([DB].[dbo].[MetadataNamespace].[UX_MetadataNamespace_U2] AS [MN]), SEEK:([MN].[UniqueName]=[Staging].[dbo].[Service_ValueDomain].[NamespaceUniqueName] as [S_VD].[NamespaceUniqueName]) ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[UX_ValueDomainEnumeration_U1] AS [VD_IT]), SEEK:([VD_IT].[MetadataSourceValueDomainEnumerationId]=[Staging].[dbo].[Service_ValueDomain].[ValueDomainIdType] as [S_VD].[ValueDomainIdType]) ORDERED FORWARD)
| | | | | | |--Compute Scalar(DEFINE:([TrgPrb1040]=(1)))
| | | | | | |--Compute Scalar(DEFINE:([Expr1028]=[DB].[dbo].[ValueDomain].[NamespaceId], [Expr1029]=[DB].[dbo].[ValueDomain].[UniqueName], [Expr1030]=[DB].[dbo].[ValueDomain].[MaxLength], [Expr1031]=[DB].[dbo].[ValueDomain].[MinLength], [Expr1032]=[DB].[dbo].[ValueDomain].[DecimalPlaces], [Expr1033]=[DB].[dbo].[ValueDomain].[MaxValue], [Expr1034]=[DB].[dbo].[ValueDomain].[MinValue], [Expr1035]=[DB].[dbo].[ValueDomain].[DefaultValue], [Expr1036]=[DB].[dbo].[ValueDomain].[IsActive], [Expr1038]=[DB].[dbo].[ValueDomain].[RegularExpression], [Expr1039]=[DB].[dbo].[ValueDomain].[EnumerationTypeId]))
| | | | | | |--Clustered Index Scan(OBJECT:([DB].[dbo].[ValueDomain].[PK_ValueDomain]), WHERE:([DB].[dbo].[ValueDomain].[MetadataSourceId]=(8)) ORDERED)
| | | | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[EnumerationType].[PK_EnumerationType]), SEEK:([DB].[dbo].[EnumerationType].[EnumerationTypeId]=[DB].[dbo].[ValueDomain].[EnumerationTypeId]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[MetadataSource].[PK_MetadataSource]), SEEK:([DB].[dbo].[MetadataSource].[MetadataSourceId]=[DB].[dbo].[ValueDomain].[MetadataSourceId]) ORDERED FORWARD)
| | | |--Index Spool(SEEK:([DB].[dbo].[ValueDomain].[UnitId]=[DB].[dbo].[ValueDomain].[UnitId]))
| | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[PK_ValueDomainEnumeration]), SEEK:([DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId]=[DB].[dbo].[ValueDomain].[UnitId]) ORDERED FORWARD)
| | |--Index Spool(SEEK:([DB].[dbo].[ValueDomain].[DataTypeId]=[DB].[dbo].[ValueDomain].[DataTypeId]))
| | |--Clustered Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[PK_ValueDomainEnumeration]), SEEK:([DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId]=[DB].[dbo].[ValueDomain].[DataTypeId]) ORDERED FORWARD)
| |--Index Spool(SEEK:([DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]=[DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]))
| |--Clustered Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[PK_ValueDomainEnumeration]), SEEK:([DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId]=[DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([DB].[dbo].[MetadataNamespace].[PK_MetadataNamespace]), SEEK:([DB].[dbo].[MetadataNamespace].[NamespaceId]=[DB].[dbo].[ValueDomain].[NamespaceId]) ORDERED FORWARD)
16 июн 11, 12:42    [10821166]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

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

Обновления данных не происходит, или это не важно?
16 июн 11, 12:43    [10821171]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Всегда пишите схемы и не ссылайтесь напрямую в другие базы, используйте синонимы.
SS также заверните в WITH. Перечислять колонки в VDE и VD нет смысла.
16 июн 11, 12:54    [10821240]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
Mnior
Всегда пишите схемы и не ссылайтесь напрямую в другие базы, используйте синонимы.
SS также заверните в WITH.


Прошу прощение, немного не понял, что иментся ввиду. Можете чуть-чуть по подробнее?

и поможет ли это избежать deadlock-ов или просто как оптимизация?
16 июн 11, 12:59    [10821277]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PavM
просто как оптимизация
Угу.
PavM
Полный план 1 запроса:
Не читабельно.
PavM
Обновления данных не происходит
Ну дык вы то не полностью MERGE представили, а только шапку.
16 июн 11, 13:05    [10821349]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PavM
+ Полный план 1 запроса:
  |--Assert(WHERE:(CASE WHEN NOT [Pass1336] AND [Expr1335] IS NULL THEN (0) ELSE NULL END))
|--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4) OR [DB].[dbo].[ValueDomain].[NamespaceId] IS NULL), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[NamespaceId]), DEFINE:([Expr1335] = [PROBE VALUE]))
|--Index Update(OBJECT:([DB].[dbo].[ValueDomain].[UX_ValueDomain_U2]), SET:([ValueDomainId1325] = [DB].[dbo].[ValueDomain].[ValueDomainId],[NamespaceId1326] = [DB].[dbo].[ValueDomain].[NamespaceId],[UniqueName1327] = [DB].[dbo].[ValueDomain].[UniqueName]) ACTION:([Action1042]))
| |--Collapse(GROUP BY:([DB].[dbo].[ValueDomain].[NamespaceId], [DB].[dbo].[ValueDomain].[UniqueName]))
| |--Sort(ORDER BY:([DB].[dbo].[ValueDomain].[NamespaceId] ASC, [DB].[dbo].[ValueDomain].[UniqueName] ASC, [Action1042] ASC))
| |--Filter(WHERE:(NOT [Expr1299]))
| |--Split
| |--Assert(WHERE:(CASE WHEN NOT [Pass1316] AND [Expr1315] IS NULL THEN (0) ELSE CASE WHEN NOT [Pass1318] AND [Expr1317] IS NULL THEN (1) ELSE CASE WHEN NOT [Pass1320] AND [Expr1319] IS NULL THEN (2) ELSE CASE WHEN NOT [Pass1322] AND [Expr1321] IS NULL THEN (3) ELSE CASE WHEN NOT [Pass1324] AND [Expr1323] IS NULL THEN (4) ELSE NULL END END END END END))
| |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4)), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]), DEFINE:([Expr1323] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4) OR [DB].[dbo].[ValueDomain].[DataTypeId] IS NULL), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[DataTypeId]), DEFINE:([Expr1321] = [PROBE VALUE]))
| | |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4) OR [DB].[dbo].[ValueDomain].[UnitId] IS NULL), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[UnitId]), DEFINE:([Expr1319] = [PROBE VALUE]))
| | | |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]<>(4)), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[MetadataSourceId]), DEFINE:([Expr1317] = [PROBE VALUE]))
| | | | |--Nested Loops(Left Semi Join, PASSTHRU:([Action1042]=(3)), OUTER REFERENCES:([DB].[dbo].[ValueDomain].[EnumerationTypeId]), DEFINE:([Expr1315] = [PROBE VALUE]))
| | | | | |--Clustered Index Insert(OBJECT:([DB].[sys].[change_tracking_69575286].[sys_change_cidx]), SET:([DB].[sys].[change_tracking_69575286].[sys_change_xdes_id] = [Expr1308],[DB].[sys].[change_tracking_69575286].[sys_change_xdes_id_seq] = [Expr1309],[DB].[sys].[change_tracking_69575286].[sys_change_operation] = [Expr1310],[DB].[sys].[change_tracking_69575286].[sys_change_columns] = [Expr1311],[DB].[sys].[change_tracking_69575286].[sys_change_context] = [Expr1312],[DB].[sys].[change_tracking_69575286].[k_ValueDomainId_00000001] = [DB].[dbo].[ValueDomain].[ValueDomainId]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1308]=GetDbXdesId(), [Expr1310]=CASE WHEN [Action1042]=(3) THEN N'D' ELSE CASE WHEN [Action1042]=(4) THEN N'I' ELSE N'U' END END, [Expr1311]=CASE WHEN [Action1042]=(1) THEN 0x000000000C0000000B000000050000001300000010000000120000000D000000060000000E0000000A00000009000000 ELSE NULL END, [Expr1312]=NULL))
| | | | | | |--Compute Scalar(DEFINE:([Expr1309]=getsilentidentity((1364199910),(8),NULL)))
| | | | | | |--Clustered Index Merge(OBJECT:([DB].[dbo].[ValueDomain].[PK_ValueDomain]), OBJECT:([DB].[dbo].[ValueDomain].[IX_ValueDomain_ParentValueDomainId]), OBJECT:([DB].[dbo].[ValueDomain].[IX_ValueDomain_DataTypeId]), OBJECT:([DB].[dbo].[ValueDomain].[IX_ValueDomain_UnitId]), OBJECT:([DB].[dbo].[ValueDomain].[UX_ValueDomain_U1]), SET:(Insert, [DB].[dbo].[ValueDomain].[MaxValue] = [Expr1044],[DB].[dbo].[ValueDomain].[DecimalPlaces] = [Expr1045],[DB].[dbo].[ValueDomain].[NamespaceId] = RaiseIfNullUpdate([Expr1046]),[DB].[dbo].[ValueDomain].[RegularExpression] = [Expr1047],[DB].[dbo].[ValueDomain].[IsActive] = RaiseIfNullUpdate([Expr1048]),[DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId] = RaiseIfNullUpdate([Expr1049]),[DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId] = RaiseIfNullUpdate([Expr1050]),[DB].[dbo].[ValueDomain].[EnumerationTypeId] = RaiseIfNullUpdate([Expr1051]),[DB].[dbo].[ValueDomain].[MinValue] = [Expr1052],[DB].[dbo].[ValueDomain].[UniqueName] = RaiseIfNullUpdate([Expr1053]),[DB].[dbo].[ValueDomain].[MetadataSourceId] = RaiseIfNullUpdate([Expr1054]),[DB].[dbo].[ValueDomain].[DefaultValue] = [Expr1055],[DB].[dbo].[ValueDomain].[MinLength] = [Expr1056],[DB].[dbo].[ValueDomain].[MaxLength] = [Expr1057],[DB].[dbo].[ValueDomain].[ValueDomainId] = [Expr1058],[DB].[dbo].[ValueDomain].[ParentValueDomainId] = [Expr1059],[DB].[dbo].[ValueDomain].[DataTypeId] = [Expr1060],[DB].[dbo].[ValueDomain].[UnitId] = [Expr1061],[DB].[dbo].[ValueDomain].[ActiveTo] = [Expr1062],[DB].[dbo].[ValueDomain].[$sys_change_xdes_id] = [Expr1105]), SET:(Update, [DB].[dbo].[ValueDomain].[MaxValue] = [Expr1044],[DB].[dbo].[ValueDomain].[DecimalPlaces] = [Expr1045],[DB].[dbo].[ValueDomain].[NamespaceId] = RaiseIfNullUpdate([Expr1046]),[DB].[dbo].[ValueDomain].[RegularExpression] = [Expr1047],[DB].[dbo].[ValueDomain].[IsActive] = RaiseIfNullUpdate([Expr1048]),[DB].[dbo].[ValueDomain].[EnumerationTypeId] = RaiseIfNullUpdate([Expr1051]),[DB].[dbo].[ValueDomain].[MinValue] = [Expr1052],[DB].[dbo].[ValueDomain].[UniqueName] = RaiseIfNullUpdate([Expr1053]),[DB].[dbo].[ValueDomain].[DefaultValue] = [Expr1055],[DB].[dbo].[ValueDomain].[MinLength] = [Expr1056],[DB].[dbo].[ValueDomain].[MaxLength] = [Expr1057],[DB].[dbo].[ValueDomain].[$sys_change_xdes_id] = [Expr1105]) ACTION:([Action1042]))
| | | | | | |--Compute Scalar(DEFINE:([Action1042]=[Action1042], [Expr1299]=[Expr1299], [Expr1295]=[Expr1295], [Expr1296]=[Expr1296], [Expr1297]=[Expr1297], [Expr1298]=[Expr1298]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1295]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1296]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1297]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1298]=CASE WHEN [Action1042] = (1) THEN (0) ELSE [Action1042] END, [Expr1299]=CASE WHEN [Expr1086] AND [Expr1093] THEN (1) ELSE (0) END))
| | | | | | |--Compute Scalar(DEFINE:([Expr1105]=GetDbXdesId()))
| | | | | | |--Assert(WHERE:(CASE WHEN [Expr1104]>(1) THEN (0) ELSE NULL END))
| | | | | | |--Sequence Project(DEFINE:([Expr1104]=conditional_row_number))
| | | | | | |--Segment
| | | | | | |--Sort(ORDER BY:([DB].[dbo].[ValueDomain].[ValueDomainId] ASC))
| | | | | | |--Compute Scalar(DEFINE:([Expr1044]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1016] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1016] ELSE [DB].[dbo].[ValueDomain].[MaxValue] END END, [Expr1045]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1015] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1015] ELSE [DB].[dbo].[ValueDomain].[DecimalPlaces] END END, [Expr1046]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1011] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1011] ELSE [DB].[dbo].[ValueDomain].[NamespaceId] END END, [Expr1047]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1022] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1022] ELSE [DB].[dbo].[ValueDomain].[RegularExpression] END END, [Expr1048]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NULL THEN (0) ELSE [Expr1019] END, [Expr1049]=CASE WHEN [Action1042]=(4) THEN [Expr1020] ELSE [DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId] END, [Expr1050]=CASE WHEN [Action1042]=(4) THEN [Expr1009] ELSE [DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId] END, [Expr1051]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1021] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1021] ELSE [DB].[dbo].[ValueDomain].[EnumerationTypeId] END END, [Expr1052]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1017] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1017] ELSE [DB].[dbo].[ValueDomain].[MinValue] END END, [Expr1053]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1012] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1012] ELSE [DB].[dbo].[ValueDomain].[UniqueName] END END, [Expr1054]=CASE WHEN [Action1042]=(4) THEN CONVERT_IMPLICIT(bigint,[Expr1010],0) ELSE [DB].[dbo].[ValueDomain].[MetadataSourceId] END, [Expr1055]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1018] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1018] ELSE [DB].[dbo].[ValueDomain].[DefaultValue] END END, [Expr1056]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1014] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1014] ELSE [DB].[dbo].[ValueDomain].[MinLength] END END, [Expr1057]=CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1013] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1013] ELSE [DB].[dbo].[ValueDomain].[MaxLength] END END, [Expr1058]=CASE WHEN [Action1042]=(4) THEN [Expr1043] ELSE [DB].[dbo].[ValueDomain].[ValueDomainId] END, [Expr1059]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[ParentValueDomainId] END, [Expr1060]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[DataTypeId] END, [Expr1061]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[UnitId] END, [Expr1062]=CASE WHEN [Action1042]=(4) THEN NULL ELSE [DB].[dbo].[ValueDomain].[ActiveTo] END, [Expr1086]=[Action1042]=(1) AND CASE WHEN [DB].[dbo].[ValueDomain].[NamespaceId] = CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1011] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1011] ELSE [DB].[dbo].[ValueDomain].[NamespaceId] END END THEN (1) ELSE (0) END, [Expr1093]=[Action1042]=(1) AND CASE WHEN [DB].[dbo].[ValueDomain].[UniqueName] = CASE WHEN [Action1042]=(1) AND [SrcPrb1023] IS NOT NULL THEN [Expr1012] ELSE CASE WHEN [Action1042]=(4) THEN [Expr1012] ELSE [DB].[dbo].[ValueDomain].[UniqueName] END END THEN (1) ELSE (0) END))
| | | | | | |--Compute Scalar(DEFINE:([Expr1043]=getconditionalidentity((69575286),(8),NULL,[Action1042]=(4))))
| | | | | | |--Top(ROWCOUNT est 0)
| | | | | | |--Filter(WHERE:([Action1042] IS NOT NULL))
| | | | | | |--Compute Scalar(DEFINE:([Action1042]=ForceOrder(CASE WHEN [SrcPrb1023] IS NOT NULL THEN CASE WHEN [TrgPrb1040] IS NOT NULL THEN CASE WHEN [Expr1028]<>[Expr1011] OR [Expr1029]<>[Expr1012] OR [Expr1030]<>[Expr1013] AND [Expr1030] IS NOT NULL AND [Expr1013] IS NOT NULL OR [Expr1030] IS NULL AND [Expr1013] IS NOT NULL OR [Expr1030] IS NOT NULL AND [Expr1013] IS NULL OR [Expr1031]<>[Expr1014] AND [Expr1031] IS NOT NULL AND [Expr1014] IS NOT NULL OR [Expr1031] IS NULL AND [Expr1014] IS NOT NULL OR [Expr1031] IS NOT NULL AND [Expr1014] IS NULL OR [Expr1032]<>[Expr1015] AND [Expr1032] IS NOT NULL AND [Expr1015] IS NOT NULL OR [Expr1032] IS NULL AND [Expr1015] IS NOT NULL OR [Expr1032] IS NOT NULL AND [Expr1015] IS NULL OR isnull([Expr1033],N'')<>isnull([Expr1016],N'') OR isnull([Expr1034],N'')<>isnull([Expr1017],N'') OR isnull([Expr1035],N'')<>isnull([Expr1018],N'') OR [Expr1036]<>[Expr1019] OR [Expr1039]<>[Expr1021] OR isnull([Expr1038],N'')<>isnull([Expr1022],N'') THEN (1) ELSE NULL END ELSE (4) END ELSE CASE WHEN [Expr1036]=(1) THEN (1) ELSE NULL END END)))
| | | | | | |--Hash Match(Full Outer Join, HASH:([S_VD].[ValueDomainId], [VD_IT].[ValueDomainEnumerationId])=([DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId], [DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]), RESIDUAL:([DB].[dbo].[ValueDomain].[MetadataSourceValueDomainId]=[Staging].[dbo].[Service_ValueDomain].[ValueDomainId] as [S_VD].[ValueDomainId] AND [DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]=[DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId] as [VD_IT].[ValueDomainEnumerationId]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1010]=(8), [SrcPrb1023]=(1)))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1020]=[DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId] as [VD_IT].[ValueDomainEnumerationId]))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([S_VD].[ValueDomainIdType]))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1011]=[DB].[dbo].[MetadataNamespace].[NamespaceId] as [MN].[NamespaceId]))
| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([S_VD].[NamespaceUniqueName]))
| | | | | | | | |--Hash Match(Inner Join, HASH:([ET].[UniqueName])=([S_VD].[EnumerationType]), RESIDUAL:([DB].[dbo].[EnumerationType].[UniqueName] as [ET].[UniqueName]=[Staging].[dbo].[Service_ValueDomain].[EnumerationType] as [S_VD].[EnumerationType]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1021]=[DB].[dbo].[EnumerationType].[EnumerationTypeId] as [ET].[EnumerationTypeId]))
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([DB].[dbo].[EnumerationType].[PK_EnumerationType] AS [ET]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1009]=[Staging].[dbo].[Service_ValueDomain].[ValueDomainId] as [S_VD].[ValueDomainId], [Expr1012]=[Staging].[dbo].[Service_ValueDomain].[UniqueName] as [S_VD].[UniqueName], [Expr1013]=[Staging].[dbo].[Service_ValueDomain].[MaxLength] as [S_VD].[MaxLength], [Expr1014]=[Staging].[dbo].[Service_ValueDomain].[MinLength] as [S_VD].[MinLength], [Expr1015]=[Staging].[dbo].[Service_ValueDomain].[DecimalPlaces] as [S_VD].[DecimalPlaces], [Expr1016]=[Staging].[dbo].[Service_ValueDomain].[MaxValue] as [S_VD].[MaxValue], [Expr1017]=[Staging].[dbo].[Service_ValueDomain].[MinValue] as [S_VD].[MinValue], [Expr1018]=[Staging].[dbo].[Service_ValueDomain].[DefaultValue] as [S_VD].[DefaultValue], [Expr1019]=[Staging].[dbo].[Service_ValueDomain].[IsActive] as [S_VD].[IsActive], [Expr1022]=[Staging].[dbo].[Service_ValueDomain].[RegularExpression] as [S_VD].[RegularExpression]))
| | | | | | | | | |--Table Scan(OBJECT:([Staging].[dbo].[Service_ValueDomain] AS [S_VD]))
| | | | | | | | |--Index Seek(OBJECT:([DB].[dbo].[MetadataNamespace].[UX_MetadataNamespace_U2] AS [MN]), SEEK:([MN].[UniqueName]=[Staging].[dbo].[Service_ValueDomain].[NamespaceUniqueName] as [S_VD].[NamespaceUniqueName]) ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[UX_ValueDomainEnumeration_U1] AS [VD_IT]), SEEK:([VD_IT].[MetadataSourceValueDomainEnumerationId]=[Staging].[dbo].[Service_ValueDomain].[ValueDomainIdType] as [S_VD].[ValueDomainIdType]) ORDERED FORWARD)
| | | | | | |--Compute Scalar(DEFINE:([TrgPrb1040]=(1)))
| | | | | | |--Compute Scalar(DEFINE:([Expr1028]=[DB].[dbo].[ValueDomain].[NamespaceId], [Expr1029]=[DB].[dbo].[ValueDomain].[UniqueName], [Expr1030]=[DB].[dbo].[ValueDomain].[MaxLength], [Expr1031]=[DB].[dbo].[ValueDomain].[MinLength], [Expr1032]=[DB].[dbo].[ValueDomain].[DecimalPlaces], [Expr1033]=[DB].[dbo].[ValueDomain].[MaxValue], [Expr1034]=[DB].[dbo].[ValueDomain].[MinValue], [Expr1035]=[DB].[dbo].[ValueDomain].[DefaultValue], [Expr1036]=[DB].[dbo].[ValueDomain].[IsActive], [Expr1038]=[DB].[dbo].[ValueDomain].[RegularExpression], [Expr1039]=[DB].[dbo].[ValueDomain].[EnumerationTypeId]))
| | | | | | |--Clustered Index Scan(OBJECT:([DB].[dbo].[ValueDomain].[PK_ValueDomain]), WHERE:([DB].[dbo].[ValueDomain].[MetadataSourceId]=(8)) ORDERED)
| | | | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[EnumerationType].[PK_EnumerationType]), SEEK:([DB].[dbo].[EnumerationType].[EnumerationTypeId]=[DB].[dbo].[ValueDomain].[EnumerationTypeId]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[MetadataSource].[PK_MetadataSource]), SEEK:([DB].[dbo].[MetadataSource].[MetadataSourceId]=[DB].[dbo].[ValueDomain].[MetadataSourceId]) ORDERED FORWARD)
| | | |--Index Spool(SEEK:([DB].[dbo].[ValueDomain].[UnitId]=[DB].[dbo].[ValueDomain].[UnitId]))
| | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[PK_ValueDomainEnumeration]), SEEK:([DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId]=[DB].[dbo].[ValueDomain].[UnitId]) ORDERED FORWARD)
| | |--Index Spool(SEEK:([DB].[dbo].[ValueDomain].[DataTypeId]=[DB].[dbo].[ValueDomain].[DataTypeId]))
| | |--Clustered Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[PK_ValueDomainEnumeration]), SEEK:([DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId]=[DB].[dbo].[ValueDomain].[DataTypeId]) ORDERED FORWARD)
| |--Index Spool(SEEK:([DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]=[DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]))
| |--Clustered Index Seek(OBJECT:([DB].[dbo].[ValueDomainEnumeration].[PK_ValueDomainEnumeration]), SEEK:([DB].[dbo].[ValueDomainEnumeration].[ValueDomainEnumerationId]=[DB].[dbo].[ValueDomain].[MetadataSourceIdentifierTypeId]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([DB].[dbo].[MetadataNamespace].[PK_MetadataNamespace]), SEEK:([DB].[dbo].[MetadataNamespace].[NamespaceId]=[DB].[dbo].[ValueDomain].[NamespaceId]) ORDERED FORWARD)
Так лучше.
16 июн 11, 13:06    [10821358]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PavM
Еще немного информации из XML, которая была получена профайлером
Мало. Вы уверены что оба запроса участвуют в DeadLock?
Подозрения, что валится на одном, а не двух. MERGE коварный.

Жду полной инфы по запросам и DeadLock графе.
16 июн 11, 13:16    [10821458]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
Полная XML с профайлера

К сообщению приложен файл (deadlock.xml - 10Kb) cкачать
16 июн 11, 13:31    [10821558]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
Mnior
Ну дык вы то не полностью MERGE представили, а только шапку.


Ну дальше стандартные инсерты да апдейты
16 июн 11, 13:32    [10821567]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PavM
Обновления данных не происходит
PavM
Ну дальше стандартные инсерты да апдейты
Противоречия
16 июн 11, 13:51    [10821739]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
PavM
Member

Откуда:
Сообщений: 122
я имею ввиду, что на тех данных, на которых я получаю взаимоблокировки обновления данных в таблицах не происходит
16 июн 11, 14:00    [10821836]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Леша777
Guest
Сколько записей в ValueDomain. И сколько обычно отбирается условием MetadataSourceId = @MetadataSourceId ?
Возможно индекс MetadataSourceId, MetadataSourceValueDomainId,MetadataSourceIdentifierTypeId уникальный решил бы проблему. Или переделать кластерный так, чтобы ведущий столбец в нем был MetadataSourceId. Коллизий станет намного меньше.
16 июн 11, 15:12    [10822512]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
PavM
я имею ввиду, что на тех данных, на которых я получаю взаимоблокировки обновления данных в таблицах не происходит
Вода мокрая. ROLLBACK ролбэчит. До исполнения команды команда не выполняется.

Ваш MERGE понять тяжело. Вы хоть сами понимаете что там написано.
Почему есть условия на Target (@MetadataSourceId) и нет условий на Source?
16 июн 11, 15:14    [10822523]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с deadlock  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Леша777, у него может быть логическая ошибка.
Изменяются данные в двух потоках одной логической сущности.
Чёрт, так и есть:
1: exec SP_Service_RefreshDataElement_Full @ServiceName = 'WebQA'
2: exec SP_Service_RefreshDataElement_Full @ServiceName = 'WebQA'
Чё за фигня.

PavM, вы вообще понимаете что делаете?
16 июн 11, 15:27    [10822631]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить