SQL Serer閂鎖 和 閂鎖超時(shí)故障排除

SQL Serer閂鎖 和 閂鎖超時(shí)故障排除

翻譯自：https://mssqlwiki.com/2012/09/07/latch-timeout-and-sql-server-latch/

在一個(gè)多線程的進(jìn)程里，當(dāng)一個(gè)線程在內(nèi)存里更新一個(gè)數(shù)據(jù)或索引頁，而另一個(gè)線程正在讀取相同的頁，將會(huì)發(fā)生什么？

當(dāng)?shù)谝粋€(gè)線程在內(nèi)存里讀取一個(gè)數(shù)據(jù)或索引頁，而第二個(gè)線程正在從內(nèi)存里釋放相同的頁，將會(huì)發(fā)生什么？

答案是：我們將獲得數(shù)據(jù)或數(shù)據(jù)結(jié)構(gòu)不一致的結(jié)果。為了避免不一致，SQL Server使用同步機(jī)制像鎖（Locks）、閂鎖（Latches）和自旋鎖（Spinlocks）。

在這篇博文里，我們將討論關(guān)于閂鎖的一些關(guān)鍵點(diǎn)和如何排除閂鎖超時(shí)dump故障。

什么是閂鎖（Latch）？

它們通過多線程控制對(duì)數(shù)據(jù)頁和結(jié)構(gòu)的并發(fā)訪問。閂鎖提供數(shù)據(jù)頁的物理數(shù)據(jù)一致性，并提供數(shù)據(jù)結(jié)果的同步。閂鎖不可以像鎖一樣被用戶控制。

閂鎖的類型：

Buffer(BUF) Latch
用于同步訪問BUF結(jié)構(gòu)和它們相關(guān)的數(shù)據(jù)庫頁。

Buffer "IO" Latch
Buffer Latch的一個(gè)子集，用于當(dāng)BUF和相關(guān)的數(shù)據(jù)/索引頁正在一個(gè)IO操作（從磁盤讀取頁或者寫入頁到磁盤）中間時(shí)。

Non-Buffer(Non-BUF) Latch
這些閂鎖被用于同步一般的內(nèi)存中數(shù)據(jù)結(jié)構(gòu)，這些結(jié)構(gòu)通常被并行線程、自動(dòng)增長操作和收縮操作等查詢/任務(wù)執(zhí)行所使用。

閂鎖模式：

Keep(KP) Latches
用于確保當(dāng)頁面正在使用時(shí)，不會(huì)從內(nèi)存釋放。

Shared(SH) Latches
用于對(duì)數(shù)據(jù)結(jié)構(gòu)的只讀訪問，和阻止其他線程的寫訪問。
這個(gè)模式允許共享訪問。
SH可兼容于KP、SH和UP。應(yīng)該注意的是，盡管通常SH表明了只讀訪問，但不總是這樣。對(duì)于Buffer Latches，SH是為了讀取一個(gè)數(shù)據(jù)頁的最小模式要求。

Update(UP) Latches
允許對(duì)數(shù)據(jù)結(jié)構(gòu)（兼容于SH和KP）的讀訪問，但是阻止其他EX-latch訪問。
當(dāng)頁分裂檢測關(guān)閉并且當(dāng)AWE沒有啟用時(shí)用于寫操作。

Exclusive(EX) Latches
阻止發(fā)生在被閂鎖結(jié)構(gòu)上的讀取活動(dòng)。EX只兼容于KP。
當(dāng)頁分裂檢測開啟或者AWE啟用時(shí)在讀IO和寫IO期間。

Destroy(DT) Latches
用于當(dāng)從Buffer Pool移除BUFS，要么通過添加它們到空閑列表，要么取消映射AWE Buffers。

閂鎖兼容性：

      KP     SH     UP     EX     DT
KP     Y     Y     Y     Y     N
SH     Y     Y     Y     N     N
UP     Y     Y     N     N     N
EX     Y     N     N     N     N
DT     N     N     N     N     N

如何識(shí)別閂鎖爭用？

閂鎖爭用可以通過在sysprocesses里的等待類型來識(shí)別。

