MySQL 表鎖定實現並發安全的批量資料遷移

場景

需將 insert_table（暫存表）資料批量搬移至 real_table（正式表），並清空暫存表，要求過程中無其他寫入干擾、資料零遺失。

標準流程

LOCK TABLES real_table WRITE, insert_table WRITE;

INSERT INTO real_table SELECT * FROM insert_table;

DELETE FROM insert_table;

UNLOCK TABLES;

關鍵點解析

1. LOCK TABLES ... WRITE：獲取排他鎖，阻塞其他 Session 的讀寫（MyISAM 表級鎖特性）。InnoDB 雖支援行鎖，但 LOCK TABLES 仍會在 Server 層強制表級鎖，確保邏輯原子性。
2. INSERT ... SELECT：單條 SQL 完成資料複製，避免應用層循環往返開銷。
3. DELETE 清空暫存表，釋放空間。
4. UNLOCK TABLES：釋放所有鎖，恢復並發存取。

注意事項

• 鎖定期間不可執行其他需要鎖的語句（如另一個 LOCK TABLES），否則死鎖。
• 若 real_table 極大，INSERT ... SELECT 可能長時間持有鎖，建議分批處理（如每批 1 萬行）並在批次間 UNLOCK TABLES 釋放鎖喘息。
• InnoDB 建議改用 INSERT ... ON DUPLICATE KEY UPDATE + 事務，避免表鎖對並發影響過大。
• 備份/遷移工具首選 mysqldump/XtraBackup，手動鎖表僅適用於特定業務邏輯。