萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server 長時間未提交事務偵測與處理:DBCC OPENTRAN + DMV 深度排查

未提交事務會阻塞其他會話、膨脹交易記錄檔、阻礙記錄檔截斷。本文示範用 DBCC OPENTRAN 定位最早活動事務,再結合 DMV 鑽取具體 SQL 與會話資訊。

1. 定位最早活動事務

DBCC OPENTRAN;

輸出關鍵欄位:

  • SPID:伺服器處理序 ID,可用於 KILL 或進一步查詢。
  • Start Time:事務開始時間,判斷持續多久。
  • LSN:記錄檔序號,供高階分析。

2. 透過 DMV 還原完整脈絡

Step A: 取得 session_id ↔ transaction_id 對應

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

Step B: 以 session_id 取得最近執行 SQL

SELECT st.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
WHERE c.session_id = 54;  -- 替換為 Step A 取得的 SPID

Step C: 以 transaction_id 觀察事務狀態

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'
    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;  -- 替換為 Step A 取得的 transaction_id

3. 完整排查腳本 (一鍵輸出)

-- 找出所有運行超過 30 分鐘的用戶事務
SELECT
  tat.transaction_id,
  tat.transaction_begin_time,
  DATEDIFF(MI, tat.transaction_begin_time, GETDATE()) AS duration_min,
  tst.session_id,
  st.text AS last_sql
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tst ON tat.transaction_id = tst.transaction_id
CROSS APPLY sys.dm_exec_connections c ON tst.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
WHERE tat.transaction_type = 1
  AND tat.transaction_state = 2
  AND DATEDIFF(MI, tat.transaction_begin_time, GETDATE()) > 30;

4. 處置建議

  • 確認為應用 Bug (漏 Commit/Rollback) → 通知開發修正程式碼。
  • 確認為長跑批次作業 → 評估是否可拆分、加 CHECKPOINT、調整隔離等級。
  • 確認為孤立連線 (App Crash) → KILL 釋放鎖與記錄檔空間。
  • 建立監控告警:DBCC OPENTRAN 定期巡檢 + DMV 長事務查詢 → 郵件/Slack 通知。

掌握此流程,可在事務造成嚴重阻塞前主動介入。

copyright © 萬盛學電腦網 all rights reserved