Добрый день!

Кто может подсказать почему возникает deadlock и можно ли его побороть.

Согласно графу deadlock происходит Key Lock индекса PK_Calculation.

В БД выполняется сохранение связанных данных:
1. Идёт вставка 1 записи в таблицу Data.Calculation
2. Идёт вставка множества записей в таблицы, которые связанны с таблицей Data.Calculation по внешнему ключу (FK_Calculation_....)

Все deadlock одинаковые.

В приложении граф deadlock.

Картинка deadlock

invm,

Нет, данные с другими соединениям не пересекаются, в каждом соединении используется свой CalсulationId.
Есть таблица Data.Calculation которая описывает шапку расчёта и 4 таблицы в одной их которых содержатся результаты расчёта и входные данные расчёта. Как раз в этих 4 таблицах используется CalсulationId как внешний ключ для результатов и входных данных.

TaPaK,

После применения старые блокировки не ушли а добавились новые.

TaPaK,

Sequence

TaPaK,

DECLARE @CalculationId int = NEXT VALUE FOR Data.SeqCalculation;

INSERT INTO Data.Calculation WITH (ROWLOCK, HOLDLOCK) (CalculationId, TaskId, DateFrom, DateTo, CalcDateTime, StepTypeId, LocationId, TargetExternalSourceId, Target, Comment, UserProfileId, CalculationSetId)
VALUES (@CalculationId, @TaskId, @DateFrom, @DateTo, @CalcDateTime, @StepTypeId, @LocationId, @TargetExternalSourceId, @Target, @Comment, @UserProfileId, @CalculationSetId);




INSERT INTO Data.CalculationFactor(CalculationFactorId, CalculationId, FactorId, ValueDateTime, Value, ExternalSourceId, IsChanged)
SELECT ROW_NUMBER() OVER(ORDER BY ValueDateTime)+ CONVERT(bigint,@range_first_value_factor)-1,
@CalculationId, EntityId, ValueDateTime, Value, [Source], 0
FROM @factorData





INSERT INTO Data.CalculationResult(CalculationResultId, CalculationId,ObjectControlId,ValueDateTime,Mdp,Adp,MdpReverse,AdpReverse,IsControlKpos,MdpInstructionValueId,
AdpInstructionValueId, [Rule], MdpOriginal, AdpOriginal,Temperature,MdpMptFormula,ExternalSourceId,InstructionLineRepairId,ArchmState,IrregularFluctuation,MdpClear)
SELECT ROW_NUMBER() OVER(ORDER BY ValueDateTime)+ CONVERT(bigint,@range_first_value_result)-1,
@CalculationId,ObjectControlId,ValueDateTime,MdpValue,AdpValue,null,null,IsControlled,MdpInstructionValueId,AdpInstructionValueId,
[Rule], MdpOriginalValue, AdpOriginalValue,Temperature,MdpMptFormula,[Source],RepairSchemaId,ArchmState,IrregularFluctuation,MdpClear
FROM @resultValue

TaPaK,

Полная версия тела ХП


SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @TaskCode varchar(50);
SET @TaskCode = (SELECT Code FROM Sec.Task WITH(NOLOCK) WHERE TaskId = @TaskId)

DECLARE @ErrorMessage varchar(8000),@ErrorSeverity INT,@ErrorState INT,@ErrorNumber INT;
DECLARE @timeout varchar(20)
declare @rc int = 0 -- return code

DECLARE @triesMax int = 10;
DECLARE @tries int
SET @tries = 0
WHILE @tries<@triesMax
BEGIN

BEGIN TRY
BEGIN TRAN

DECLARE
@sequence_name nvarchar(100),
@range_size int,
@range_first_value_factor sql_variant,
@range_first_value_meteo sql_variant,
@range_first_value_ne sql_variant,
@range_first_value_result sql_variant,
@range_last_value sql_variant,
@sequence_increment sql_variant,
@sequence_min_value sql_variant,
@sequence_max_value sql_variant;

SET @sequence_name = 'Data.SeqCalculationFactor'
select @range_size = count(*) from @factorData
if @range_size>0
EXEC sp_sequence_get_range
@sequence_name = @sequence_name,
@range_size = @range_size,
@range_first_value = @range_first_value_factor OUTPUT,
@range_last_value = @range_last_value OUTPUT, @sequence_increment = @sequence_increment OUTPUT,@sequence_min_value = @sequence_min_value OUTPUT,@sequence_max_value = @sequence_max_value OUTPUT;

