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