PAGEIOLATCH_*：
這個(gè)sysprocesses里的等待類型表明SQL Server正在等待一個(gè)Buffer Pool頁的物理I/O完成。
    1.PAGEIOLATCH_*通常通過調(diào)優(yōu)查詢來解決，該查詢會(huì)執(zhí)行大量的IO操作（通常通過添加、修改和移除索引或統(tǒng)計(jì)信息來介紹物理IO數(shù)量）。
    2.識(shí)別是否有磁盤瓶頸并修復(fù)它們（PAGEIOLATCH等待時(shí)間（例如大于30ms））。

PAGELATCH_*：
這個(gè)sysprocesses里的等待類型表明SQL Server正在等待訪問一個(gè)數(shù)據(jù)庫頁，但是該頁沒有經(jīng)歷物理IO。
    1.這個(gè)問題通常由在同一時(shí)間試圖訪問相同物理頁的大量會(huì)話導(dǎo)致。你有應(yīng)該查看spid的等待資源。這個(gè)wait_resource是被訪問的頁號(hào)（格式是dbid:file:pageno）。
    2.我們可以使用DBCC PAGE來識(shí)別對(duì)象或者發(fā)生爭用的頁類型。它也幫助我們確定是否爭用是用于分配、數(shù)據(jù)或文本。
    3.如果SQL Server最頻繁等待的頁面在tempdb數(shù)據(jù)庫，在dbid為2對(duì)于一個(gè)頁號(hào)檢查等待資源列。你可能面臨著在這里提到的tempdb閂鎖爭用：http://support.microsoft.com/kb/328551
    4.如果頁在一個(gè)用戶數(shù)據(jù)庫，檢查是否表在一個(gè)單調(diào)鍵像標(biāo)識(shí)列上有一個(gè)聚集索引，所有的線程正在爭用表末尾的相同頁。在這種情況下，我們需要選擇一個(gè)不同的聚集索引鍵，將工作分散到不同的頁。

LATCH_*：
Non-buf閂鎖等待可以被各種事情導(dǎo)致。我們可以在sysprocesses里使用這個(gè)等待資源列來確定包含的閂鎖類型（KB 822101）。
    1.一個(gè)非常普通的LATCH_EX等待是由于運(yùn)行一個(gè)Profiler跟蹤或者sp_trace_getdata參考KB 929728獲取更多信息。
    2.自動(dòng)增長和自動(dòng)收縮。

當(dāng)一個(gè)閂鎖被線程請求，并且如果由于其他線程在相同的頁或數(shù)據(jù)結(jié)構(gòu)上持有一個(gè)不兼容的閂鎖，而這個(gè)閂鎖不能被立即授予，那么這個(gè)請求者必需等待閂鎖可被授予。如果等待間隔達(dá)到5分鐘（waittime 300），類似以下的一條警告信息在SQL Server錯(cuò)誤日志中輸出，并且所有線程的一個(gè)mini dump被捕獲。警告信息對(duì)buffer和non-buffer latches有所區(qū)別。

844: Time out occurred while waiting for buffer latch — type %d, bp %p, page %d:%d, stat %#x, database id: %d, allocation unit id: %I64d%ls, task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p.  Continuing to wait.
846: A time-out occurred while waiting for buffer latch — type %d, bp %p, page %d:%d, stat %#x, database id: %d, allocation unit Id: %I64d%ls, task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p. Not continuing to wait.
847: Timeout occurred while waiting for latch: class ‘%ls’, id %p, type %d, Task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p. Continuing to wait.

拆分以上警告

類型（type）：
當(dāng)前閂鎖獲取請求的閂鎖模式。這個(gè)一個(gè)使用如下匹配的numerical值：
0 – NL (not used); 1 – KP; 2 – SH; 3 – UP; 4 – EX; 5 – DT.

任務(wù)（task）：
我們嘗試得到閂鎖的任務(wù)。

等待時(shí)間（Waittime）：
等待閂鎖獲取請求的以秒為單位的總時(shí)間。

擁有的任務(wù)（owning task）：
可用的擁有閂鎖的任務(wù)地址。

