Sql-server – Sometimes the updated rows are not locked within instead of update trigger

lockingsql servert-sqltrigger

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?

Best Answer

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.

Related Question