SET @sequence_name = 'Data.SeqCalculationResultMeteo'
select @range_size = count(*) from @meteoData
if @range_size>0
EXEC sp_sequence_get_range
@sequence_name = @sequence_name,
@range_size = @range_size,
@range_first_value = @range_first_value_meteo OUTPUT,
@range_last_value = @range_last_value OUTPUT, @sequence_increment = @sequence_increment OUTPUT,@sequence_min_value = @sequence_min_value OUTPUT,@sequence_max_value = @sequence_max_value OUTPUT;

SET @sequence_name = 'Data.SeqCalculationNetElement'
select @range_size = count(*) from @repairPoint
if @range_size>0
EXEC sp_sequence_get_range
@sequence_name = @sequence_name,
@range_size = @range_size,
@range_first_value = @range_first_value_ne OUTPUT,
@range_last_value = @range_last_value OUTPUT, @sequence_increment = @sequence_increment OUTPUT,@sequence_min_value = @sequence_min_value OUTPUT,@sequence_max_value = @sequence_max_value OUTPUT;

SET @sequence_name = 'Data.SeqCalculationResult'
select @range_size = count(*) from @resultValue
if @range_size>0
EXEC sp_sequence_get_range
@sequence_name = @sequence_name,
@range_size = @range_size,
@range_first_value = @range_first_value_result OUTPUT,
@range_last_value = @range_last_value OUTPUT, @sequence_increment = @sequence_increment OUTPUT,@sequence_min_value = @sequence_min_value OUTPUT,@sequence_max_value = @sequence_max_value OUTPUT;

IF 1 = 1
BEGIN

DECLARE @CalcDateTime datetime = GETUTCDATE();

DECLARE @StepTypeId int = '24';

DECLARE @TargetExternalSourceId int = NULL;
DECLARE @Target varchar(850) = NULL;

DECLARE @CalculationId int = NEXT VALUE FOR Data.SeqCalculation;

INSERT INTO Data.Calculation WITH (ROWLOCK, HOLDLOCK) (CalculationId, TaskId, DateFrom, DateTo, CalcDateTime, StepTypeId, LocationId, TargetExternalSourceId, Target, Comment, UserProfileId, CalculationSetId)
VALUES (@CalculationId, @TaskId, @DateFrom, @DateTo, @CalcDateTime, @StepTypeId, @LocationId, @TargetExternalSourceId, @Target, @Comment, @UserProfileId, @CalculationSetId);
END


IF 1 = 1
BEGIN

INSERT INTO Data.CalculationFactor(CalculationFactorId, CalculationId, FactorId, ValueDateTime, Value, ExternalSourceId, IsChanged)
SELECT ROW_NUMBER() OVER(ORDER BY ValueDateTime)+ CONVERT(bigint,@range_first_value_factor)-1,
@CalculationId, EntityId, ValueDateTime, Value, [Source], 0
FROM @factorData

END


IF 1 = 1
BEGIN
INSERT INTO Data.CalculationResultMeteo(CalculationResultMeteoId, CalculationId, MeteoPointId, EnergySystemId,ValueDateTime, MeteoTypeId, Value)
SELECT ROW_NUMBER() OVER(ORDER BY ValueDateTime)+ CONVERT(bigint,@range_first_value_meteo)-1,
@CalculationId,
CASE WHEN @TaskCode = '2' OR @TaskCode = '1' THEN EntityId ELSE NULL END AS MeteoPointId,
CASE WHEN @TaskCode = '3' THEN EntityId ELSE NULL END AS EnergySystemId,
ValueDateTime, null, Value
FROM @meteoData
where Value is not null or @TaskCode = '2' OR @TaskCode = '1'
END


IF 1 = 1
BEGIN
INSERT INTO Data.CalculationNetElement(CalculationNetElementId, CalculationId, NetElementId, ValueDateTime,
Value, ExternalSourceId, IsChanged)
SELECT ROW_NUMBER() OVER(ORDER BY PointDate)+ CONVERT(bigint,@range_first_value_ne)-1,
@CalculationId, NetElementId, PointDate,
1, [Source], IsChanged FROM @repairPoint
END


