Documentation
Computer Science
資料庫
資料庫基礎
Database Locks

Database Locks

部分內容由 LLM 生成，尚未經過人工驗證。

資料庫鎖定機制用於控制併發存取，確保資料的一致性與完整性。

Pessimistic / Optimistic Lock

悲觀鎖（Pessimistic Lock）

適合用在資料衝突頻繁發生的環境中。它能確保資料的安全性，但會降低系統的併發能力。

樂觀鎖（Optimistic Lock）

適合讀多寫少的場景，特別是在衝突較少時，它能夠有效提高系統的性能。但如果競爭激烈，可能導致過多的重試操作。

  sequenceDiagram
    participant A as ClientA
    participant B as ClientB
    participant DB as Database

    B ->> DB: read product
    DB ->> B: return product(Quantity=10, Version=1)
    A ->> DB: read product
    DB ->> A: return product(Quantity=10, Version=1)
    B ->> DB: update product(Quantity=8)
    DB ->> DB: update quantity, incr version(Version=2) success
    A ->> DB: update product(Quantity=9, Version=1)
    DB ->> DB: version checked failed(Version != 1)
    note over A, DB: conflict!
    DB ->> A: OptimisticLockErr

DB 鎖的種類

Database Locks Types

依鎖定範圍分類

鎖類型說明
Row-Level Lock鎖定表中的特定行，允許同時存取其他行
Page-Level Lock鎖定資料庫中的特定頁（固定大小的資料區塊）
Table-Level Lock鎖定整個表，實現簡單但會顯著降低並發性

依鎖定模式分類

鎖類型說明
Shared Lock (S Lock)共享鎖，允許多個事務同時讀取資源但不能修改它
Exclusive Lock (X Lock)排他鎖，允許事務讀取和修改資源，持有時其他事務無法取得任何類型的鎖
Update Lock (U Lock)更新鎖，用於防止事務打算更新資源時出現死鎖情況

其他鎖類型

鎖類型說明
Schema Lock架構鎖，用於保護資料庫物件的結構
Bulk Update Lock (BU Lock)批次更新鎖，在批量插入操作時使用，透過減少所需的鎖定數量來提高效能
Key-Range Lock鍵範圍鎖定，用在索引資料中以防止幻讀

MySQL / PostgreSQL Lock 支援

Lock 類型MySQL (InnoDB)PostgreSQL備註
Row-Level Lock✅ 自動✅ 自動InnoDB 預設行級鎖，由引擎根據查詢自動選擇
Page-Level Lock❌❌兩者皆不支援，為 SQL Server 特有
Table-Level Lock✅ LOCK TABLES✅ LOCK TABLE可手動指定，但通常由引擎自動處理
Shared Lock (S)✅ SELECT ... FOR SHARE✅ SELECT ... FOR SHAREMySQL 8.0+ 支援 FOR SHARE 語法
Exclusive Lock (X)✅ SELECT ... FOR UPDATE✅ SELECT ... FOR UPDATE兩者語法相同
Update Lock (U)❌❌SQL Server 特有，MySQL/PostgreSQL 無此概念
Schema Lock✅ Metadata Lock✅ AccessExclusiveLockDDL 操作時自動取得
Bulk Update Lock❌❌SQL Server 特有
Key-Range Lock✅ Gap Lock / Next-Key Lock✅ Predicate Lock用於防止幻讀
MySQL InnoDB 的鎖定機制大多由儲存引擎根據 SQL 語句和隔離級別自動選擇，使用者無法直接指定 Row Lock 或 Gap Lock。

減少 Row Lock 競爭

策略說明
縮小事務範圍只鎖定必要的行，盡快 COMMIT
避免長時間持鎖不要在事務中做耗時操作（如 API 呼叫）
使用適當索引無索引的 UPDATE/DELETE 可能升級為 Table Lock
分批處理大量更新時分批執行，避免一次鎖太多行

批次更新最佳實踐

分批 UPDATE + LIMIT（應用層迴圈）

應用層重複執行此 SQL 直到影響行數為 0：

-- 單次執行，應用層判斷 affected_rows 後決定是否繼續
UPDATE orders
SET status = 'archived'
WHERE created_at < '2024-01-01' AND status = 'completed'
LIMIT 1000;

應用層邏輯（偽代碼）：

while True:
    affected = db.execute(UPDATE ... LIMIT 1000)
    if affected == 0:
        break
    sleep(0.1)  # 可選：減少鎖競爭

