MySQL 交易處理完整指南：ACID、自動提交、START TRANSACTION、鎖、隔離級別、Python 實作

MySQL 交易處理核心概念與實戰：ACID 四特性、autocommit 模式、顯式交易控制、DDL 隱式提交、行鎖機制、隔離級別、Python 連線池交易範本、相對更新避免競態。

ACID 四大特性（以銀行轉帳為例）

引擎支援：必須用 InnoDB

CREATE TABLE t (...) ENGINE=InnoDB;
-- MyISAM 不支援交易

自動提交模式

• 預設 autocommit=ON：每句 SQL 即時提交
• 關閉：SET autocommit=0（僅當前連線生效，斷線重連恢復預設）
• 查看：SELECT @@autocommit

交易控制兩種模式

模式 A：autocommit=ON（預設）+ 顯式交易

START TRANSACTION;
UPDATE account SET balance=balance-100 WHERE id=1;
UPDATE account SET balance=balance+100 WHERE id=2;
COMMIT;  -- 或 ROLLBACK;

模式 B：autocommit=OFF + 隱式交易

SET autocommit=0;
UPDATE account SET balance=balance-100 WHERE id=1;
UPDATE account SET balance=balance+100 WHERE id=2;
COMMIT;  -- 此處才真正提交
-- 下一筆 SQL 自動開啟新交易

⚠️ DDL 會隱式提交（無法納入交易）

START TRANSACTION;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (i INT);  -- 這裡會自動 COMMIT 前面的 INSERT
INSERT INTO t1 VALUES (2);
ROLLBACK;  -- 只能回滾第二個 INSERT，第一個已提交、t2 已建立

受影響語句：CREATE/ALTER/DROP DATABASE/TABLE、TRUNCATE、LOCK/UNLOCK TABLES、SET AUTOCOMMIT 等。

Python 交易範本（MySQLdb/PyMySQL）

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='pass', db='dbname')
try:
    with conn.cursor() as cur:
        cur.execute('SET autocommit=0')
        cur.execute('INSERT INTO t1 VALUES (%s)', (1,))
        cur.execute('INSERT INTO t1 VALUES (%s)', (2,))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f'Error: {e}')
finally:
    conn.close()

行鎖與並發

• InnoDB：行鎖，不同列並發修改無阻塞
• 同一列：先到先鎖，釋放後下一個才能進
• MyISAM：表鎖，同表寫入串行

隔離級別

InnoDB 預設 REPEATABLE READ，同一交易內重複 SELECT 結果一致（MVCC 快照）。

避免多語句非原子操作：相對更新

-- ❌ 絕對更新：讀取→計算→寫入，中間可能被改
UPDATE account SET balance = 100 WHERE id = 1;

-- ✅ 相對更新：單一語句原子操作
UPDATE account SET balance = balance - 50 WHERE id = 1;

顯式鎖表（最後手段）

LOCK TABLES account WRITE;
-- 多語句操作
UNLOCK TABLES;