萬盛學電腦網

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

MySQL 事務處理核心原理與實戰避坑指南

ACID 四大特性實戰解讀

特性核心含義銀行轉帳例子MySQL 實現機制
原子性不可分割,全成全敗扣款+入帳同時成功或同時失敗Undo Log 回滾
一致性狀態正確轉換總金額守恆,無憑空增減約束、觸發器、應用邏輯
隔離性併發事務互不干擾A轉帳未提交時B查不到中間狀態MVCC + 鎖 (RR 預設)
持久性提交後永久生效系統崩潰後帳戶餘額不丟Redo Log (WAL) + flush

MySQL 事務控制語句

-- 1. 顯式事務 (autocommit=0 或 START TRANSACTION)
SET autocommit = 0;
-- 或
START TRANSACTION;

-- 2. 業務 SQL
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 3. 提交或回滾
COMMIT;
-- 或
ROLLBACK;

-- 4. 保存點 (大事務分段回滾)
SAVEPOINT sp1;
UPDATE ...;
ROLLBACK TO SAVEPOINT sp1; -- 僅回滾到保存點
RELEASE SAVEPOINT sp1;

自動提交模式陷阱

  • 預設 autocommit=ON,每條 SQL 隱式提交
  • 連線斷開重連 → 重置為 ON,舊連線設定失效
  • 最佳實踐:連線池初始化執行 SET SESSION autocommit = 0,或框架層統一管理

隱式提交陷阱 (DDL 隱式提交)

START TRANSACTION;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (id INT); -- 這裡隱式 COMMIT!
INSERT INTO t1 VALUES (2); -- 新事務
ROLLBACK; -- 僅回滾第二個 INSERT,第一個已提交!

隱式提交語句:DDL (CREATE/ALTER/DROP)、鎖語句 (LOCK TABLES)、管理語句 (ANALYZE/OPTIMIZE/REPAIR)、SET autocommit=1 等。

隔離級別與現象

隔離級別髒讀不可重複讀幻讀MySQL 預設
READ UNCOMMITTEDYYY
READ COMMITTEDNYY
REPEATABLE READNNN* (間隙鎖)✓
SERIALIZABLENNN

* InnoDB RR 以 Next-Key Lock 解決幻讀。

Python 操作事務範本

import MySQLdb
conn = MySQLdb.connect(host="localhost", user="root", passwd="pass", db="dbname")
try:
    cur = conn.cursor()
    cur.execute("SET autocommit=0")
    cur.execute("INSERT INTO t1 VALUES (%s)", (1,))
    cur.execute("INSERT INTO t1 VALUES (%s)", (2,))
    conn.commit()
except MySQLdb.Error as e:
    conn.rollback()
    print(f"MySQL Error {e.args[0]}: {e.args[1]}")
finally:
    cur.close()
    conn.close()

併發鎖機制對比

  • MyISAM:表鎖,寫鎖獨佔,讀寫互斥,併發極差
  • InnoDB:行鎖 (索引上加鎖),讀寫不阻塞 (MVCC),高併發首選

多語句非原子操作解決方案

  1. 顯式鎖表:LOCK TABLES t WRITE; ... UNLOCK TABLES; (僅 MyISAM/InnoDB 表鎖模式)
  2. SELECT ... FOR UPDATE:行鎖 + 悲觀鎖,事務內執行
  3. 相對更新:UPDATE t SET qty = qty - 1 WHERE id = 1 AND qty > 0 避免「查-改」非原子
  4. 樂觀鎖:version 欄位,UPDATE ... SET x=..., version=version+1 WHERE id=1 AND version=old
copyright © 萬盛學電腦網 all rights reserved