MySQL 僅 InnoDB 與 BDB 支援事務。預設 MyISAM **不支援**。檢查:
SHOW ENGINES; -- InnoDB 行 Support = YES
SHOW VARIABLES LIKE 'have_innodb';
啟用:my.cnf 註釋 skip-innodb,重啟;建表指定 ENGINE=InnoDB 或 ALTER TABLE t ENGINE=InnoDB;。
BEGIN; -- 或 START TRANSACTION;
-- SQL 1
-- SQL 2
-- ...
COMMIT; -- 全部成功提交
-- 或
ROLLBACK; -- 任一失敗回滾
SET autocommit = 0; -- 關閉自動提交,開啟隱性事務
-- SQL 1
-- SQL 2
COMMIT; -- 提交並開啟新隱性事務
-- 或
ROLLBACK; -- 回滾並開啟新隱性事務
SET autocommit = 1; -- 恢復自動提交模式
⚠️ autocommit=0 會影響整個連線周期,易忘記提交導致鎖持有過久,**生產環境強烈建議用顯式 BEGIN/COMMIT**。
<?php
$conn = mysqli_connect('127.0.0.1', 'root', 'pass', 'shop_test');
mysqli_set_charset($conn, 'utf8mb4');
mysqli_begin_transaction($conn); // 或 mysqli_query($conn, 'START TRANSACTION');
try {
// 1. 建立訂單
$sql1 = "INSERT INTO user_order (user, price, count) VALUES ('lucy', 10, 2)";
if (!mysqli_query($conn, $sql1)) throw new Exception('Order insert failed');
// 2. 扣減餘額
$sql2 = "UPDATE user_account SET money = money - 20 WHERE user = 'lucy'";
if (!mysqli_query($conn, $sql2)) throw new Exception('Balance update failed');
// 3. 業務邏輯檢查:餘額不能為負
$res = mysqli_query($conn, "SELECT money FROM user_account WHERE user='lucy'");
$row = mysqli_fetch_assoc($res);
if ($row['money'] < 0) throw new Exception('Insufficient balance');
mysqli_commit($conn);
echo "OK";
} catch (Exception $e) {
mysqli_rollback($conn);
echo "Error: " . $e->getMessage();
}
mysqli_close($conn);
關鍵點:業務邏輯檢查(如餘額)必須在事務內執行,否則無法回滾已提交的操作。
InnoDB 自增計數器在**記憶體**中單調遞增,不持久化到磁碟。ROLLBACK 不會倒退計數器。重啟時以 SELECT MAX(id) FROM table FOR UPDATE 重新初始化。這是設計使然,**不可避免**,業務勿依賴連續無間隙 ID。
COMMIT 時強制刷盤(innodb_flush_log_at_trx_commit=1),崩潰恢復用。INSERT→DELETE,UPDATE→反向 UPDATE),儲存於共享表空間/Undo 表空間。ROLLBACK 執行邏輯逆操作,**不回收空間**,由 purge 線程異步清理。ERROR 1213)。Purge 線程清理 Undo Log,導致歷史版本堆積、ibdata1 膨脹。mysqldump --single-transaction、xtrabackup)需等待長事務結束才能拿到一致性視圖。| 隔離級別 | 髒讀 | 不可重複讀 | 幻讀 | 鎖機制 |
|---|---|---|---|---|
| READ UNCOMMITTED | Y | Y | Y | 無鎖 |
| READ COMMITTED | N | Y | Y | 行鎖(無間隙鎖) |
| REPEATABLE READ (預設) | N | N | N* (MVCC) | 行鎖+間隙鎖 |
| SERIALIZABLE | N | N | N | 表鎖/範圍鎖 |
生產建議 READ COMMITTED + binlog_format=ROW/MIXED,平衡併發與一致性。
MySQL 事務建立在 InnoDB Redo/Undo 雙日誌架構上。掌握 BEGIN/COMMIT/ROLLBACK 顯式控制、理解 AUTO_INCREMENT 不回滾本質、警惕長事務與死鎖、選對隔離級別,是寫出健壯資料庫應用的核心功力。