如何为涉及前 1 个子查询的 Upsert 使用正确的提示

对于这种特定情况（跟踪负载均衡器开关），我们希望优化更新插入，以便

• 它没有表现出任何竞争条件，
• 导致任何 PK 违规，或
• 获得任何超大锁。

我知道更大的锁（页面）可能更有效，但出于质疑的目的，目标是最小的（行）。关于 upsert/lock 主题有很多链接，但答案有些不一致（尤其是 updlock 和multi-statements），这个特殊情况涉及嵌入式子查询。

表定义：

create table [User].[SessionWebServerLog] (
  [SessionId] bigint not null,
  [IsSSL] bit not null default ((0)),
  [LastRequestUtc] datetime2(7) not null default (sysutcdatetime()),
  [WebServerProcessInstanceId] bigint not null,
  [RequestCount] int not null default ((1)),
  [FirstRequestUtc] datetime2(7) not null default (sysutcdatetime()),
  foreign key ([SessionId]) references [User].[Session] ( [SessionId] ) on delete cascade,
  primary key clustered ([SessionId] asc, [IsSSL] asc, [LastRequestUtc] desc, [WebServerProcessInstanceId] asc)
  with ( 
    allow_row_locks = on,
    allow_page_locks = off,  -- Needed else page locks were taken
  )
)

仅当 Session+IsSsl 组合自最近一次请求 Session+IsSsl 以来更改了服务器 ID 时，SP 才应插入：

create proc [User].[usp_LogSessionWebServerRequest]    
    @pSessionId                   bigint, 
    @pWebServerProcessInstanceId  bigint,    
    @pIsSsl                       bit,        -- True for https, false for http
    @pDebug                       bit = 0     -- debug flag for print statements
as    
begin try        
    set xact_abort on;
    begin transaction;

        update l
           set RequestCount = RequestCount + 1,
               LastRequestUtc = sysutcdatetime()
          from [User].SessionWebServerLog l             
          with (rowlock, xlock, serializable) -- row level, exclusively held, until end of xact
         cross apply
             (
               select top(1) WebServerProcessInstanceId, LastRequestUtc
                 from [User].SessionWebServerLog 
                 with (rowlock, xlock, serializable) -- row level, exclusively held, until end of xact
                   -- PK supports this join:  SessionId, IsSsl, LastRequestUtc (desc), WebServerProcessId                       
                where SessionId = @pSessionId
                  and IsSSL = @pIsSsl
                order by LastRequestUtc desc 
             ) prev -- previous request
         where SessionId = @pSessionId
           and IsSSL = @pIsSsl
           and prev.WebServerProcessInstanceId = @pWebServerProcessInstanceId
           and l.WebServerProcessInstanceId = @pWebServerProcessInstanceId
           and l.LastRequestUtc = prev.LastRequestUtc;

        if (@@rowcount = 0) -- if no update occurred, insert new
        begin
            insert into [user].SessionWebServerLog
                 ( SessionId, WebServerProcessInstanceId, IsSSL )
            values 
                 ( @pSessionId, @pWebServerProcessInstanceId, @pIsSsl );                
        end            

    commit;            
end try
begin catch    
   if (xact_state() = -1 or @@trancount > 0)
    rollback;
   -- log, etc.
end catch

通过使用两个窗口进行测试并在每个窗口内执行事务的前半部分并检查阻塞，此例程似乎适用于简单的情况。

Q1：当更新与任一窗口的任何行都不匹配但它们是不同的键时，它会阻塞。是否因为键范围锁仅保留在现有键上而发生阻塞？

赢1：

declare
    @pSessionId                   bigint = 3, -- does not exist in table
    @pWebServerProcessInstanceId  bigint = 100,    
    @pIsSsl                       bit = 0;

 sp_lock 72:

spid  dbid  ObjId      IndId   Type       Resource  Mode      Status
  72    16      0          0     DB                 S          GRANT 
  72    16  388964512      1    KEY (6c2787a590a2)  RangeX-X   GRANT
  72    16  388964512      0    TAB                 IX         GRANT

赢2：

 declare
        @pSessionId                   bigint = 4,  -- does not exist in table
        @pWebServerProcessInstanceId  bigint = 100,    
        @pIsSsl                       bit = 0;

    sp_lock 92:

    spid  dbid      ObjId   IndId   Type  Resource         Mode   Status
    92      16          0       0     DB                    S      GRANT
    92      16  388964512       1    KEY  (6c2787a590a2) RangeX-X   WAIT
    92      16  388964512       0    TAB                    IX     GRANT

声明@pSessionId bigint = 4，@pWebServerProcessInstanceId bigint = 100，
@pIsSsl bit = 0；

Q2：如果我在 PK 上允许页锁（默认），为什么即使指定了行锁提示，页锁也会被取消？

spid  dbid      ObjId   IndId   Type     Resource      Mode      Status
72      16          0       0      DB                    S       GRANT
72      16  388964512       1     PAG       1:444       IX       GRANT
72      16  388964512       1     KEY (6c2787a590a2)  RangeX-X   GRANT
72      16  388964512       0     TAB                   IX       GRANT

事务隔离级别是默认的“read committed”。我选择不针对此特定内容进行更改，因为与仅使用表锁 (imo) 相比，恢复它似乎更混乱（对于成功和失败以及假设/确定默认值）。

零案例的查询计划：

当 WebSession+Ssl 存在多行不同日期时的查询计划（从分支到顶部正好一行，完美，显然使用日期 PK）：

Q3：这是否矫枉过正——是否有其他提示可以实现目标？（请不要为了这个问题的目的重新安排查询或尝试转换为合并语句）。