SQL Server 鎖機制全解析：從粒度、模式到動態升級的核心原理

為何需要鎖？

多使用者並發存取同一資料時，若無協調機制會產生四大經典問題：

1. 更新遺失：兩筆交易讀取同一行、各自修改、後寫入者覆蓋先前更新。
2. 髒讀：交易 A 修改未提交，交易 B 讀取此中間狀態，A 回滾後 B 擁有錯誤資料。
3. 不可重複讀：交易 A 同一查詢執行兩次，中間交易 B 修改並提交，導致兩次結果不一致。
4. 幻讀：交易 A 範圍查詢，交易 B 插入新行滿足條件，A 再次查詢發現「多出」的行。

鎖即透過限制資源存取權實現隔離，保證 ACID 特性。

鎖粒度階層與權衡

動態鎖升級

SQL Server 監控交易持有鎖數量，超過閾值（約 5,000 把鎖）自動將列/頁鎖升級為表鎖，降低記憶體壓力。閾值與升級行為由引擎自動決定，不可手動設定。設計啟示：大批次更新建議分批次提交，避免觸發升級導致並發急降。

鎖模式完整圖譜

意圖鎖：層級間的協調信號

• 意圖共享：下層將取得 S 鎖。
• 意圖排他：下層將取得 X 鎖。
• 意圖共享排他：上層允許 S，下層將取得 X。

意圖鎖不阻塞資料操作，僅讓引擎快速判斷上層資源是否可加鎖，避免逐層掃描。

隔離等級與鎖行為對應表

檢視與排查實務

• sp_lock / sys.dm_tran_locks：即時鎖快照。
• DBCC INPUTBUFFER(spid)：查看阻塞/被阻塞 SPID 執行的 SQL 文本。
• 擴充事件 / Profiler：捕捉 Lock:Deadlock 圖形化分析死結鏈。

最佳實踐原則

1. 固定存取順序（表 A → 表 B），消除循環等待條件。
2. 交易簡短、單一批次提交，縮短鎖持有時間。
3. 善用索引縮小掃描範圍，降低鎖粒度需求。
4. 避免交易中夾帶使用者互動（等待輸入）。
5. 評估可接受的隔離等級，必要時啟用 READ_COMMITTED_SNAPSHOT。

結語

鎖是 SQL Server 並發控制的基石。深入理解粒度、模式、升級與隔離等級的交互關係，才能在高並發環境下兼顧資料一致性與系統吞吐量。