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

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

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

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

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

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

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

К сообщению приложен файл (Deadlock.xdl - 8Kb) cкачать
16 фев 18, 12:49    [21196542]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

Откуда:
Сообщений: 16
Картинка deadlock

К сообщению приложен файл. Размер - 29Kb
16 фев 18, 12:51    [21196551]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

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

имхо
INSERT INTO Data.Calculation WITH (ROWLOCK, HOLDLOCK)
16 фев 18, 13:30    [21196753]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
egorrezchikov
2. Идёт вставка множества записей в таблицы, которые связанны с таблицей Data.Calculation по внешнему ключу (FK_Calculation_....)
И они пересекаются по CalculationId с данными, вставленными в Data.Calculation в других соединениях?
16 фев 18, 14:47    [21197064]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
Согласно графу deadlock
Guest
TaPaK,

помочь может как раз наоборот принудительная ескалация вверх
16 фев 18, 14:59    [21197130]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Согласно графу deadlock
TaPaK,

помочь может как раз наоборот принудительная ескалация вверх

то таки вы тока ROWLOCK видите? или всё лечим табоками?
16 фев 18, 15:01    [21197140]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

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

Нет, данные с другими соединениям не пересекаются, в каждом соединении используется свой CalсulationId.
Есть таблица Data.Calculation которая описывает шапку расчёта и 4 таблицы в одной их которых содержатся результаты расчёта и входные данные расчёта. Как раз в этих 4 таблицах используется CalсulationId как внешний ключ для результатов и входных данных.
16 фев 18, 15:11    [21197189]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

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

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

К сообщению приложен файл. Размер - 40Kb
16 фев 18, 15:13    [21197198]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
Согласно графу deadlock
Guest
egorrezchikov,

я б предложил FK или совсем пристрелить (на возможность этого нам намекает название схемы) или отключать на время объемных вставок в зависимые таблицы. Или с той стороны (да в целом хоть с какой) PAGLOCK воткни и попробуй.
16 фев 18, 15:19    [21197224]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

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

а как вы @CalculationId получаете??
16 фев 18, 15:24    [21197238]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

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

Sequence
16 фев 18, 15:28    [21197260]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

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

покажите весь скрипт
16 фев 18, 15:35    [21197279]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Ну это типовая ситуация. Индекс + кластерный индекс процесс пишет и тут же читает + второй такой же процесс.

Скорее всего, поможет покрывающий индекс, дабы правый инсерт с поздапросом не лез в кластерный индекс за недостающими полями.
А ещё лучше перепишите вставку без подзапросов.
16 фев 18, 15:43    [21197312]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов
Ну это типовая ситуация. Индекс + кластерный индекс процесс пишет и тут же читает + второй такой же процесс.

Скорее всего, поможет покрывающий индекс, дабы правый инсерт с поздапросом не лез в кластерный индекс за недостающими полями.
А ещё лучше перепишите вставку без подзапросов.

т.е. граф не открывали?
16 фев 18, 15:44    [21197325]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

Откуда:
Сообщений: 16
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
16 фев 18, 15:45    [21197326]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
Konst_One
Member

Откуда:
Сообщений: 11522
в общую транзакцию оберните
16 фев 18, 15:47    [21197334]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

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

так а где ваше Meteo?
16 фев 18, 15:49    [21197339]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

Откуда:
Сообщений: 16
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
16 фев 18, 15:54    [21197363]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
egorrezchikov
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
В этом скрипте нет конфликтующих инструкций из графа дедлока.

В общем, конфликтуют у вас для Data.Calculation вставка и чтение при проверке FK.
С учетом
egorrezchikov
Нет, данные с другими соединениям не пересекаются, в каждом соединении используется свой CalсulationId.
Такое может происходить если в инструкциях insert для соединения с Data.Calculation при проверке FK используется merge или hash.
Если это так, то можете попробовать вылечить, дописав к инструкциям вставки в подчиненные таблицы option(loop join)
16 фев 18, 15:58    [21197380]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
у вас FK чистые или с какими-то глупостями класса каскад делита ?
16 фев 18, 16:10    [21197429]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

Откуда:
Сообщений: 16
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
16 фев 18, 16:15    [21197440]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

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

а для 21197198
xml есть?
16 фев 18, 16:21    [21197463]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
egorrezchikov
Member

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

Да есть.

К сообщению приложен файл (Deadlock3.xdl - 8Kb) cкачать
16 фев 18, 16:24    [21197481]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
TaPaK
Member

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

на всю транзакцию SERIALIZABLE

можно убрать WITH (ROWLOCK, HOLDLOCK) или пробовать ставить на все инсёрты
16 фев 18, 16:35    [21197538]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с deadlock PK Index  [new]
или всё лечим табоками
Guest
TaPaK,

автор
или всё лечим табоками?

Какой нахрен SERIALIZABLE. SingleUser уже давай. Какие к черту ROWLOCK. У него из-за ROWLOCK проблемы и лезут.
SERIALIZABLE при массовых вставках - вообще ни разу не таблок, да? еще и при FK.
16 фев 18, 16:44    [21197585]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить