MySQL 事務與鎖定命令完整參考：ACID 事務、表鎖、隔離級別與一致性備份

系統整理 MySQL 事務控制 (BEGIN/COMMIT/ROLLBACK)、表鎖 (LOCK TABLES)、隔離級別設定、隱含提交命令、一致性備份鎖等核心語法與行為。

事務控制

• 預設 autocommit=ON，每條更新即時落盤。
• 事務安全引擎 (InnoDB/BDB) 可設 SET AUTOCOMMIT=0 或 START TRANSACTION (4.0.11+，ANSI 標準) 開啟顯式事務。
• COMMIT 提交；ROLLBACK 回滾。非事務表更新即時生效，回滾時產生警告且不變更。
• 隱含提交命令：ALTER TABLE、CREATE/DROP INDEX、DROP/CREATE/RENAME/TRUNCATE TABLE、DROP DATABASE 會自動提交前序事務。
• 二進位日誌以事務為單位記錄 (COMMIT 時寫入)，支援時間點恢復。

表鎖 (LOCK TABLES)

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [...]
UNLOCK TABLES

• READ：所有線程可讀，不可寫。READ LOCAL 允許非衝突 INSERT (MyISAM 並發插入)。
• WRITE：唯有持鎖線程可讀寫，其他阻塞。優先級高於 READ。
• LOW_PRIORITY WRITE：等待無 READ 鎖時才加鎖，避免寫入飢餓讀取。
• 鎖定順序：內部排序 → 寫鎖優先於讀鎖 → 逐個獲取，防死鎖。
• 連線關閉或再次 LOCK TABLES 自動釋放舊鎖。
• 不鎖場景：單條 UPDATE 原子性；用 UPDATE ... SET col=col+val 或 LAST_INSERT_ID() 替代鎖表；用戶級鎖 GET_LOCK()/RELEASE_LOCK()。

隔離級別 (SET TRANSACTION)

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
  {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

• 預設設定下一個事務隔離級；GLOBAL 需 SUPER 權限影響新連線；SESSION 影響當前連線後續事務。
• 啟動參數 --transaction-isolation 設定全域預設值。

一致性備份鎖

FLUSH TABLES WITH READ LOCK：全庫唯讀鎖，配合支援快照的檔案系統 (Veritas 等) 可秒級熱備份。

關鍵提醒

LOCK TABLES 非事務安全，執行前會自動提交所有活躍事務。