SQL blocking with replication and triggers
I have a table that is replicated from Server A to Server B using transactional replication. I also have a INSERT & UPDATE trigger on the table on Server B. When the SQL Server Agent is replicating data, there are blocks, caused by the trigger.
Below are my triggers.
USE [STOREMAIN]
GO
/****** Object: Trigger [dbo].[UPD_tblReceivingHeaderStatus] Script Date: 08/16/2011 13:28:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UPD_tblReceivingHeaderStatus]
ON [dbo].[tblReceivingHeader]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @SeqNum numeric ,@Location numeric
--get the seqnum & location from the inserted record
select @SeqNum = i.SeqNum, @Location = i.Location
from tblReceivingHeaderStatus D
left join inserted i on D.Location = i.Location and D.SeqNum = i.SeqNum
UPDATE tblReceivingHeaderStatus
SET
AdjTax = inserted.AdjTax,
AdjDeliveryFee = inserted.AdjDeliveryFee,
AdjDiscount = inserted.AdjDiscount,
AdjInvoiceTotal = inserted.AdjInvoiceTotal,
AdjItemCount= inserted.AdjItemCount,
AdjInvoiceInfo = inserted.AdjInvoiceInfo,
InvoiceAdjReason = ISNULL(inserted.InvoiceAdjReason,''),
PaidFlag = inserted.PaidFlag,
StartDate = inserted.StartDate,
CheckComments = inserted.CheckComments,
POeMailSent = case inserted.CheckComments
when '.' then 'P'
else ''
end,
PONumber = inserted.PONumber,
[Status] = inserted.[Status],
MiscFlag2 = 'T'
FROM
inserted
WHERE
inserted.seqnum = tblReceivingHeaderStatus.seqnum AND
inserted.location = tblReceivingHeaderStatus.location
;
--this assigns all inventory PO receivers to someone in pricing to approve
update tblReceivingHeaderStatus
set NextApprover = 1
from tblReceivingHeaderStatus
left join apvendp on vmvend = vendornum
where
recdevice = 'P' and
status = '1' and
NextApprover <> 1 and
vminex = 'I'
;
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
--------------------------------
USE [STOREMAIN]
GO
/****** Object: Trigger [dbo].[INS_INTO_tblReceivingHeaderStatus] Script Date: 08/16/2011 13:28:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[INS_INTO_tblReceivingHeaderStatus]
ON [dbo].[tblReceivingHeader]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT stat开发者_高级运维ements.
SET NOCOUNT ON;
declare @SeqNum numeric ,@Location numeric
--get the seqnum & location from the inserted record
select @SeqNum = i.SeqNum, @Location = i.Location
from tblReceivingHeaderStatus D
left join inserted i on D.Location = i.Location and D.SeqNum = i.SeqNum;
INSERT INTO storemain..tblReceivingHeaderStatus
( SeqNum, VendorNum, InvoiceNum, InvoiceTotal, ItemCount, InvoiceDate, Status, Location, AdjTax, AdjDeliveryFee, AdjDiscount, AdjInvoiceTotal,
AdjItemCount, AdjInvoiceInfo, Tax, DeliveryFee, Discount, ApprovedTime, ApprovedDate, ApprovedBy, InvoiceAdjReason, SentTo400, TimeDateSent, PaidFlag,
StartDate, CheckComments, DrayEnteredBy, NextApprover, PONumber, recDevice, SalesTaxFlag, FreightFlag, MiscFlag1, MiscFlag2, MiscFlag3, MiscChar1,
MiscChar2, MiscChar3, MiscNumber1, M
Maybe implementing dirty reads in your query might help?
select id, description from sometable (nolock)
saying that, im not 100% of the impact of dirty reads in the sort of environment you are in, so it might be worth investigating further.
The tables could be locking depending on the server load required when copying data from one server to another, as intensive selects can (i believe) cause locks if there are large enough queries being run. dirty reads mitigate this problem somewhat, however can have consequences in that you can get stale data. Could it be worth trying a different form of replication?
Anyone/everyone, please correct me if im wrong and barking up the wrong tree :)
精彩评论