bp(只有Buffer latches）：
與Buffer latch對(duì)應(yīng)的BUF結(jié)構(gòu)的地址。

page（只有Buffer latches）：
對(duì)于當(dāng)前包含在BUF結(jié)構(gòu)中的頁的頁ID。

database id（只有Buffer latches）：
對(duì)于在BUF里的頁的數(shù)據(jù)庫ID。

像排除SQL Server里的阻塞問題一樣，當(dāng)有一個(gè)閂鎖爭用或者超時(shí)dump，識(shí)別閂鎖的所有者并故障排除為什么閂鎖被該所有者長時(shí)間持有。

當(dāng)有閂鎖超時(shí)dump，你可以看到類似以下一個(gè)的警告信息。在dump是非常重要的用于找到閂鎖的所有者線程之前，警告錯(cuò)誤信息打印在SQL Server錯(cuò)誤日志里。

2012-01-18 00:52:03.16 spid69      A time-out occurred while waiting for buffer latch — type 4, bp 00000000ECFDAA00, page 1:6088, stat 0x4c1010f, database id: 4, allocation unit Id: 72057594043367424, task 0x0000000006E096D8 : 0, waittime 300, flags 0x19,
owning task 0x0000000006E08328. Not continuing to wait.
spid21s     **Dump thread – spid = 21, PSS = 0x0000000094622B60, EC = 0x0000000094622B70
spid21s     ***Stack Dump being sent to E:\Data\Disk1\MSSQL.1\MSSQL\LOG\SQLDump0009.txt
spid21s     * *******************************************************************************
spid21s     * BEGIN STACK DUMP:
spid21s     *   02/28/12 00:32:03 spid 21
spid21s     * Latch timeout
Timeout occurred while waiting for latch: class ‘ACCESS_METHODS_HOBT_COUNT’, id 00000002D8C32E70, type 2, Task 0x00000000008FCBC8 : 7, waittime 300, flags 0x1a, owning task 0x00000000050E1288. Continuing to wait.
Timeout occurred while waiting for latch: class ‘ACCESS_METHODS_HOBT_VIRTUAL_ROOT’, id 00000002D8C32E70, type 2, Task 0x00000000008FCBC8 : 7, waittime 300, flags 0x1a, owning task 0x00000000050E1288. Continuing to wait.

從以上錯(cuò)誤信息，我們可以很容易理解，我們正嘗試在數(shù)據(jù)庫ID為4，頁1:6088（第一個(gè)文件的6088頁）請求閂鎖，并且因?yàn)槿蝿?wù)0x0000000006E08328（在警告信息中擁有任務(wù)0x0000000006E08328）正在它上面持有一個(gè)閂鎖而超時(shí)。

注意：任務(wù)只是被線程執(zhí)行的一個(gè)工作請求。（就像system task、login task和ghost cleanup task等）。執(zhí)行這個(gè)任務(wù)的線程將按需持有需要的閂鎖。

讓我們看看如何分析閂鎖超時(shí)dump和使用擁有任務(wù)0x0000000006E08328獲取閂鎖的擁有線程。

去分析dump，從這里http://sdrv.ms/MO6ytG下載和安裝Windows Debugger。

步驟1：
打開Windbg。選擇“File”菜單，選擇“Open crash dump”，選擇“Dump file”（SQLDump000#.mdmp）。

步驟2：
在命令行窗口輸入
.sympath srv*c:\Websymbols*http://msdl.microsoft.com/download/symbols;

步驟3：
輸入.reload /f并回車。這將強(qiáng)制debugger立即加載所有的符號(hào)。

步驟4：
通過使用debugger命令lmvm驗(yàn)證是否符號(hào)被SQL Server加載。

0:002> lmvm sqlservr
start             end                 module name
00000000`01000000 00000000`03679000   sqlservr T (pdb symbols)          c:\websymbols\sqlservr.pdb\21E4AC6E96294A529C9D99826B5A7C032\sqlservr.pdb
    Loaded symbol p_w_picpath file: sqlservr.exe
    Image path: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
    Image name: sqlservr.exe
    Timestamp:        Wed Oct 07 21:15:52 2009 (4ACD6778)
    CheckSum:         025FEB5E
    ImageSize:        02679000
    File version:     2005.90.4266.0
    Product version:  9.0.4266.0
    File flags:       0 (Mask 3F)
    File OS:          40000 NT Base
    File type:        1.0 App
    File date:        00000000.00000000
    Translations:     0000.04b0 0000.04e4 0409.04b0 0409.04e4

步驟5：
使用以下命令來搜索線程堆棧來識(shí)別與擁有的任務(wù)相關(guān)的線程，并且它是擁有閂鎖的線程。在你的錯(cuò)誤日志里使用擁有的任務(wù)替代0X0000000006E08328
~*e .echo ThreadId:; ?? @$tid; r? @$t1 = ((ntdll!_NT_TIB *)@$teb)->StackLimit; r? @$t2 = ((ntdll!_NT_TIB *)@$teb)->StackBase; s -d @$t1 @$t2 0X0000000006E08328

