开发者

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 :)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