如何對涉及前 1 個子查詢的 Upsert 使用適當的提示
對於這種特定情況（跟踪負載均衡器開關），我們希望優化 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 位 = 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事務隔離級別是預設的“已送出讀”。我選擇不針對這個具體進行更改，因為恢復它似乎比僅使用表鎖 (imo) 更混亂（對於成功和失敗以及假設/確定預設值）。
零情況的查詢計劃：
當存在多行不同日期的 WebSession+Ssl 時的查詢計劃（從分支到頂部正好只有一行，完美，顯然使用的是按日期的 PK）：
**Q3：**這是不是矯枉過正——還有其他可以實現目標的提示嗎？（出於此問題的目的，請不要重新安排查詢或嘗試轉換為合併語句）。
**Q1：**範圍鎖定可能
SessionId,IsSSL,LastRequestUtc是因為沒有聲明為唯一的。它是否正確？這意味著必須鎖定您要查找的值之前和之後的值（因為您在表上請求 XLOCK）以避免在您讀取它時修改範圍。如果您要聲明該組合是唯一的，我相信這個問題應該消失了。這也可能導致 INSERT 鎖定。**Q2：**首先，澄清一下。頁鎖不是預設設置，行鎖是（頁鎖在 SQL Server 7.0 中是預設設置）。話雖如此，SQL Server 仍會在頁面上取出意向排他 (IX) 鎖。這不是問題，因為此鎖與其他 IX 鎖兼容。實際上，您可以通過
ALLOW_PAGE_LOCKS = OFF在索引上使用更便宜的鎖定（如果您進行頻繁的表掃描，這會產生副作用，所以要小心這一點）。Q3：您不必在 CROSS APPLY 中強制 rowlock 或強制 xlock。如果您希望將多個語句插入到相同的會話和範圍中，您可能需要強制可序列化。如果你不這樣做，那麼你也可以擺脫它（例如，如果每個插入器只插入同一個會話）。