Добрый день! есть следующая проблема. Для автоматизации учета дебиторской задолженности был создан раздел. Записи в данный раздел попадают при добавлении счетов. Был создан триггер который срабатывает при UPDATE, Insert , Delete таблицы tbl_Invoice.Но при подгрузке большого количества счетов из Excel файла SQL server выдает ошибку дословно следующую "Транзакция (идентификатор процесса 142) вызвала взаимоблокировку ресурсов буфер связи с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно". Триггер запускает хранимую процедуру. Возможно стоит запускать ее в обработчике датасета счета? Подскажите пожалуста?
Код триггера.
ON [dbo].[tbl_Invoice]
after INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @ContractID uniqueidentifier;
declare @access int
SET @access = 0
IF EXISTS(SELECT ContractID FROM inserted)
begin
SET @ContractID =(SELECT top 1 ContractID FROM [Inserted])
IF (UPDATE(Amount) OR UPDATE(PaymentAmount))
begin
SET @access = 1;
end
end
else
begin
SET @ContractID =(SELECT top 1 ContractID FROM [Deleted])
SET @access = 1;
end
IF (@ContractID IS NULL)
begin
RETURN
end
IF (@access = 1)
begin
exec [dbo].[sp_CreateDebtor] @ContractID
end
END
Нравится
Здравствуйте Иван,
Не уверен что переписывание логики на событие Post Dataset'а поможет исправить ситуацию, в принципе, наверное стоит понять что собственно блокирует транзакцию, это можно сделать как описано http://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-…
Если вы конечно захотите реализовать вызов хранимой процедуры через события датасета, то это можно сделать следующим образом http://www.community.terrasoft.ru/forum/topic/7030#comment-30105
Как вариант импорта могу посоветовать делать Insert на прямую в таблицу через SQL Server посмотреть как это сделать можно по следующей ссылке http://www.community.terrasoft.ru/forum/topic/6954#comment-29730
Я предполагаю что блокирует транзакцию обновление таблицы дебиторов. А как это обойти?
Можете пожалуйста привести код хранимой процедуры sp_CreateDebtor
[sql]
ALTER PROCEDURE [dbo].[sp_CreateDebtor]
(@ContractID uniqueidentifier)
AS
BEGIN
SET NOCOUNT ON;
declare @DelayDebt decimal(15,4)
declare @AllDebt decimal(15,4)
declare @CurrentDebt decimal(15,4)
set @DelayDebt = (select [dbo].[f_getDebtorDolg](@ContractID,3))
set @AllDebt = (select [dbo].[f_getDebtorDolg](@ContractID,2))
set @CurrentDebt = (select [dbo].[f_getDebtorDolg](@ContractID,1))
if (not EXISTS(select ContractID from tbl_Debtor db where db.ContractID = @ContractID))
or (EXISTS(select ContractID
from tbl_Debtor db1
left join
tbl_DebtorState as ds on ds.ID = db1.StateID
where db1.ContractID = @ContractID
and ds.Code = 'Close'
and db1.DebtDate = (select MAX(ISNULL(DebtDate,GETDATE())) from tbl_Debtor db3
where @ContractID = db3.ContractID)))
begin
if not EXISTS(select
SUM(ISNULL(i.PaymentAmount,0)) - SUM(ISNULL(i.Amount,0))
from tbl_Invoice i
left join
tbl_Contract as c on c.ID = i.ContractID
where not ContractID is null
and c.ContractTypeID = 'ED7FD7B2-46A7-4583-8FB4-37356272EB8A'
and contractID = @ContractID
group by ContractID
HAVING SUM(ISNULL(i.PaymentAmount,0)) - SUM(ISNULL(i.Amount,0)) < 0 )
begin
return
end
insert into tbl_Debtor
(ID,CreatedOn,ContractID,DebtDate,AccountID,DebtorNumber,StateID,ActionID,
DelayDebt, AllDebt, CurrentDebt,IsOff)
Values(
newID(),
GETDATE(),
@ContractID,
GETDATE()
,(select CustomerID from tbl_Contract where ID = @ContractID)
,Cast(Month(GetDate())as varchar(5))+substring(Cast(YEAR(GetDate())as varchar(5)),3,5)+
(select ContractNumber from tbl_Contract where ID = @ContractID)
,(select ID from tbl_DebtorState where Code = 'New')
,(select ID from tbl_DebtorAction where IsStart = 1)
,@DelayDebt
,@AllDebt
,@CurrentDebt
,0)
-----exec [dbo].[sp_SetAccessDebtor] @ContractID
end
else
begin
update tbl_Debtor
set CurrentDebt = @CurrentDebt,
AllDebt = @AllDebt,
DelayDebt = @DelayDebt
where tbl_Debtor.ContractID = @ContractID
and
tbl_Debtor.DebtDate = (select MAX(ISNULL(db3.DebtDate,GETDATE())) from tbl_Debtor db3
where @ContractID = db3.ContractID)
end
END
[/sql]
Попытался воспроизвести проблемму, создав sp_CreateDebtor , и использовать триггер в 1м посте, создал импорт с Excel, проблемму вопспроизвести не удалось импорт делался на 1000 записей. Сколько записей вы импортируете, возможно проблему обойти импортируя меньшее колиечство записей ?
[sql]
Create PROCEDURE [dbo].[sp_CreateDebtor]
(@ContractID uniqueidentifier)
AS
BEGIN
SET NOCOUNT ON;
declare @DelayDebt decimal(15,4)
declare @AllDebt decimal(15,4)
declare @CurrentDebt decimal(15,4)
begin
IF NOT EXISTS(SELECT
SUM(ISNULL(i.PaymentAmount,0)) - SUM(ISNULL(i.Amount,0))
FROM tbl_Invoice i
LEFT JOIN
tbl_Contract AS c ON c.ID = i.ContractID
WHERE NOT ContractID IS NULL
AND c.ContractTypeID = 'ED7FD7B2-46A7-4583-8FB4-37356272EB8A'
AND contractID = @ContractID
GROUP BY ContractID
HAVING SUM(ISNULL(i.PaymentAmount,0)) - SUM(ISNULL(i.Amount,0)) < 0 )
begin
RETURN
end
end
END
[/sql]
Импортируется порядка 400 строк но помимо данных еще идет подгрузка файлов. И в вашем посте в процедуре отсутствует Update и Inserte таблицы. Мне кажется что проблема кроется в них.
Возможно ли передача backup’а базы данных нам, для того что бы могли помочь вам в решении поставленной перед вами задачу?
К сожалению возможности передать дамп базы не представляется возможным.
Иван,
Предлагаю вам последовать рекомендациям в статье
http://www.rsdn.ru/article/db/deadlocks.xml#ECMAE
Возможно установление некой задержки
[sql]
WAITFOR DELAY '00:00:10'
[/sql]
и установка различных уровней изоляции помогут решить проблему
Спасибо) Я в данный момент склоняюсь к варианту отказа от триггеров и создания job который раз в час например пересчитывает задолженность и обновляет записи.