← 技術筆記

DBA筆記-基本維護知識-1.4-檢查鎖定來源

資料庫 · 2026/8/6

舊文搬家系列之四。

這篇文章做為一個初級教學只把鎖定這件事簡單帶過。

實際上,我覺得鎖定真是個可怕的東西,就算是到現在,我也還是偶爾會遇到完全不知道怎麼辦的卡死事件。


鎖定(Lock)是資料庫運作的基本,其基本邏輯是當第一個執行序準備對A資料異動時,便會對A資料執行鎖定,以防在其異動過程中被其它執行序影響。

鎖定有多種類型,

一些鎖定是共享的,在鎖定期間其資料仍可被其他執行序讀取,

一些鎖定是獨占的,在該執行序完成工作前,其資料都不可被其它執行序讀取,

鎖定本身是一門複雜學問,本章先不提,本章只講單的檢查鎖定來源與內容的方法。

檢查鎖定來源最簡單的方式是開啟SQL SERVER的Activity Monitor(活動監視器)介面,它畫面中就會顯示各執行序的封鎖者,"封鎖者"代表造成此執行序等待的元凶,正常情況下,若A執行序被B執行序鎖定,此時"封鎖者"欄位會顯示B執行序的SessionID,此時就要等待B執行序完成後,A執行序才可繼續執行。

而更複雜一點的狀況中,可能會是A鎖定B,B再鎖定C,形成一個互等的狀況,此時最源頭的鎖定者的"源頭封鎖者"欄位會顯示1(在此例中為A)。


除由介面中查詢鎖定之外,當然也可下查詢語法,以下只舉一個簡單且常見的查詢語法,在實務上不只單一語法可查詢,且各語法中還有多種搭配方式,不再一一詳述。

SELECT      r.scheduler_id as 排程器識別碼,

            status         as 要求的狀態,

            r.session_id   as SPID,

            r.blocking_session_id as BlkBy,

            substring(

				ltrim(q.text),

				r.statement_start_offset/2+1,

				(CASE

                 WHEN r.statement_end_offset = -1

                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2

                 ELSE r.statement_end_offset

                 END - r.statement_start_offset)/2)

                 AS [正在執行的 T-SQL 命令],

            r.cpu_time      as [CPU Time(ms)],

            r.start_time    as [開始時間],

            r.total_elapsed_time as [執行總時間],

            r.reads              as [讀取數],

            r.writes             as [寫入數],

            r.logical_reads      as [邏輯讀取數],

            q.text, /* 完整的 T-SQL 指令碼 */

            d.name               as [資料庫名稱]

FROM        sys.dm_exec_requests r 

			CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q

			LEFT JOIN sys.databases d ON (r.database_id=d.database_id)

WHERE       r.session_id > 50 AND r.session_id <> @@SPID

ORDER BY    r.total_elapsed_time desc

上述這個例子,查出來的結果與活動監視器的資料大同小異,主要只是少了圖形介面而已。其中的BlkBy欄位便等同活動監視器中的"封鎖者"欄位。

當查出鎖定或被鎖定執行序對應的SessionID後,可以使用  DBCC INPUTBUFFER(SessionID) 這個語法來查詢該執行序的執行內容,或是直接在活動監視器的執行序上按滑鼠右鍵點選"詳細內容"亦可。

當然,查詢Session執行內容的方式亦不僅限上方所述,SQL SERVER提供了非常多類似功能的工具及語法。

鎖定常會造成等待現象,狀況更複雜時還可能出現DEAD LOCK使執行序異常中止。

即使如此,鎖定機制是資料庫運作的基本原則,鎖定用在多線執行中的排序與及隔離,因此若希望SQL SERVER完全不要出現鎖定的想法並不實際。

一般來說,都要等到鎖定來源完成後,被鎖定者才可繼續執行工作,但特殊狀況下也可強制中止鎖定者正在執行的工作(kill process),但通常是在確定該執行序執行有異常的狀況下才會執行此處理。

留言

還沒有留言,來當第一個吧。

寫留言