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?
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] 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. : https://sqlperformance.com/2015/09/sql-plan/instead-of-triggers