python学习体系

Appearance

章节4：事务与锁机制 ​

一、学习目标 ​

完成本章学习后，你将能够：

  1. 理解事务 ACID 四大特性及其实现原理
  2. 区分四大隔离级别以及脏读、不可重复读、幻读的差异
  3. 掌握行锁、表锁、间隙锁的应用场景
  4. 理解 MVCC 多版本并发控制的运作机制（Undo Log + Read View）
  5. 使用 Python 的 pymysql 与 DBUtils 连接池操作 MySQL

二、核心知识点 ​

4.1 事务 ACID 四大特性 ​

定义 ​

事务 是一组不可分割的数据库操作，要么全部成功，要么全部回滚。

特性含义实现机制
Atomicity（原子性）事务中的所有操作要么全部提交，要么全部回滚Undo Log
Consistency（一致性）事务执行前后，数据库必须满足所有约束规则应用层 + 数据库约束
Isolation（隔离性）并发事务之间互不干扰锁 + MVCC
Durability（持久性）已提交事务的修改永久保存Redo Log

语法与示例 ​

sql
-- 准备测试表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);

INSERT INTO account (name, balance) VALUES
('张三', 1000.00),
('李四', 500.00);

-- 事务示例：转账操作
START TRANSACTION;   -- 或 BEGIN;

-- 张三扣款
UPDATE account SET balance = balance - 200 WHERE name = '张三';

-- 李四加款
UPDATE account SET balance = balance + 200 WHERE name = '李四';

-- 提交事务
COMMIT;

-- 模拟失败回滚
START TRANSACTION;
UPDATE account SET balance = balance - 200 WHERE name = '张三';
UPDATE account SET balance = balance + 200 WHERE name = '李四';
-- 发现异常，回滚
ROLLBACK;

-- 查看事务隔离级别
SELECT @@transaction_isolation;

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4.2 四大隔离级别与并发问题 ​

定义 ​

隔离级别 定义了事务之间如何隔离，级别越高数据一致性越强，但并发性能越低。

隔离级别对照 ​

隔离级别脏读不可重复读幻读实现方式
READ UNCOMMITTED✅ 可能✅ 可能✅ 可能不加锁
READ COMMITTED❌ 禁止✅ 可能✅ 可能行锁（每次读取最新快照）
REPEATABLE READ❌ 禁止❌ 禁止✅ 可能MVCC + 间隙锁（MySQL 默认）
SERIALIZABLE❌ 禁止❌ 禁止❌ 禁止表级锁 + 所有读加共享锁

并发问题定义 ​

问题定义
脏读事务 A 读到事务 B 未提交的数据，若 B 回滚则 A 读到的是无效数据
不可重复读事务 A 内两次读取同一行数据，结果不一致（B 修改并提交了）
幻读事务 A 内两次范围查询，结果行数不一致（B 插入并提交了新行）

语法与示例 ​

sql
-- 事务 A（左窗口）             -- 事务 B（右窗口）
-- 1. 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 2. A 开启事务                -- B 开启事务
START TRANSACTION;              START TRANSACTION;

-- 3. A 查询 balance
SELECT balance FROM account      -- B 修改但不提交
WHERE name = '张三';            UPDATE account SET balance = 9999
-- 结果: 1000                    WHERE name = '张三';

-- 4. A 再次查询 ← 读到未提    -- B 回滚
-- 交数据（脏读！）              ROLLBACK;
SELECT balance FROM account
WHERE name = '张三';
-- 结果: 9999 ❌（脏数据）

MySQL 默认隔离级别是 REPEATABLE READ，通过 MVCC 解决了不可重复读，但幻读在部分场景下仍可能发生（需间隙锁解决）。

4.3 行锁、表锁、间隙锁 ​

定义 ​

锁类型作用范围说明
表锁（Table Lock）整张表MyISAM 默认，InnoDB 也支持 LOCK TABLES
行锁（Row Lock）单行记录InnoDB 默认，基于索引实现
间隙锁（Gap Lock）索引记录之间的间隙防止幻读，只在 RR 级别生效
临键锁（Next-Key Lock）行锁 + 间隙锁InnoDB RR 级别默认锁定算法