使用子查詢批次更新（推薦）

應用層可直接組裝 SQL，無需建立臨時表：

UPDATE user_accounts ua
INNER JOIN (
    -- 務必確保 ID 經過排序，預防死鎖
    SELECT 1 as user_id, 50.00 as amount UNION ALL
    SELECT 2, 100.00 UNION ALL
    SELECT 3, 75.50
    ORDER BY user_id ASC
) as temp ON ua.user_id = temp.user_id
SET ua.balance = ua.balance + temp.amount
WHERE ua.balance + temp.amount >= 0;  -- 保留樂觀檢查
ORDER BY 排序的重要性：多行更新時，若不同事務以不同順序鎖定相同資料，會造成死鎖。統一按 PK 排序可確保所有事務以相同順序取得鎖。

使用臨時表批次更新

適用於資料量大或需要多次查詢的場景：

-- 1. 先將要更新的 ID 寫入臨時表
CREATE TEMPORARY TABLE tmp_update_ids AS
SELECT id FROM orders WHERE created_at < '2024-01-01' LIMIT 10000;

-- 2. 透過 JOIN 批次更新
UPDATE orders o
INNER JOIN tmp_update_ids t ON o.id = t.id
SET o.status = 'archived';

-- 3. 清理臨時表
DROP TEMPORARY TABLE tmp_update_ids;
關於 autocommit=0 批次提交：在批次 INSERT 時效能提升明顯，UPDATE 時也有效但需注意事務大小。過大的事務會導致 undo log 膨脹、鎖持有時間過長，建議每 1000-5000 筆提交一次。

Gap Lock

間隙鎖是 MySQL InnoDB 的特殊鎖定機制。

  • 透過 SELECT ... FOR UPDATE 等語句來觸發
  • 間隙鎖的作用是鎖定索引鍵（PK）之間的空隙，防止其他事務在這個空隙中插入新行

Demo Table

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

防止幻讀

TimeTransaction ATransaction B
T0BEGIN;BEGIN;
T1SELECT * FROM employees WHERE id BETWEEN 1 AND 3 FOR UPDATE;
T2INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
T3COMMIT; — 等待事務 A Commit
T4SELECT * FROM employees; — ID(2) 資料不在 Table 內
T5COMMIT;
T6SELECT * FROM employees; — ID(2) 資料現在顯示在 Table 內

防止資料誤刪/更改

TimeTransaction ATransaction B
T0BEGIN;BEGIN;
T1SELECT count(1) FROM employees WHERE id >= 1; — 顯示 2 筆
T2INSERT INTO employees (id, name, salary) VALUES (4, 'Jack', 10000.00);
T3COMMIT; — 等待事務 A Commit
T4SELECT count(1) FROM employees WHERE id >= 1; — 依舊顯示 2 筆
T5COMMIT;
T6SELECT count(1) FROM employees WHERE id >= 1; — 顯示 3 筆

Distributed Locks

分散式鎖用於在分散式系統中協調多個節點對共享資源的存取。

Centralized Locking

中央化鎖定，由單一節點管理所有鎖定。

問題：單點故障（SPOF）

Token-Based Locking

基於令牌的鎖定。

問題：

  • Token Lost（令牌遺失）
  • Token Expired（令牌過期）

Quorum-Based Locking (RedLock)

以 Redis Cluster 為例（假設 5 個節點）：

步驟：

  1. Acquire Time：客戶端首先記錄當前時間 T1
  2. Lock Acquisition：
    • 嘗試在每個 Redis 節點上使用 SETNX 命令來獲取鎖
    • 每個節點會返回：
      • OK：如果成功獲得鎖
      • NULL：如果該鎖已被其他客戶端持有
    • 客戶端會在每個 SETNX 命令中設置一個過期時間（TTL），確保鎖不會永遠存在，避免死鎖
  3. Calculate Elapsed Time：計算耗時 T2 - T1
  4. Quorum Check：N/2 + 1 = 5/2 + 1 = 3，如果成功節點數 > 3 則鎖定成功
  5. Unlocking：使用 DEL 釋放鎖

優點：

  • Quorum Requirement：即使一兩個 Redis 節點失敗，只要大多數節點正常運行（例如 3 節點），鎖仍能被正確地獲得和釋放
  • Fault Tolerance：避免了單點故障

相關主題

CAP TheoremNotations