萬盛學電腦網

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

MySQL 事務完整指南:ACID 特性、鎖機制、隔離級別、死鎖處理與 PHP 實戰封裝

事務四大特性 (ACID)

⚠️ 僅 InnoDB 支援事務,MyISAM/MEMORY 不支援。

事務控制語句

-- 開啟
START TRANSACTION;  -- 或 BEGIN

-- 提交
COMMIT;

-- 回滾
ROLLBACK;

-- 保存點
SAVEPOINT sp1;
-- ... 若干操作
ROLLBACK TO SAVEPOINT sp1;  -- 回滾到保存點
RELEASE SAVEPOINT sp1;      -- 釋放保存點

自動提交模式

SET AUTOCOMMIT = 0;  -- 關閉自動提交,後續每條 SQL 同屬一事務
SET AUTOCOMMIT = 1;  -- 開啟(預設)

-- 連線斷開時,未 COMMIT 的事務自動 ROLLBACK

鎖機制

1. 共享鎖 (S Lock) — SELECT ... LOCK IN SHARE MODE

  • 讀取當前最新提交數據
  • 允許其他事務加共享鎖,阻塞排他鎖
  • 適用:驗證資料存在後再更新

2. 排他鎖 (X Lock) — SELECT ... FOR UPDATE

  • 查詢行加排他鎖,其他事務不可讀(LOCK IN SHARE MODE)、不可寫
  • 適用:查詢後立即更新同一行(如扣庫存)

3. 意向鎖 (IS/IX) — 表級,自動加裝

  • 事務對行加 S 鎖前,先對表加 IS 鎖;加 X 鎖前先加 IX 鎖
  • 用於快速判斷表是否有行鎖,避免全表掃描檢查

4. 間隙鎖 / Next-Key Lock (RR 隔離級別)

  • 鎖定索引區間,防止幻讀
  • SELECT ... FOR UPDATE 在非唯一索引/無索引時鎖定間隙

5. 插入意向鎖

  • INSERT 時在間隙中加輕量鎖,多事務同一間隙插入不同值可並行

隔離級別

特性含義MySQL 實現
Atomicity (原子性)全做或全不做Undo Log 回滾
Consistency (一致性)資料完整性約束不被破壞Foreign Key、Check、Trigger
Isolation (隔離性)併發事務互不干擾MVCC + 鎖
Durability (持久性)提交後永久生效Redo Log (WAL)
級別髒讀不可重複讀幻讀鎖行為適用場景
READ UNCOMMITTED✅✅✅無鎖極少用
READ COMMITTED❌✅✅行鎖Oracle 預設、高併發讀多寫少
REPEATABLE READ❌❌❌*Next-Key LockMySQL 預設、一般業務
SERIALIZABLE❌❌❌表鎖/間隙鎖嚴格一致性、低併發

* RR 下透過 Next-Key Lock 解決幻讀,但非標準 SQL 定義的完全避免。

設定隔離級別

-- 下一事務生效
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

-- 全域新連線
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

死鎖處理

  • InnoDB 自動偵測死鎖,回滾代價較小的事務(權重:修改行數、鎖等待時間)
  • innodb_lock_wait_timeout = 50 (秒),超時主動回滾
  • 避免死鎖原則:
    1. 固定順序訪問表/行
    2. 事務盡量短、拆分大事務
    3. 避免長事務中有用戶交互
    4. 合理使用索引減少鎖範圍

PHP + MySQLi 事務封裝實戰

class TransactionManager {
    private mysqli $mysqli;
    private int $depth = 0;

    public function __construct(mysqli $mysqli) {
        $this->mysqli = $mysqli;
    }

    public function begin(): void {
        if ($this->depth === 0) {
            $this->mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
        }
        $this->depth++;
    }

    public function commit(): void {
        if ($this->depth <= 0) throw new Exception('No transaction to commit');
        $this->depth--;
        if ($this->depth === 0) {
            $this->mysqli->commit();
        }
    }

    public function rollback(): void {
        if ($this->depth <= 0) throw new Exception('No transaction to rollback');
        $this->depth = 0;
        $this->mysqli->rollback();
    }

    // 執行回調自動管理事務
    public function transactional(callable $callback) {
        $this->begin();
        try {
            $result = $callback($this->mysqli);
            $this->commit();
            return $result;
        } catch (Throwable $e) {
            $this->rollback();
            throw $e;
        }
    }
}

// 使用範例
$tm = new TransactionManager($mysqli);
$tm->transactional(function(mysqli $db) use ($userId, $amount) {
    // 鎖定用戶行
    $stmt = $db->prepare("SELECT balance FROM accounts WHERE user_id = ? FOR UPDATE");
    $stmt->bind_param('i', $userId);
    $stmt->execute();
    $balance = $stmt->get_result()->fetch_assoc()['balance'];
    
    if ($balance < $amount) throw new Exception('Insufficient balance');
    
    $stmt = $db->prepare("UPDATE accounts SET balance = balance - ? WHERE user_id = ?");
    $stmt->bind_param('di', $amount, $userId);
    $stmt->execute();
    
    // 記錄流水
    $stmt = $db->prepare("INSERT INTO transactions (user_id, amount, type) VALUES (?, ?, 'debit')");
    $stmt->bind_param('id', $userId, $amount);
    $stmt->execute();
});

效能調優參數

[mysqld]
innodb_buffer_pool_size = 4G          # 核心,設為實體記憶體 60-70%
innodb_log_file_size = 512M           # 大事務減少刷盤
innodb_flush_log_at_trx_commit = 1    # 1=最安全,2=高效能(掉電丟1秒)
innodb_flush_method = O_DIRECT        # 避免雙重緩衝
innodb_lock_wait_timeout = 50         # 死鎖等待上限
innodb_deadlock_detect = ON           # 自動偵測死鎖
copyright © 萬盛學電腦網 all rights reserved