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?
TopAnswers
Databases
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