Sql-server – How to use proper hints for Upsert involving a top 1 subquery

For this specific case (tracking Load Balancer switches) we wish to optimize an Upsert so that

• it does not exhibit any race conditions,
• cause any PK violation, or
• acquire any over sized locks.

I understand larger locks (page) may be more efficient but for purposes of question the goal is minimal (row). There are numerous links on the upsert/lock subject but the answers are are somewhat inconsistent (esp. updlock, and multi-statements) and this particular case involves an embedded sub query.

Table definition:

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

The SP should insert only if the Session+IsSsl combination has changed server ids since the most recent request for that Session+IsSsl:

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

This routine appears to work for simple cases by testing using two windows and executing the first half of the transaction within each window and checking blocking.

Q1: It blocks when the update does not match any rows for either window but yet they are different keys. Does blocking occur because the key range lock is held on existing keys only?

Win1:

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

Win2:

 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

declare
@pSessionId bigint = 4,
@pWebServerProcessInstanceId bigint = 100,
@pIsSsl bit = 0;

Q2: If I allow page locks (the default) on the PK, why is a page lock is taken out even though the row lock hint was specified.

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

The transaction isolation level is the default "read committed." I chose not to alter for this specific because restoring it seems messier (for success and failure and assuming/determining default) than just using table locks (imo).

Query plan for the zero case:

Query plan when multiple rows for WebSession+Ssl with different dates exist (exactly one row from branch to top, perfect, apparently using the PK by date):

Q3: Is this overkill–are there other hints that will accomplish the goals? (Please do not re-arrange query or try to convert to merge statement for purposes of this question).