SQL Server – Can Trigger with Insert Cause Intext Exclusive Locks?

insertsql serversql-server-2017trigger

We have a trigger that runs AFTER an insert in a table (Nodes) that creates entries in another table (NodeClosures) that selects entries (6 on average) from the NodeClosures table and inserts them as new rows with a different value in a certain column. The insert operations in the Nodes table occurs one at a time (so no single large batch insert operations, Entity Framework) and the select/insert operation into the NodeClosures table is causing an intent exclusive lock. I'm uncertain if this is due to lock escalation (not sure how as neither insert operations are inserting more than 6 rows). Some additional information, Read Committed Snapshot Isolation is being used as well as Delayed Durability Transactions.
I have included the full trigger for reference (MSSQL 2017):

CREATE TRIGGER [tr_dbNode_insert]
ON [dbo].[DbNodes]
After INSERT
AS
BEGIN
    -- Insert self closure
    insert into DbNodeClosures (ParentID, ChildID, Depth, AccountID)
    select Id, Id, 0, AccountID
    from inserted;
    -- Insert parent's closures as it's own + 1
    insert into DbNodeClosures (ParentID, ChildID, Depth, AccountID)
    select closures.ParentID, inserted.Id, closures.Depth + 1, inserted.AccountID
    from inserted join DbNodeClosures as closures on inserted.ParentID = closures.ChildID;

END

Best Answer

The short answer to your question is - Yes, any insert, even a single row, can cause an Intent-Exclusive lock, but on the higher levels in the lock hierarchy than the actual Exclusive lock that was taken.

The Intent-Exclusive lock indicates that a request has taken an Exclusive lock at a lower level in the Lock Hierarchy (Database (High) -> Table -> Page -> Row (Low)). For example, if you're seeing the IX lock at the table level, it indicates a request has an X lock at the page or row level. If you see the IX lock at the page level, then there is an X lock at the row level. Note that in lock escalation, Row doesn't escalate to Page, instead both Row and Page locks escalate to Table locks.

The Intent Exclusive lock isn't indicating lock escalation per se, it is there to control locks on higher-level resources to ensure the isolation level of all transactions is respected. If your trigger is updating 6 rows then it will most likely only have row or page level Exclusive locks, and the table-level will show an Intent Exclusive lock. This affects other sessions trying to take locks on higher-level resources by indicating lower-level resources are already locked.

Check out this article for a good explanation of the different locks and their compatibility with one another.

Related Question