人人為我 我為人人

紀錄一個最近解決的 query blocked 問題

某天開始突然一直收到 job step blocking message

查看了一下該 job 是把資料搬入歷史區，所異動的資料量頗多耗時長也不會再被變更

e.g. blocking query text

DECLARE @CopyDate datetime;
SET @CopyDate = dateadd(hour, - 4, GETUTCDATE());
INSERT INTO [History] (
	[Id]
	,[Name]
	,[Age]
	,[Phone]
	,[Address]
	,[DT]
	)
SELECT i.[Id]
	,p.[Name]
	,p.[Age]
	,i.[Phone]
	,i.[Address]
	,GETUTCDATE()
FROM [People] p
JOIN [Information] i 
ON i.[Id] = p.[MyId]
WHERE i.[DateTime] < @CopyDate

e.g. blocked query text

BEGIN TRAN
INSERT [dbo].[People] ([MyId] ,[Name] ,[Age] ,[Birthday] ,[Gender]) VALUES (@0 ,@1 ,@2 ,@3 ,@4 ,@5)
SELECT [MyId] FROM [dbo].[People] WHERE @@ROWCOUNT > 0 AND [MyId] = scope_identity()

透過模擬資料並查詢 sysprocesses 獲得 LCK_M_S 鎖

SELECT * FROM sysprocesses WHERE blocked <> 0 and spid > 50

從結果中發現 spid 74 (blocking) 使用 LCK_M_S 造成 spid 59 and 75 (blocked) 被鎖

為了避免資料搬動過程遭遇或產生 blocks 所以使用 WITH(NOLOCK) 允許 dirty read

e.g. after blocking query text

DECLARE @CopyDate datetime;
SET @CopyDate = dateadd(hour, - 4, GETUTCDATE());
INSERT INTO [History] (
	[Id]
	,[Name]
	,[Age]
	,[Phone]
	,[Address]
	,[DT]
	)
SELECT i.[Id]
	,p.[Name]
	,p.[Age]
	,i.[Phone]
	,i.[Address]
	,GETUTCDATE()
FROM [People] p WITH(NOLOCK)
JOIN [Information] i WITH(NOLOCK)
ON i.[Id] = p.[MyId]
WHERE i.[DateTime] < @CopyDate

e.g. after blocked query text

BEGIN TRAN
INSERT [dbo].[People] ([MyId] ,[Name] ,[Age] ,[Birthday] ,[Gender]) VALUES (@0 ,@1 ,@2 ,@3 ,@4 ,@5)
SELECT [MyId] FROM [dbo].[People] WITH(NOLOCK) WHERE @@ROWCOUNT > 0 AND [MyId] = scope_identity()

接下來的幾次模擬中就再也沒有發生 LCK_M_S 問題了

而 PAGELATCH 是 SQL Server 為了解決插入資料時物理層的衝突

當要修改 page 時 就必需先申請並取得一個 EX 的 latch 才能修改 page 內容
(PAGELATCH_EX = 獨佔, PAGELATCH_SH = 共用)

也因為資料頁修改都是在記憶體中完成 所以耗時短暫可被忽略

官方也有詳細的說明完整的鎖定相容性
https://technet.microsoft.com/en-us/library/ms186396(v=sql.105).aspx

have fun ʕ•͡ᴥ•ʔ