7

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?

9

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 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.

4
  • Thank you Paul, but what about inserted/deleted in trigger that can differ from what we have in the table right now? Does it mean that in case of non-trivial plan we potentially can have "lost update"?
    – sepupic
    Dec 16 '19 at 13:40
  • 1
    @sepupic Instead of triggers definitely complicate thinking about isolation levels, but it's really a timing difference and remember the instead of trigger can do anything it likes instead of the original operation. If the concerns are valid in your real scenario, you could add an UPDLOCK hint manually or use a higher isolation level. Under read committed, SQL Server need not take any long term locks when reading. The U locks you often see are for conversion deadlock avoidance and are scoped to the statement not the txn. Long separate discussion.
    – Paul White
    Dec 16 '19 at 13:46
  • Does this mean that in every update from join one should always add a hint to updating table as in some second moment this table can get instead of update trigger on it? Isn't it a bug since it's not documented that inserted/deleted guarantee no consistency with what was in update itself, i.e. one cannot rely on these tables within instead of update trigger? There is no word about it here: docs.microsoft.com/en-us/sql/relational-databases/triggers/… under "Using the inserted and deleted Tables in INSTEAD OF Triggers"
    – sepupic
    Dec 17 '19 at 7:53
  • 1
    @sepupic If that's the guarantee you want then yes you might need a hint or higher isolation level. I can't really understand the rest of your comment, but I would say it is the responsibility of the person adding the trigger to ensure everything works. Anyway, this is all a tangent from your original question, which I have answered. You can ask follow-up questions of course.
    – Paul White
    Dec 17 '19 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.