您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 事務處理與鎖機制完整指南：從基礎語法到最佳實踐

編輯：MySQL 資料庫相關知識

MySQL 事務處理是確保資料一致性的核心機制，配合行級鎖（InnoDB）可實現高並發下的資料完整性。本文系統整理事務控制語法、鎖類型特性、隔離等級與實務應用模式。

一、事務控制核心語法

-- 開啟事務
START TRANSACTION;  -- 或 BEGIN [WORK]

-- 提交事務
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE];

-- 回滾事務
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE];

-- 設定自動提交模式（連接層級）
SET AUTOCOMMIT = {0 | 1};

CHAIN：事務結束後立即啟動新事務，保持相同隔離等級。
RELEASE：事務結束後斷開客戶端連接。
NO CHAIN / NO RELEASE：抑制預設的鏈結/釋放行為。

二、自動提交與顯式事務模式

MySQL 預設 AUTOCOMMIT=1，每條更新語句立即持久化。對於事務型引擎（InnoDB、BDB、NDB Cluster），可透過 SET AUTOCOMMIT=0 關閉自動提交，需顯式 COMMIT 或 ROLLBACK。

-- 單一系列語句使用顯式事務（推薦）
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

使用 START TRANSACTION 期間自動提交暫時關閉，直到 COMMIT/ROLLBACK 結束後恢復原狀態。

三、一致性快照讀取

START TRANSACTION WITH CONSISTENT SNAPSHOT;

僅適用於 InnoDB，效果等同於 START TRANSACTION 後緊接一個 SELECT，獲得事務開始時刻的一致性視圖。

四、隱含鎖定行為與鎖類型對照

鎖層級	代表引擎	特性
表級鎖	MyISAM, MEMORY, ISAM	開銷小、鎖定快、無死鎖、並發度低
頁級鎖	BDB	開銷與並發度介於表級與行級之間、可能死鎖
行級鎖	InnoDB	開銷大、並發度最高、可能死鎖、支援長時間單列鎖定

五、行級鎖優缺點與適用場景

優點

多連線存取不同記錄時減少鎖衝突
異常回滾時僅影響少量資料
支援長時間單列鎖定

缺點

記憶體佔用較大
大量資料操作時 I/O 開銷大，適合寫操作而非全表掃描讀取
易發生死鎖

表級鎖更優場景

以讀取為主的工作負載
嚴格索引條件下的單列更新/刪除
大量 GROUP BY、全表掃描且無寫入操作

六、死鎖預防與處理

固定存取資料表/資料列的順序
適當縮短事務長度
使用 SELECT ... FOR UPDATE 明確加鎖
捕捉 ER_LOCK_DEADLOCK 錯誤碼並重試

七、實務範例：PHP 中的表鎖與行鎖操作

// 表鎖（MyISAM 適用）
$sql = "LOCK TABLES stat_num WRITE";
$db->execute($sql);
$sql = "UPDATE stat_num SET correct_num=correct_num+1 WHERE stat_date='{$cur_date}'";
$db->execute($sql);
$sql = "UNLOCK TABLES";
$db->execute($sql);

// 行鎖（InnoDB 適用，配合事務）
$db->query("SET AUTOCOMMIT=0");
$row = $db->fetch_first("SELECT * FROM prizes WHERE id={$id} FOR UPDATE");
$db->query("DELETE FROM prizes WHERE id={$row['id']}");
$db->query("COMMIT");