資料庫被誰給卡住了

在一個多人同時使用資料庫的環境中，IT人員常會有這樣的經驗，使用者電話打來抱怨：

「我的程式查詢按下去跑不出來」
「我的報表跑到逾時終止」
「我平常執行這個程式一下子就跑完了，今天程式執行得特別慢，等好久」

為什麼今天資料庫卡卡的？我的資料庫被誰卡住了？

這要回到資料庫同時處理多個程序的機制。SQL Server 資料庫引擎，是架構在Client/Server環境下的服務，當使用者對資料庫伺服器提出請求要查詢某個資料表的某些資料，資料庫引擎會先檢查目前是否有其他程序正在”使用”這些資料，如果其他程序還在使用中，必須等待其他程序使用完才能使用。有點類似，某天小明到便利超商想使用洗手間，得先看洗手間是否有人使用，如果有其他人正在使用中，必須等洗手間沒有人使用才能進去。可是小明怎麼知道洗手間有沒有人使用? 便利超商的洗手間，門把上有一個牌子，當有人進入洗手間鎖上，外面會看到牌子會變成紅色的”使用中”，其他人就知道洗手間現在有人正在使用，鎖打開後，牌子會變成藍色的”空閒中”，下一位客人才能使用。SQL Server也是使用相同的原理，產生鎖定(Lock)來通知其他使用者，目前資料正在使用中。

這裡利用一個簡易的例子來做說明：

在Microsoft SQL Server Management Studio中，新增查詢執行以下的SQL語法(SQLQuery1)

    -- 移除資料庫db01
    USE master;
    DROP DATABASE IF EXISTS db01;
    GO

    -- 建立資料庫db01
    CREATE DATABASE db01;
    GO

    USE db01;

    -- 建立測試資料表 dbo.Test
    CREATE TABLE dbo.Test
    (
    id int PRIMARY KEY
    );

新增查詢，執行以下語法(SQLQuery2)

    USE db01;

    BEGIN TRAN

    INSERT INTO dbo.Test(id) VALUES(1);

再新增一個查詢，執行以下語法(SQLQuery3)

    USE db01;

    SELECT * FROM dbo.Test;

這時候，(SQL-2)的語法會處於一個等待中的狀態，在Object Explored，Server上右鍵 => Activity Monitor

列表中Session ID = 56 的狀態為 SUSPENDED(暫止中)，等待Session 55的Lock被釋放。
Session ID=55, Header Blocker欄位的值為1 ，表示這一個Session為阻擋執行的元凶。

在這個Session上右鍵=>Kill Process就可以讓其他Process的障礙被移除，但也要注意到，此時被Kill的Process的動作就會被取消了，交易就失敗了。

也可以利用SQL語法找出目前正在執行的程序

    EXEC sp_who2;

可以看到 Session 56 被 Session 55給擋住了(Blocked By)

以下的DMV也可以找到被擋住的語法

    SELECT r.session_id,
    r.status AS [指令狀態],
    r.command AS [指令類型],
    r.wait_time/1000.0 AS [等待時間(秒)],
    s.client_interface_name AS [連線資料庫的驅動程式],
    s.host_name AS [電腦名稱],
    s.program_name AS [執行程式名稱],
    t.text AS [執行的SQL語法],
    r.blocking_session_id AS [被鎖定卡住的session_id]
    FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE s.is_user_process = 1;

SQL Server有提供各種監控的DMV(Dynamic Management View)，只是要兜出完整的資訊往往需要好幾個View一起看，對初學者而言不是很容易的事情，也因此，在SSMS介面中有不少工具可以使用，利用這些工具，也可以很容易地找出究竟現在的SQL Server在”忙甚麼”，下次有使用者問「我的程式查詢按下去跑不出來」可以試著利用這些方法來找出，究竟我的資料庫是被誰給卡住了。