鎖的必要性：四大並發異常
- 遺失更新：兩事務讀同一列、各自更新，後寫入覆蓋先寫入。
- 髒讀：事務讀取另一事務未提交的修改，後者回滾導致資料不存在。
- 不可重複讀：同一事務兩次讀同一列，中間被他事務修改，結果不一致。
- 幻讀：事務讀取範圍，他事務插入新列符合範圍，重讀時出現「幻影」列。
多粒度鎖定架構
SQL Server 支援層級：資料列 → 頁 (8KB) → 區 (8頁) → 資料表 → 資料庫。
- 列鎖：粒度最細，並發最高，但鎖數量多、記憶體開銷大。
- 頁鎖：SQL 7.0 前預設，鎖定整頁，即使只改一列也阻塞該頁其他列。
- 表鎖：系統資源最少，但並發最低，大量資料操作時自動升級使用。
- 區鎖：僅用於配置空間（建庫、建表），防止同區重複配置。
- 資料庫鎖：僅用於資料庫還原，設定單一使用者模式。
鎖升級
當單一事務持有大量細粒度鎖（列/頁）超過閾值，系統自動升級為表鎖以降低管理開銷。閾值由動態演算法決定，無需人工設定。
六大鎖模式
- 共享鎖 (S)：唯讀操作（
SELECT），多事務可同時持有，讀完即釋放（除非 HOLDLOCK）。
- 更新鎖 (U)：修改初期防死鎖，讀取時取 U 鎖，修改時轉 X 鎖，避免 S→X 升級死鎖。
- 排他鎖 (X)：資料修改（
INSERT/UPDATE/DELETE），獨佔資源，阻塞其他讀寫。
- 結構鎖 (Sch-M/Sch-S)：DDL 操作取 Sch-M；編譯查詢取 Sch-S，不阻塞任何鎖含 X，但阻塞 DDL。
- 意圖鎖 (IS/IX/SIX)：表示在較低層級將取得 S/X 鎖，協調多粒度鎖相容性。
- 大量更新鎖 (BU)：
BULK INSERT 配合 TABLOCK 時使用，允許並發大量載入，阻塞非大量操作。
表提示控制鎖行為
| 提示 | 效果 |
|---|
| HOLDLOCK | 持有 S 鎖至交易結束 |
| NOLOCK / READUNCOMMITTED | 不加鎖，可能讀髒資料，僅限 SELECT |
| PAGLOCK | 強制頁鎖 |
| READCOMMITTED | 提交讀隔離級別語意 |
| READPAST | 跳過已鎖列，僅限 READ COMMITTED |
| REPEATABLEREAD | 可重複讀隔離級別 |
| ROWLOCK | 強制列鎖 |
| SERIALIZABLE | 可序列化隔離級別，等同 HOLDLOCK |
| TABLOCK | 表級 S 鎖，陳述式結束釋放；配合 HOLDLOCK 持有至交易結束 |
| TABLOCKX | 表級 X 鎖，阻塞所有讀寫 |
| UPDLOCK | 讀取時取 U 鎖，持有至陳述式/交易結束 |
死鎖偵測與犧牲者選擇
- 系統定期掃描等待圖，偵測到循環等待（鎖請求環）即判定死鎖。
- 自動選擇「回滾成本最低」的事務作為犧牲者，回滾並返回 1205 錯誤。
- 可用
SET DEADLOCK_PRIORITY LOW 降低會話優先權，增加被選為犧牲者機率。
應用層避免死鎖六大原則
- 統一資料表存取順序（所有程式依相同順序鎖表）。
- 交易簡短、單一批次完成，避免使用者介入等待。
- 時域離散：排程後台作業錯峰，避開前台尖峰，工作流限制同類任務併發數。
- 空域離散：大表拆分（水平/垂直/按用戶群），減少熱點爭用。
- 使用最低足夠隔離等級（預設 READ COMMITTED），避免過度 SERIALIZABLE。
- 綁定連接 (
sp_getbindtoken/sp_bindsession) 共享鎖與交易，消除同邏輯單元內鎖衝突。
結語
理解鎖粒度、模式、升級與死鎖機制，配合應用層設計原則，是 DBA 與開發人員構建高並發、高一致性系統的核心功力。
