| 隔離級別 | 髒讀 | 不可重複讀 | 幻讀 | MySQL 預設 |
|---|
| READ UNCOMMITTED | ✅ 可能 | ✅ 可能 | ✅ 可能 | 否 |
| READ COMMITTED | ❌ 不可能 | ✅ 可能 | ✅ 可能 | 否（推薦生產） |
| REPEATABLE READ | ❌ 不可能 | ❌ 不可能 | ✅ 可能（間隙鎖緩解） | ✅ 是 |
| SERIALIZABLE | ❌ 不可能 | ❌ 不可能 | ❌ 不可能 | 否（效能極差） |
設定隔離級別
-- 當前會話
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 全域永久
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 或 my.cnf
[mysqld]
transaction-isolation = READ-COMMITTED
不可回滾的語句（隱性提交）
以下語句會導致隱性提交，結束當前事務，無法回滾：
- DDL：
CREATE/DROP/ALTER DATABASE/TABLE/VIEW/INDEX
- 資料庫管理：
TRUNCATE TABLE、LOCK TABLES、UNLOCK TABLES
- 複制/備份：
START REPLICA、STOP REPLICA、RESET MASTER
- 帳號管理：
CREATE/DROP/ALTER USER、GRANT/REVOKE
- 其他：
ANALYZE TABLE、CHECK TABLE、OPTIMIZE TABLE、REPAIR TABLE
鐵律：事務中嚴禁混用 DDL 與 DML。
PHP 實戰：mysqli 事務控制
connect_errno) {
die("Connect failed: " . $mysqli->connect_error);
}
try {
// 關閉自動提交
$mysqli->autocommit(false);
// 業務邏輯：新增員工 + 電話
$sql1 = "INSERT INTO employee (first_name, last_name, job_title, salary)
VALUES ('Grace', 'Williams', 'Software Engineer', 5000)";
if (!$mysqli->query($sql1)) {
throw new Exception("Employee insert failed: " . $mysqli->error);
}
$emp_id = $mysqli->insert_id; // 取得自增 ID
$sql2 = "INSERT INTO telephone (employee_id, type, no)
VALUES ($emp_id, 'mobile', '270-598712')";
if (!$mysqli->query($sql2)) {
throw new Exception("Phone insert failed: " . $mysqli->error);
}
// 全部成功 → 提交
$mysqli->commit();
echo "Transaction committed successfully.";
} catch (Exception $e) {
// 任何失敗 → 回滾
$mysqli->rollback();
echo "Transaction rolled back: " . $e->getMessage();
} finally {
$mysqli->autocommit(true); // 恢復自動提交
$mysqli->close();
}
?>
關鍵點解析
autocommit(false)：關鍵步驟，否則每條 SQL 即時提交insert_id：在事務中也能正確取得自增 IDtry-catch：確保異常路徑也能回滾finally：恢復連線預設行為，避免連線池污染
PDO 版本（推薦現代專案）
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO employee (first_name, last_name, job_title, salary) VALUES (?, ?, ?, ?)");
$stmt->execute(['Grace', 'Williams', 'Software Engineer', 5000]);
$emp_id = $pdo->lastInsertId();
$stmt = $pdo->prepare("INSERT INTO telephone (employee_id, type, no) VALUES (?, ?, ?)");
$stmt->execute([$emp_id, 'mobile', '270-598712']);
$pdo->commit();
echo "Success";
} catch (Exception $e) {
$pdo->rollBack();
echo "Failed: " . $e->getMessage();
}
?>
事務效能優化建議
- 縮短事務範圍：只包含必要的寫操作，讀操作移至事務外
- 避免長事務：長事務持有鎖久、阻塞其他事務、Undo Log 膨脹
- 合理索引：減少掃描行數，縮短鎖持有時間
- 批次提交：大量寫入分批，每批 100-1000 行提交一次
- 監控指標：
Innodb_row_lock_waits、Innodb_row_lock_time_avg、Trx_rseg_history_len
死鎖處理
- InnoDB 自動偵測並回滾代價較小的事務（返回
ERROR 1213）
- 應用層捕獲 1213 錯誤後重試（指數退避）
- 固定加鎖順序（如：按主鍵遞增順序更新多表）
- 上一篇文章： Ubuntu/Debian 系統完整卸載 MySQL 指令與殘留清理
- 下一篇文章： MySQL 關聯查詢三種寫法對比：ON vs USING vs WHERE 條件
Copyright ©
程式碼之家 All Rights Reserved
