Insert-update trigger blocking

  • I am running into an intermittent inexplicable issue. This happens on a high end SQL server, like 64 cores with 256GB of RAM. The database is just about 100GB. Needless to say resource isn't an issue here

    The condition is that an insert SPID will enter sleeping awaiting command state, escalate quickly to blocking leader and stay put blocking all SPIDs reading this table via a join. The blocking will grow until the insert SPID is killed, which releases all SPIDs instantaneously. Obviously, killing the SPID is undesired and 3rd party vendor doesn't want to move trigger code into the application and disable it

    Application is a ASP.NET application. It open a transaction to send in an insert and transaction is open while entering sleeping state. There could be other aspects of the application reading the table but not necessarily the same record. Until the original insert/update transactions is committed or rolled back, rest of the SPIDs will remain stuck. This explains the blocking. I am trying to figure out why it entered the sleep state

    The table has an insert/update trigger. This trigger records last activity timestamp. The only problem is it uses the PK and the clustered index for the insert/update command and used by trigger to update. I suspect it is the trigger and I suspect it has to do with SQL internals. I am seeing quite a few tables with the same logic and they all encounter the same state until killed

    Any help/guidance is much appreciated

    example:

    CREATE TABLE [Table1]

    (

    [Id] [VARCHAR](30) NOT NULL,

    [Name] [VARCHAR](100) NULL,

    [last_change_dt] [DATETIME] NULL,

    CONSTRAINT [pk_Table1_1] PRIMARY KEY CLUSTERED

    (

    [Id] ASC

    )

    );

    CREATE TRIGGER [table1_ins_upd]

    ON [Table1] FOR INSERT, UPDATE NOT FOR REPLICATION

    AS

    DECLARE @num_rows INT

    DECLARE @system_timestamp DATETIME

    SELECT @num_rows=@@ROWCOUNT

    IF @num_rows=0

    RETURN

    SELECT @system_timestamp = GETDATE()

    UPDATE Table1

    SET last_change_date = @system_timestamp

    FROM Table1, inserted

    WHERE Table1.Id = inserted.Id

    RETURN

  • I'm not sure about this, but I'm wondering if this sets up an infinite loop of the trigger firing the trigger. If you changed the trigger to INSTEAD OF instead of AFTER (the default you're getting by specifying FOR), you'd need to change the code slightly, but I think you could be sure that the trigger won't end up calling itself because of the following from Books Online:

    If an INSTEAD OF trigger defined on a table executes a statement against the table

    that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called

    recursively. Instead, the statement is processed as if the table had no INSTEAD OF

    trigger and starts the chain of constraint operations and AFTER trigger executions.

    For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and

    the trigger executes an INSERT statement on the same table, the INSERT statement

    executed by the INSTEAD OF trigger does not call the trigger again. The INSERT

    executed by the trigger starts the process of performing constraint actions and firing

    any AFTER INSERT triggers defined for the table.

    See also this part about Recursive Triggers:

    SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS

    setting is enabled using ALTER DATABASE.

    Recursive triggers enable the following types of recursion to occur:

    Indirect recursion

    With indirect recursion, an application updates table T1. This fires trigger TR1,

    updating table T2. In this scenario, trigger T2 then fires and updates table T1.

    Direct recursion

    With direct recursion, the application updates table T1. This fires trigger TR1,

    updating table T1. Because table T1 was updated, trigger TR1 fires again, and

    so on.

    The following example uses both indirect and direct trigger recursion.

    Assume that two update triggers, TR1 and TR2, are defined on table T1.

    Trigger TR1 updates table T1 recursively. An UPDATE statement executes

    each TR1 and TR2 one time. Additionally, the execution of TR1 triggers the

    execution of TR1 (recursively) and TR2. The inserted and deleted tables for a

    specific trigger contain rows that correspond only to the UPDATE statement

    that invoked the trigger.

    Note:

    The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is

    enabled by using ALTER DATABASE. There is no defined order in which multiple

    triggers defined for a specific event are executed. Each trigger should be

    self-contained.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thanks for your response. We did think about possible recursion but the "Recursive Triggers Enabled" option is set to False. Will give INSTEAD OF a spin

    Also, this is intermittent not consistent. Recursion would have kicked in everytime. Anything else that could be looked at

  • An "instead of" trigger seems like the best solution. It is pretty simple for your example here:

    create trigger table1_ins_upd

    on table1 instead of insert, update

    as

    begin

    declare @num_rows int;

    declare @system_timestamp datetime;

    set @num_rows = @@ROWCOUNT;

    if @num_rows = 0

    return;

    set @system_timestamp = getdate();

    -- insert any records that don't have matching "deleted" rows

    insert into table1 (id, name, last_change_dt)

    select id, name, @system_timestamp

    from inserted ii

    where not exists (select 1 from deleted dd where dd.id = ii.id);

    -- update any records in both "inserted" and "deleted"

    update table1

    set name = ii.name, last_change_dt = @system_timestamp

    from inserted ii

    join deleted dd on dd.id = ii.id

    where table1.id = ii.id;

    end;

    The primary advantage here is that the initial write to the table is delayed until the "last_change_dt" has the desired value, so you are avoiding the initial write followed by a second write when the trigger fires.

    Note however, that you need to handle Inserts and "Updates" slightly differently, since an Update to a column other than the PK will cause a duplicate key error if you just try to use the "inserted" table.

    On a true Insert, the "inserted" table will hold the new row and there will be no corresponding row in the "deleted" table. On an Update, the "deleted" table holds the old (pre-deletion) row, so you can join on the primary keys between "inserted" and "deleted" to properly handle each type.

    Just to make sure you aren't counting on all the "last_change_dt" being the same for all records in a transaction, if you try this with some multi-record transactions, you will find that the timestamp values will vary across the records. On my test server, running batches of 1000 records showed measurable (4-10 millisecond) time differences every 21 records (using Datetime, which is fairly low resolution).

  • Thank you. I will give this a spin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply