SQL Server 排查長時間未提交事務完整指南

危害分析

• 阻塞其他會話存取被修改資料
• 交易日誌無法截斷，導致日誌暴增甚至達物理上限
• 造成鎖爭用、死鎖、效能驟降

核心工具：DBCC OPENTRAN

DBCC OPENTRAN;

輸出關鍵資訊：

• SPID：服務器進程 ID
• Start Time：事務開始時間
• LSN：日誌序列號

進階排查：DMV 動態管理視圖

1. 找出用戶事務會話

SELECT session_id, transaction_id, is_user_transaction, is_local
FROM sys.dm_tran_session_transactions
WHERE is_user_transaction = 1;

2. 取得該會話最近執行的 SQL

SELECT s.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) s
WHERE c.session_id = 54;  -- 替換實際 SPID

3. 分析事務詳細狀態

SELECT 
    transaction_begin_time,
    CASE transaction_type
        WHEN 1 THEN 'Read/Write'
        WHEN 2 THEN 'Read-Only'
        WHEN 3 THEN 'System'
        WHEN 4 THEN 'Distributed'
    END AS tran_type,
    CASE transaction_state
        WHEN 0 THEN 'Not initialized'
        WHEN 1 THEN 'Initialized not started'
        WHEN 2 THEN 'Active'
        WHEN 3 THEN 'Ended (read-only)'
        WHEN 4 THEN 'Commit initiated'
        WHEN 5 THEN 'Prepared'
        WHEN 6 THEN 'Committed'
        WHEN 7 THEN 'Rolling back'
        WHEN 8 THEN 'Rolled back'
    END AS tran_state
FROM sys.dm_tran_active_transactions
WHERE transaction_id = 489743;  -- 替換實際 transaction_id

標準排查流程（SOP）

1. DBCC OPENTRAN 獲取最早活動事務 SPID
2. sys.dm_tran_session_transactions 映射 SPID → transaction_id
3. sys.dm_exec_connections + dm_exec_sql_text 取得最後執行 SQL
4. sys.dm_tran_active_transactions 確認事務類型、狀態、持續時間
5. 定位應用程式代碼位置，檢查是否遺漏 COMMIT/ROLLBACK、邏輯死循環、長時間外部呼叫
6. 必要時 KILL spid 強制回滾（謹慎操作，評估業務影響）

預防措施

• 程式碼審查：所有事務路徑必有 COMMIT/ROLLBACK
• 設定 SET LOCK_TIMEOUT / SET QUERY_GOVERNOR_COST_LIMIT
• 監控告警：sys.dm_tran_active_transactions 持續時間 > 閾值發警
• 連接池配置：啟用連接驗證、閒置超時回收