语法与示例 ​

sql
-- 行锁（InnoDB 默认，基于索引）
START TRANSACTION;
SELECT * FROM account WHERE id = 1 FOR UPDATE;  -- 排他行锁
-- 其他事务无法修改/删除 id=1 的行

-- 表锁（显式锁定）
LOCK TABLES account WRITE;   -- 写锁：其他会话不能读也不能写
-- ... 执行操作 ...
UNLOCK TABLES;

LOCK TABLES account READ;    -- 读锁：其他会话可以读，不能写
UNLOCK TABLES;

-- 间隙锁示例（RR 级别自动生效）
-- 假设 account 表中有 id: 1, 3, 5, 10
START TRANSACTION;
SELECT * FROM account WHERE id BETWEEN 3 AND 5 FOR UPDATE;
-- 间隙锁锁住 (1,3], (3,5], (5,∞) 的间隙
-- 其他事务无法插入 id=2,4,6,... 的行

-- 查看当前锁状态
SHOW OPEN TABLES WHERE In_use > 0;
SELECT * FROM performance_schema.data_locks\G;

4.4 MVCC 多版本并发控制 ​

定义 ​

MVCC（Multi-Version Concurrency Control） 是 InnoDB 实现高并发读写的关键技术，让读不阻塞写，写不阻塞读。

核心组件 ​

组件作用
Undo Log记录旧版本数据，用于事务回滚和快照读
Read View事务开启时创建的"视图"，决定哪些版本可见
隐藏字段每行记录隐藏 DB_TRX_ID（事务ID）和 DB_ROLL_PTR（回滚指针）

可见性规则 ​

Read View 包含：
  - m_ids：正在活跃（未提交）的事务 ID 列表
  - min_trx_id：m_ids 中的最小值
  - max_trx_id：下一个待分配的事务 ID（m_ids 最大值 + 1）

判断行版本是否可见：
  - 若行的事务 ID < min_trx_id → ✅ 可见（已提交的旧事务）
  - 若行的事务 ID >= max_trx_id → ❌ 不可见（未来事务）
  - 若 min_trx_id ≤ 行事务 ID < max_trx_id:
      - 在 m_ids 中 → ❌ 不可见（未提交）
      - 不在 m_ids 中 → ✅ 可见（已提交）

示例 ​

sql
-- 开启事务 A
START TRANSACTION;
SELECT * FROM account WHERE id = 1;
-- 此时 MVCC 创建 Read View A，读到的是事务 B 开始前的版本

-- 事务 B 修改并提交
START TRANSACTION;
UPDATE account SET balance = 2000 WHERE id = 1;
COMMIT;

-- 事务 A 再次查询（快照读）
SELECT * FROM account WHERE id = 1;
-- ❗ 结果仍然是 1000（RR 级别下快照读使用同一个 Read View）
-- 如果 A 使用 SELECT ... FOR UPDATE（当前读），会读到 2000

-- 提交事务 A
COMMIT;

快照读（Snapshot Read）：普通的 SELECT，不加锁，使用 MVCC 读取历史版本。
当前读（Current Read）：SELECT ... FOR UPDATE / UPDATE / DELETE，读取最新已提交数据。

4.5 Python 数据库交互与连接池 ​

定义 ​

  • pymysql：Python 连接 MySQL 的纯 Python 驱动
  • DBUtils：数据库连接池工具，复用连接避免频繁创建

语法与示例 ​

基本 pymysql 使用 ​
python
import pymysql

# 1. 建立连接
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='your_password',
    database='school',
    charset='utf8mb4',
    autocommit=False      # 手动控制事务
)

