萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 事務與回滾完整指南:原理、PHP 實戰、Auto-Increment 不回滾之謎

事務支援前提:InnoDB 引擎

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;           -- 任一失敗回滾

模式二:自動提交開關(會話級,影響後續所有 SQL)

SET autocommit = 0;  -- 關閉自動提交,開啟隱性事務
-- SQL 1
-- SQL 2
COMMIT;              -- 提交並開啟新隱性事務
-- 或
ROLLBACK;            -- 回滾並開啟新隱性事務
SET autocommit = 1;  -- 恢復自動提交模式

⚠️ autocommit=0 會影響整個連線周期,易忘記提交導致鎖持有過久,**生產環境強烈建議用顯式 BEGIN/COMMIT**。

PHP 實戰:轉帳扣款與餘額檢查回滾

<?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);

關鍵點:業務邏輯檢查(如餘額)必須在事務內執行,否則無法回滾已提交的操作。

常見問題深度解析

Q1:為什麼 AUTO_INCREMENT 不回滾?

InnoDB 自增計數器在**記憶體**中單調遞增,不持久化到磁碟。ROLLBACK 不會倒退計數器。重啟時以 SELECT MAX(id) FROM table FOR UPDATE 重新初始化。這是設計使然,**不可避免**,業務勿依賴連續無間隙 ID。

Q2:事務是物理還是邏輯回滾?

  • Redo Log(重做日誌):物理頁修改記錄,COMMIT 時強制刷盤(innodb_flush_log_at_trx_commit=1),崩潰恢復用。
  • Undo Log(回滾日誌):邏輯逆操作(INSERT→DELETE,UPDATE→反向 UPDATE),儲存於共享表空間/Undo 表空間。ROLLBACK 執行邏輯逆操作,**不回收空間**,由 purge 線程異步清理。

Q3:死鎖怎麼辦?

  • InnoDB 自動偵測死鎖,回滾代價較小的事務(返回 ERROR 1213)。
  • 應用層捕獲 1213 錯誤,**指數退避重試**。
  • 預防:固定順序訪問表/行、縮短事務、避免長事務、適當索引減少鎖範圍。

Q4:長事務危害

  • 阻塞 Purge 線程清理 Undo Log,導致歷史版本堆積、ibdata1 膨脹。
  • 持有鎖時間長,嚴重降低併發度。
  • 備份工具(mysqldump --single-transaction、xtrabackup)需等待長事務結束才能拿到一致性視圖。

隔離級別與鎖關係

隔離級別髒讀不可重複讀幻讀鎖機制
READ UNCOMMITTEDYYY無鎖
READ COMMITTEDNYY行鎖(無間隙鎖)
REPEATABLE READ (預設)NNN* (MVCC)行鎖+間隙鎖
SERIALIZABLENNN表鎖/範圍鎖

生產建議 READ COMMITTED + binlog_format=ROW/MIXED,平衡併發與一致性。

總結

MySQL 事務建立在 InnoDB Redo/Undo 雙日誌架構上。掌握 BEGIN/COMMIT/ROLLBACK 顯式控制、理解 AUTO_INCREMENT 不回滾本質、警惕長事務與死鎖、選對隔離級別,是寫出健壯資料庫應用的核心功力。

copyright © 萬盛學電腦網 all rights reserved