萬盛學電腦網

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

MySQL 事務處理完整指南:ACID、鎖、隔離級別與實戰最佳實踐

兩種事務控制模式

模式一:顯式事務(推薦,粒度可控)

BEGIN;                 -- 或 START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;                -- 提交事務
-- 若中間失敗
ROLLBACK;              -- 回滾事務

模式二:自動提交模式切換(會話級)

SET autocommit = 0;    -- 關閉自動提交,後續所有語句隸屬同一事務
-- 執行多條 SQL...
COMMIT;                -- 確認提交
-- 或
ROLLBACK;              -- 放棄回滾
-- 注意:COMMIT/ROLLBACK 後會隱式開啟新事務

事務四大特性(ACID)

特性全稱含義InnoDB 實現機制
AAtomicity原子性:要麼全做,要麼全不做Undo Log 回滾日志
CConsistency一致性:數據從一個一致狀態變到另一個約束、外鍵、觸發器 + 原子性保障
IIsolation隔離性:並發事務互不干擾MVCC + 鎖機制(四種隔離級別)
DDurability持久性:提交後永久生效,不怕崩潰Redo Log 重做日志 + 雙寫緩衝

保存點:大事務中的「後悔藥」

SAVEPOINT sp1;
UPDATE ...;
SAVEPOINT sp2;
DELETE ...;
ROLLBACK TO SAVEPOINT sp1;  -- 回滾到 sp1,sp2 後操作作廢,sp1 前操作保留
RELEASE SAVEPOINT sp2;      -- 釋放保存點

鎖機制與並發控制

鎖模式

模式SQL 語法特性阻塞關係
共享鎖 (S鎖)SELECT ... LOCK IN SHARE MODE可讀,不可寫;兼容 S 鎖,排斥 X 鎖阻塞 X 鎖
排它鎖 (X鎖)SELECT ... FOR UPDATE不可讀(除非 READ COMMITTED),不可寫;排斥 S/X 鎖阻塞 S/X 鎖
意向鎖 (IS/IX)隱式(表級)標識事務意圖獲取行鎖,不阻塞除全表鎖外一切—

隱式加鎖操作

  • INSERT / UPDATE / DELETE:自動加行級 X 鎖。
  • SELECT ... FOR UPDATE:掃描範圍內所有行加 X 鎖(含間隙鎖,防幻讀)。
  • SELECT ... LOCK IN SHARE MODE:掃描範圍加 S 鎖。

死鎖處理

  • InnoDB 自動檢測死鎖,代價較小的事務回滾(報錯 ERROR 1213 (HY000): Deadlock found)。
  • 參數 innodb_lock_wait_timeout = 50(默認 50 秒)控制等待超時。
  • 避免死鎖:按固定順序訪問表/行、縮短事務耗時、低隔離級別。

四大隔離級別詳細對比

隔離級別髒讀不可重複讀幻讀InnoDB 實現典型場景
READ UNCOMMITTED✅ 可能✅ 可能✅ 可能無鎖,直接讀最新版本統計近似值、日志分析
READ COMMITTED❌✅ 可能✅ 可能RC:每次 SELECT 讀最新提交版本(快照讀)Oracle 默認、高併發業務
REPEATABLE READ (默認)❌❌❌ (MVCC+Next-Key Lock)RR:事務啟動時建立一致性視圖,當前讀加間隙鎖大多數業務、金融核心
SERIALIZABLE❌❌❌所有 SELECT 隱式加 S 鎖,串行執行極高一致性要求、報表

設置隔離級別

-- 僅對下一個事務生效
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 當前會話所有事務
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 所有新連接(需 SUPER 權限,持久化寫入配置文件)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

InnoDB 必備條件

  1. 存儲引擎必須為 InnoDB(MyISAM 不支持事務)。
  2. 檢查:SHOW ENGINES; 或 SHOW VARIABLES LIKE 'have_innodb';
  3. 修改現有表引擎:ALTER TABLE t ENGINE=InnoDB;

PHP 事務封裝示例

function transaction(array $sqls): bool {
    $mysqli = new mysqli("localhost", "root", "", "task");
    $mysqli->autocommit(false);
    try {
        foreach ($sqls as $sql) {
            if (!$mysqli->query($sql)) throw new Exception($mysqli->error);
        }
        $mysqli->commit();
        return true;
    } catch (Exception $e) {
        $mysqli->rollback();
        return false;
    } finally {
        $mysqli->close();
    }
}

// 使用
transaction([
    "INSERT INTO trans (id) VALUES (2)",
    "INSERT INTO trans (id) VALUES (4)"
]);

總結

MySQL 事務建立在 InnoDB + 顯式 BEGIN/COMMIT/ROLLBACK 基礎上。理解 ACID、掌握 四大隔離級別 與 鎖機制、善用 保存點 與 死鎖預防,是寫出高併發、高可靠數據庫應用的核心功力。記住:事務越短越好,索引越精準越好,隔離級別越低(滿足業務前提)越好。

copyright © 萬盛學電腦網 all rights reserved