IF 1 = 1
BEGIN
INSERT INTO Data.CalculationResult(CalculationResultId,CalculationId,ObjectControlId,ValueDateTime,Mdp,Adp,MdpReverse,AdpReverse,IsControlKpos,MdpInstructionValueId,
AdpInstructionValueId, [Rule], MdpOriginal, AdpOriginal,Temperature,MdpMptFormula,ExternalSourceId,InstructionLineRepairId,ArchmState,IrregularFluctuation,MdpClear)
SELECT ROW_NUMBER() OVER(ORDER BY ValueDateTime)+ CONVERT(bigint,@range_first_value_result)-1,
@CalculationId,ObjectControlId,ValueDateTime,MdpValue,AdpValue,null,null,IsControlled,
case when MdpInstructionValueId = 0 then null else MdpInstructionValueId end as MdpInstructionValueId,
case when AdpInstructionValueId = 0 then null else AdpInstructionValueId end as AdpInstructionValueId,
[Rule], MdpOriginalValue, AdpOriginalValue,Temperature,MdpMptFormula,[Source],
case when RepairSchemaId = 0 then null else RepairSchemaId end as RepairSchemaId,
ArchmState,IrregularFluctuation,MdpClear
FROM @resultValue
END

SELECT @CalculationId AS CalculationId

COMMIT TRAN

END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
SET @tries = @tries + 1

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER();

IF (@ErrorNumber = 1205 OR @ErrorNumber = 1222) AND @tries < @triesMax
begin

set @timeout = '00:00:0'+Substring(CONVERT(varchar(20),RAND()),1,4)
WAITFOR DELAY @timeout
CONTINUE
end
RAISERROR(@ErrorMessage, 16, 1);

END CATCH

BREAK;
END -- TRIES

Maxx,

Да чистые. Вот скрипты на создание


ALTER TABLE [Data].[Calculation] ADD CONSTRAINT [PK_Calculation] PRIMARY KEY CLUSTERED
(
[CalculationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


ALTER TABLE [Data].[CalculationResult] WITH CHECK ADD CONSTRAINT [FK_Calculation_CalculationResult] FOREIGN KEY([CalculationId])
REFERENCES [Data].[Calculation] ([CalculationId])
GO

ALTER TABLE [Data].[CalculationResult] CHECK CONSTRAINT [FK_Calculation_CalculationResult]
GO

ALTER TABLE [Data].[CalculationResultMeteo] WITH CHECK ADD CONSTRAINT [FK_Calculation_CalculationResultMeteo] FOREIGN KEY([CalculationId])
REFERENCES [Data].[Calculation] ([CalculationId])
GO

ALTER TABLE [Data].[CalculationResultMeteo] CHECK CONSTRAINT [FK_Calculation_CalculationResultMeteo]
GO

ALTER TABLE [Data].[CalculationNetElement] WITH CHECK ADD CONSTRAINT [FK_Calculation_CalculationNetElement] FOREIGN KEY([CalculationId])
REFERENCES [Data].[Calculation] ([CalculationId])
GO

ALTER TABLE [Data].[CalculationNetElement] CHECK CONSTRAINT [FK_Calculation_CalculationNetElement]
GO

ALTER TABLE [Data].[CalculationFactor] WITH CHECK ADD CONSTRAINT [FK_Calculation_CalculationFactor] FOREIGN KEY([CalculationId])
REFERENCES [Data].[Calculation] ([CalculationId])
GO

ALTER TABLE [Data].[CalculationFactor] CHECK CONSTRAINT [FK_Calculation_CalculationFactor]
GO

TaPaK,

Да есть.

Может быть дело в NONCLUSTERED INDEX ?

В таблицах Data.CalculationResult, Data.CalculationNetElement есть индексы на внешний ключ CalculationId

CREATE NONCLUSTERED INDEX [IX_CalculationNetElement_CalculationId] ON [Data].[CalculationNetElement]
(
[CalculationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_CalculationResult_CalculationId] ON [Data].[CalculationResult]
(
[CalculationId] ASC
)
INCLUDE ( [ObjectControlId],
[ValueDateTime],
[Mdp]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


В остальных таблицах такого индекса нет.

Владислав Колосов,

Спасибо за совет, попробую.
Я так понял это единственное решение? Если удалить эти индексы, то потом может упасть скорость чтения данных. Я недавно читал про настройку ONLINE для индекса, она влияет только на режим rebuild или reorganize?

invm,

План запроса.
https://yadi.sk/d/a9K9DDoC3SUeRD