數據庫的鎖級別

鎖是數據庫保證 并發一致性 的重要手段，不同鎖級別決定了鎖的粒度和范圍，從而影響并發性能與數據安全性。鎖級別從粗到細：全局鎖 → 表級鎖 → 頁級鎖 → 行級鎖，粒度越細，并發度越高，但開銷越大。

一、按鎖的粒度劃分（從粗到細）

1、全局鎖（Global Lock）

        (1) 定義：對整個數據庫實例加鎖，限制所有表的讀寫操作。

        (2) 適用場景：全庫邏輯備份（如 MySQL 的 FLUSH TABLES WITH READ LOCK），確保備份期間數據不被修改，保證備份一致性。

        (3) 特點：

                ① 加鎖期間，整個庫處于只讀狀態，所有更新、插入、刪除操作都會被阻塞。

                ② 粒度最粗，并發影響最大，一般僅在特殊場景（如全庫備份）短期使用。

2、表級鎖（Table-level Lock）

        (1) 定義：對整張表加鎖，控制對表的并發訪問。

        (2) 常見類型：

                ① 表共享鎖（Table Shared Lock，讀鎖 S）： 持有讀鎖時，所有事務可讀取表數據，但不能修改，且其他事務也可加讀鎖，但不能加寫鎖。

                ② 表排他鎖（Table Exclusive Lock，寫鎖 X）：持有寫鎖時，只有當前事務可讀寫表數據，其他事務既不能讀也不能寫（需等待鎖釋放）。

                ③ 意向鎖（Intention Lock）：

                        (a) 數據庫自動添加，用于表級鎖和行級鎖的協調（如 MySQL InnoDB）。

                        (b) 意向共享鎖（IS）：事務 打算 對表中某些行加讀鎖。

                        (c) 意向排他鎖（IX）：事務 打算 對表中某些行加寫鎖。

                        (d) 作用：提前聲明鎖意圖，避免表級鎖和行級鎖的沖突檢查效率過低。

                ④ 自增鎖（Auto-increment Lock）：

                        (a) 針對自增列（如 AUTO_INCREMENT）的特殊鎖，保證插入時自增值唯一。

                        (b) MySQL 可通過 innodb_autoinc_lock_mode 調整鎖粒度（0： 全表鎖； 1： 連續值批量鎖；2：無鎖，依賴二進制日志確保一致性）。

        (3) 適用場景：

                ①  表結構修改（如 ALTER TABLE）、全表掃描或大量數據更新（避免行鎖競爭）。

                ② 非事務引擎（如 MySQL MyISAM）默認使用表級鎖。

        (4) 特點：開銷小、加鎖快，適合表級操作，但 并發度低（容易阻塞）。

3、行級鎖（Row-level Lock）

        (1) 定義：對表中單行數據加鎖，是粒度最細的鎖，并發度最高。

        (2) 常見類型（以 MySQL InnoDB 為例）：

                ① 行共享鎖（Row Shared Lock，S）：事務對某行加讀鎖，允許其他事務讀該行，但不能修改（需等鎖釋放）。

                ② 行排他鎖（Row Exclusive Lock，X）：事務對某行加寫鎖，其他事務既不能讀（需等鎖釋放）也不能寫該行。

                ③ 間隙鎖（Gap Lock）：

                        (a) 鎖定索引記錄之間的 "間隙"（如 WHERE id BETWEEN 5 AND 10，鎖定 5~10 之間的空白區域），防止其他事務插入數據導致 "幻讀"。

                        (b) 僅在 InnoDB 的 可重復讀（RR）隔離級別 下生效。

                ④ 臨鍵鎖（Next-key Lock）：

                        (a) 行鎖 + 間隙鎖的組合，鎖定索引記錄本身及前面的間隙（如索引值為 10 的行，鎖定 (-∞,10] 范圍）。

                        (b) InnoDB 默認的行級鎖類型，用于平衡并發和一致性。

                ⑤ 記錄鎖（Record Lock）：僅鎖定單行記錄（不包含間隙），在 讀已提交（RC）隔離級別 或條件命中唯一索引時生效。

        (3) 適用場景：

                ① 高并發的單行或少量行操作（如用戶余額更新、訂單狀態修改）。

                ② 事務引擎（如 InnoDB、PostgreSQL）的核心鎖機制。

        (4) 特點：

                ① 開銷大、加鎖慢（需定位具體行），但并發度高，適合精細化控制。

                ② 可能引發死鎖（多個事務互相等待對方釋放行鎖）。

4、頁級鎖（Page-level Lock）

        (1) 定義：對數據頁（數據庫存儲的基本單位，如 InnoDB 一頁默認 16KB）加鎖，粒度介于表級和行級之間。

        (2) 適用場景：部分數據庫（如 MySQL BDB 引擎、SQL Server）支持，平衡并發和開銷。

        (3) 特點：加鎖速度快于行鎖，并發度高于表鎖，但可能出現 "頁內無關行被鎖定" 的浪費。

二、按鎖的功能 / 模式劃分

1、共享鎖（Shared Lock，S 鎖）

        (1) 作用：允許事務讀取數據，多個事務可同時持有同一資源的 S 鎖。

        (2) 兼容性：與 S 鎖兼容，與 X 鎖互斥（讀鎖和寫鎖不能同時存在）。

        (3) 示例：SELECT ... LOCK IN SHARE MODE（MySQL InnoDB 手動加讀鎖）。

2、排他鎖（Exclusive Lock，X 鎖）

        (1) 作用：允許事務修改數據，僅當前事務可持有，阻止其他事務讀寫。

        (2) 兼容性：與所有鎖（S 鎖、X 鎖）互斥。

        (3) 示例：SELECT ... FOR UPDATE（MySQL InnoDB 手動加寫鎖），或 UPDATE/DELETE 語句自動加 X 鎖。

3、樂觀鎖（Optimistic Lock）

        (1) 定義：非數據庫原生鎖，通過 "版本號" 或 "時間戳" 實現并發控制，假設沖突概率低，僅在提交時檢查是否有沖突。

        (2) 實現方式：表中增加 version 字段，更新時 WHERE version = 原版本，若失敗則重試。

        (3) 適用場景：讀多寫少、沖突少的場景（如商品庫存查詢）。

        (4) 特點：無鎖等待，性能高，但需應用層實現，不適合高沖突場景。

4、悲觀鎖（Pessimistic Lock）

        (1) 定義：數據庫原生鎖機制，假設沖突概率高，操作前先加鎖，確保獨占資源。

        (2) 示例：行級鎖、表級鎖均屬于悲觀鎖。

        (3) 適用場景：寫多讀少、沖突頻繁的場景（如秒殺庫存扣減）。

        (4) 特點：保證一致性，但可能導致鎖等待和死鎖。

三、不同數據庫的鎖機制差異

1、MySQL：

        (1) InnoDB 支持行級鎖（Next-key Lock 為主）、表級鎖（意向鎖、自增鎖）、全局鎖。

        (2) MyISAM 僅支持表級鎖（讀鎖 / 寫鎖），不支持事務。

2、PostgreSQL：支持行級鎖（共享 / 排他）、表級鎖、頁級鎖，且有 "咨詢鎖"（應用層自定義鎖）。

3、Oracle：以行級鎖為主，通過 "回滾段" 實現多版本并發控制（MVCC），鎖機制更靈活。

4、SQL Server：支持表級鎖、頁級鎖、行級鎖，可通過 SET TRANSACTION ISOLATION LEVEL 調整鎖行為。

不斷成長，走出舒適區，實現自我增值。-- 煙沙九洲