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
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
            insert into [user].SessionWebServerLog
                 ( SessionId, WebServerProcessInstanceId, IsSSL )
                 ( @pSessionId, @pWebServerProcessInstanceId, @pIsSsl );                

end try
begin catch    
   if (xact_state() = -1 or @@trancount > 0)
   -- 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?


    @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


        @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:

When no matching rows to update

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

enter image description here

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


Q1: The range lock is likely held becauase SessionId,IsSSL,LastRequestUtc is not declared unique. Is this correct? This means that the value before and after the one you are looking for has to be locked (as you are requesting an XLOCK on the table) to avoid the range being modified while you read it. If you were to declare the combination unique, I believe this problem should be gone. This can also cause the INSERT to lock up.

Q2: First of all, a clarification. Page lock are NOT the default, row lock are (Page locks were default in SQL Server 7.0). That being said, SQL server will still take out an intent Exclusive (IX) lock on the page. This is not a problem, because this lock is compatible with other IX locks. You can actually make locking cheaper by using ALLOW_PAGE_LOCKS = OFF on your indexes (this has side effects if you do frequent table scans, so be careful with this).

Q3: You dont have to force rowlock or to force the xlock in the CROSS APPLY. You MAY need to force serializable if you expect multiple statement to insert into the same session and ranges. If you dont, then you can get rid of that too (for example, if every inserter only inserts for the same session).

  • Q1: After creating a separate unique constraint on SessionId,IsSsl,LastRequestUtc, a RangeLock was still granted and blocking still occurred on the update statement between two windows. I removed that attempt, and tried adjusting the original PK to be SessionId,IsSsl,LastRequestUtc and allow_page_locks = false. Same result (RangeX-X and blocked). I may try adding a bit field to flag the last request.
    – crokusek
    Jan 10 '14 at 18:52
  • Q2: The page lock default I was referring to in the question was indeed the "allow page locks" on the index so we are on the same track. Thanks for the table scan note.
    – crokusek
    Jan 10 '14 at 18:54
  • Q3: Using the new PK as you suggest in Q1 and adding in the Q3 suggestion (removing the rowlock and xlock hints from within the cross apply), then the blocking goes away on different non existing inputs and the lock shows "RangeS-S". However the blocking also goes away when the inputs are the same to both windows so an extra record would be inserted where it shouldn't so I conclude that the xlock or something else is required. In reality, the LastRequestUtc could be repeated for the same session I wouldn't be able to really deploy the Q1 suggestion.
    – crokusek
    Jan 10 '14 at 18:57
  • If LastRequestUtc cannot be used for the key, what exactly is the semantics you desire for this query? That means that there might be TWO different "previous values" and hence, the effect of the CROSS APPLY is now undefined Jan 10 '14 at 20:40
  • By the way, try just adding Serializable hint to the CROSS APPLY (not the xlock) Jan 10 '14 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.