深入理解 MySQL 資料庫調度策略與鎖定機制

核心概念：讀寫客戶端

為了討論方便，定義兩類客戶端程序：

• 讀取程序：執行 SELECT 查詢的客戶端
• 寫入程序：執行 DELETE、INSERT、REPLACE 或 UPDATE 修改操作的客戶端

MySQL 預設調度策略

1. 寫入請求按到達順序處理
2. 寫入優先權高於讀取

表鎖與調度實現

調度策略透過表鎖實現。客戶端存取表前必須先獲得鎖，可手動用 LOCK TABLES，通常由伺服器鎖管理器自動處理。處理完畢釋放鎖，手動用 UNLOCK TABLES，伺服器也會自動釋放。

鎖的類型與行為

• 寫入鎖（獨占鎖）：寫操作需獨占存取。寫入過程中表處於不一致狀態，索引可能更新中，不允許其他客戶端讀寫。
• 讀取鎖（共享鎖）：讀操作需防止他人寫入，但允許多個客戶端同時讀取（讀取不修改資料）。

查詢修飾符影響調度

MySQL 提供三種修飾符調整調度優先權：

1. LOW_PRIORITY（寫入降級）

適用於 DELETE、INSERT、LOAD DATA、REPLACE、UPDATE。

行為：寫入不再優先於讀取。若寫入等待時有新讀取請求到達，新讀取搶先執行。只有無讀取請求時寫入才執行。理論上 LOW_PRIORITY 寫入可能永遠被阻塞（持續有讀取進來）。

2. HIGH_PRIORITY（讀取升級）

適用於 SELECT。

行為：使該查詢插隊至正在等待的正常優先權寫入操作之前執行。

3. DELAYED（延遲插入）

適用於 INSERT 和 REPLACE。

行為：

1. 伺服器將資料列放入隊列，立即回傳狀態給客戶端（客戶端可繼續執行）
2. 若有讀取正在進行，隊列掛起
3. 無讀取時，伺服器開始批次插入隊列資料
4. 定期檢查是否有新讀取請求，有則暫停插入讓讀取優先
5. 循環至隊列為空

版本支援對照表

INSERT DELAYED 客戶端效益

當其他客戶端執行冗長 SELECT 且不想等待插入完成時極具價值。客戶端能更快繼續執行，但需注意效能差異：若語法錯誤會立即回報錯誤。