try:
    # 2. 获取游标
    cursor = conn.cursor()

    # 3. 执行 SQL
    sql = "INSERT INTO student (name, age, class_id) VALUES (%s, %s, %s)"
    cursor.execute(sql, ('测试', 20, 1))

    # 批量插入
    data = [
        ('学生A', 21, 1),
        ('学生B', 22, 2),
        ('学生C', 23, 2)
    ]
    cursor.executemany(sql, data)

    # 4. 查询
    cursor.execute("SELECT * FROM student WHERE age > %s", (20,))
    results = cursor.fetchall()   # 返回元组列表
    for row in results:
        print(row)

    # 5. 提交事务
    conn.commit()

except Exception as e:
    conn.rollback()              # 异常回滚
    print(f"错误: {e}")

finally:
    cursor.close()
    conn.close()
DBUtils 连接池 ​
python
from DBUtils.PooledDB import PooledDB
import pymysql

# 创建连接池（全局唯一）
pool = PooledDB(
    creator=pymysql,          # 数据库驱动
    maxconnections=10,         # 最大连接数
    mincached=2,               # 最小空闲连接
    maxcached=5,               # 最大空闲连接
    blocking=True,             # 无连接时阻塞等待
    host='localhost',
    port=3306,
    user='root',
    password='your_password',
    database='school',
    charset='utf8mb4',
    autocommit=False
)

def query_student(age_threshold):
    """从连接池获取连接并查询"""
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                "SELECT id, name, age FROM student WHERE age > %s",
                (age_threshold,)
            )
            return cursor.fetchall()
    finally:
        conn.close()   # 不是真正关闭，而是归还到连接池

# 使用
students = query_student(20)
for s in students:
    print(f"ID: {s[0]}, Name: {s[1]}, Age: {s[2]}")
事务处理最佳实践 ​
python
def transfer(pool, from_id, to_id, amount):
    """带事务的转账操作"""
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            # 检查余额
            cursor.execute(
                "SELECT balance FROM account WHERE id = %s FOR UPDATE",
                (from_id,)
            )
            balance = cursor.fetchone()[0]
            if balance < amount:
                raise ValueError("余额不足")

            # 扣款
            cursor.execute(
                "UPDATE account SET balance = balance - %s WHERE id = %s",
                (amount, from_id)
            )
            # 加款
            cursor.execute(
                "UPDATE account SET balance = balance + %s WHERE id = %s",
                (amount, to_id)
            )
        conn.commit()
        return True
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

三、本章小结 ​

知识点关键要点
ACID原子性（Undo Log）→ 一致性（约束）→ 隔离性（锁+MVCC）→ 持久性（Redo Log）
隔离级别RU（脏读）→ RC（不可重复读）→ RR（幻读，默认）→ SER（串行）
锁机制行锁（基于索引）/ 表锁（显式）/ 间隙锁（RR防幻读）/ 临键锁（行锁+间隙锁）
MVCCUndo Log 存旧版本 + Read View 控制可见性 → 快照读不加锁
Python 交互pymysql 直连 / DBUtils 连接池复用 → 事务用 try-commit-except-rollback

四、练习 ​

基础练习 ​

  1. 开启两个 MySQL 客户端窗口，演示 READ UNCOMMITTED 级别的脏读场景
  2. 使用 START TRANSACTION 模拟一个银行转账事务，包含扣款和加款两步，并在异常时 ROLLBACK
  3. 在 REPEATABLE READ 级别下，演示同一事务两次 SELECT 结果一致（不可重复读被防止）
  4. 使用 Python + pymysql 编写一个批量插入 10000 条学生记录并统计耗时的脚本

进阶练习 ​

  1. 使用 DBUtils.PooledDB 实现一个 Flask/FastAPI 接口，支持并发请求下的数据库连接池复用
  2. 在 RR 级别下，开两个事务演示幻读场景（事务 A 范围查询 2 次，事务 B 插入新行并提交），并验证间隙锁能否阻止幻读
  3. 画出 MVCC 的 Read View 判断流程图，并用 SQL 模拟三种可见性场景

Python 学习资料

仅供学习使用