ThreadId:
unsigned int 0x93c
ThreadId:
unsigned int 0x9a0
ThreadId:
unsigned int 0x9b4
00000000`091fdaf0  06e08328 00000000 00000000 00000000  (……………
00000000`091fdcb8  06e08328 00000000 091fdd70 00000000  (…….p…….
00000000`091fded0  06e08328 00000000 06e0e798 00000000  (……………
00000000`091fdf38  06e08328 00000000 00000002 00000000  (……………
00000000`091fec60  06e08328 00000000 0168883a 00000000  (…….:.h…..
00000000`091ff260  06e08328 00000000 000007d0 00000000  (……………
00000000`091ff2d0  06e08328 00000000 00000020 00000000  (……. …….
00000000`091ff5f8  06e08328 00000000 800306c0 00000000  (……………
00000000`091ff6c0  06e08328 00000000 00000000 00000000  (……………
00000000`091ff930  06e08328 00000000 00000000 00000001  (……………
00000000`091ff9b8  06e08328 00000000 00000000 00000000  (……………
00000000`091ffa38  06e08328 00000000 00000000 00000000  (……………
00000000`091ffc10  06e08328 00000000 03684080 00000000  (……..@h…..
00000000`091ffc90  06e08328 00000000 00000000 00000000  (……………
ThreadId:
unsigned int 0x9b8
ThreadId:
unsigned int 0x9bc
ThreadId:
unsigned int 0x9c0
……………
…………..

步驟6：
從以上輸出，我們可以看到線程0x9b4與擁有的任務(wù)的指針相關(guān)，并且它是擁有閂鎖的線程。讓我們切換到線程（0x9b4），它正在執(zhí)行擁有的任務(wù)，然后瀏覽這個(gè)堆棧來查看為什么這個(gè)線程長時(shí)間持有閂鎖。

步驟7：
   ~~[0x9b4]s      ==> Switching to the thread (Replace 0x9b4 with your thread id which has reference to the po
ntdll!ZwWaitForSingleObject+0xa:
00000000`77ef047a c3              ret

步驟8：
0:002> kC  ==>  Print the stack
Call Site
ntdll!ZwWaitForSingleObject
kernel32!WaitForSingleObjectEx
sqlservr!SOS_Scheduler::SwitchContext
sqlservr!SOS_Scheduler::Suspend
sqlservr!SOS_Event::Wait
sqlservr!BPool::FlushCache
sqlservr!checkpoint2
sqlservr!alloca_probe
sqlservr!ProcessCheckpointRequest
sqlservr!CheckpointLoop
sqlservr!ckptproc
sqlservr!SOS_Task::Param::Execute
sqlservr!SOS_Scheduler::RunTask
sqlservr!SOS_Scheduler::ProcessTasks
sqlservr!SchedulerManager::WorkerEntryPoint
sqlservr!SystemThread::RunWorker
sqlservr!SystemThreadDispatcher::ProcessWorker
sqlservr!SchedulerManager::ThreadEntryPoint
msvcr80!endthreadex
msvcr80!endthreadex

從以上堆棧，我們可以理解，擁有閂鎖的線程正執(zhí)行檢查點(diǎn)并刷新緩存（臟頁）到磁盤。如果刷新緩存到磁盤（檢查點(diǎn)）花費(fèi)很長時(shí)間，那么顯然是有磁盤瓶頸。

類似的，對(duì)于其他閂鎖超時(shí)問題，首先識(shí)別閂鎖的擁有者線程，讀取擁有者線程的堆棧，來理解擁有者線程執(zhí)行的任務(wù)，并排除由擁有者線程執(zhí)行的任務(wù)引起的性能故障。

如果你想查看等待的線程的堆棧，那么在錯(cuò)誤日志里從閂鎖超時(shí)警告信息獲取任務(wù)（任務(wù)0x0000000006E096D8）代替擁有者任務(wù)（任務(wù)0x0000000006E08328），并使用在步驟5中提到的命令。

我希望這篇博文將幫助你學(xué)習(xí)和排除閂鎖超時(shí)故障。