SQL Server 2000 SELECT 死鎖頻發？根因多為索引缺失與大結果集，而非純查詢鎖競爭

現象與誤區

用戶反饋：「SQL Server 2000 經常出現 SELECT 死鎖，一條 SELECT 導致所有客戶端卡死。」
**關鍵澄清**：純粹的 SELECT 語句（無 HOLDLOCK/TABLOCKX 等提示）**極難產生傳統意義的死鎖**（環狀等待）。所謂「死鎖」多為：**長時間阻塞**被誤判為死鎖，或因 SELECT 掃描海量數據導致 鎖升級、TempDB 擠壓、日誌寫入瓶頸，引發連鎖超時。

真實根因排查清單

1. 缺少覆蓋索引 → 全表掃描 → 鎖升級

無索引支持的 SELECT 觸發 Table Scan，持有大量行鎖/頁鎖，超過閾值（默認 5000）升級為 **表鎖 (TAB)**，阻塞所有寫入甚至並發讀取（取決於隔離級別）。

對策：對 WHERE、JOIN、ORDER BY 涉及列建立**覆蓋索引**（INCLUDE 非鍵列），將掃描轉為 Index Seek。

2. 結果集過大 → 網絡/客戶端消費緩慢 → 連接長期佔用

SELECT * FROM BigTable 返回百萬行，客戶端緩存區滿導致服務端發送緩衝區堵塞，事務長時間不提交，鎖持續持有。

對策：

• 強制分頁：SELECT TOP 100 ... 或 ROW_NUMBER() OVER (...) WHERE rn BETWEEN @start AND @end。
• 僅選取必要列，避免 SELECT *。
• 啟用 SET ROWCOUNT 或應用層限流。

3. 隔離級別過高 / 長事務

應用代碼隱式開啟事務（如 ADO.NET 默認 ReadCommitted + 隱式事務），或使用 Serializable 級別，導致共享鎖持續到事務結束。

對策：

• 盡量使用 READ COMMITTED SNAPSHOT (RCSI) 隔離級別（SQL 2005+），讀不阻塞寫。
• SQL 2000 可嘗試 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (NOLOCK) —— **僅限報表類容忍臟讀場景**。

4. 統計信息過期 → 執行計劃劣化

SQL 2000 不支持自動更新統計（需 sp_updatestats 或 UPDATE STATISTICS），導致優化器誤選掃描計劃。

對策：建立定時任務每日更新統計信息，重建碎片索引（DBCC DBREINDEX）。

立即可執行的緩解腳本

-- 1. 查看當前阻塞鏈
SELECT * FROM master..sysprocesses WHERE blocked > 0;

-- 2. 找出消耗資源最重的查詢
SELECT TOP 10 
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1, 
        (CASE WHEN qs.statement_end_offset = -1 
              THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text,
    qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_ms DESC;
-- 注：SQL 2000 無 DMV，需用 DBCC INPUTBUFFER(spid) 或 Profiler 追蹤

-- 3. 為高頻查詢添加覆蓋索引（示例）
CREATE NONCLUSTERED INDEX IX_Orders_CustDate_Cover 
ON Orders (CustomerID, OrderDate) 
INCLUDE (TotalAmount, Status); -- SQL 2000 不支持 INCLUDE，改為復合索引鍵包含

架構級建議：讀寫分離與緩存

• 報表/查詢類 SELECT 導向**只讀副本**（Log Shipping / 復制）或**緩存層**（Redis/Memcached）。
• 核心業務寫入走主庫，查詢走從庫，物理隔離鎖競爭。
• 熱點數據（如配置表、字典表）全量加載到應用內存/分布式緩存，徹底繞過 DB。

總結

SQL Server 2000 「SELECT 死鎖」本質多為 **全表掃描+鎖升級+大結果集** 引發的**嚴重阻塞**。解決路徑：**補索引 → 限結果集 → 降隔離級別 → 讀寫分離 → 升級數據庫版本**（SQL 2000 已停止支持 2013 年，強烈建議遷移至 SQL 2019/2022）。