sql-server add tag
sepupic (imported from SE)
Here is as small repro:

    create table dbo.t (id int primary key, v int);
    insert into dbo.t values (1, 1), (2, 2);
    
    create table dbo.s (id int primary key, v int);
    insert into dbo.s values (1, 10);
    go
    
    create trigger dbo.tr_t__iou
    on dbo.t
    instead of update
    as
    begin
     set nocount on;
    
     exec sp_lock @@spid;
    end;
    go
    
    update dbo.t set v = 10 where id = 1;
    
    update t
     set
      v = 10
    from
     dbo.s s join
     dbo.t t on t.id = s.id;
    
    update t
     set
      v = 10
    from
     (values (1, 10)) s(id, v) join
     dbo.t t on t.id = s.id;
    go
    
    drop table dbo.t, dbo.s;
    go

`sp_lock` within the `trigger` reports U-`key lock` on the affected row in the first and the last cases, but in the second case there is no `lock` at all, how can it be explained?
Top Answer
Paul White (imported from SE)
When the update statement qualifies for a trivial plan, the optimizer rule that expands the instead-of trigger part of the statement (`ExpandInsteadOfTriggerUpd`) includes the part of the plan that reads from the base table. This rewrite includes adding an `UPDLOCK` hint to the base read. As usual, the `UPDLOCK` hint means that update locks are taken and held to the end of the transaction.

When the statement does not qualify for a trivial plan, the `ExpandInsteadOfTriggerUpd` rule only [rewrites the write-cursor portion][1] of the plan, leaving the base table reads untouched - no `UPDLOCK` hint is added.

My guess is that this trivial plan behaviour exists to avoid a deadlock scenario.


  [1]: https://sqlperformance.com/2015/09/sql-plan/instead-of-triggers

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.