mysql executeupdate MySQL 系列：第14篇 事务与锁入门

mysql executeupdate MySQL 系列：第14篇 事务与锁入门

IT策士 10余年一线大厂经验，专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章，助你少走弯路。

前面的文章里我们多次提到“提交”“回滚”，但从未系统讲解它们背后的核心机制——事务。多用户同时操作时，如何保证数据不错乱？转账扣钱与加钱如何做到同生共死？索引优化查询，事务与锁则保障并发下的数据一致性。今天我们用 配合实战，入门这一关键主题。

1. 准备数据：账户表模拟转账场景

import mysql.connector
import time
import threading
conn = mysql.connector.connect(
    host="127.0.0.1", port=3306,
    user="root", password="MyNewPass123!",
    database="shop"
)
cursor = conn.cursor()
# 账户表
cursor.execute("""
CREATE TABLE IF NOT EXISTS accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_name VARCHAR(50) NOT NULL UNIQUE,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB
""")
cursor.execute("TRUNCATE accounts")
accounts = [
    ("张三", 1000.00),
    ("李四", 500.00),
    ("王五", 2000.00),
]
cursor.executemany("INSERT INTO accounts (account_name, balance) VALUES (%s, %s)", accounts)
conn.commit()
print(" 账户数据准备完毕")

2. ACID：事务的四大特性

事务是一组不可分割的操作，要么全部成功，要么全部失败回滚。它由 ACID 四个特性定义：

通过 undo log 实现原子性和回滚，通过 redo log 实现持久性，通过 锁 + MVCC 实现隔离性。

3. 事务的基本操作： 与

MySQL 默认是自动提交模式，每条 SQL 都是一个独立的事务。要进行多步事务控制mysql executeupdate MySQL 系列：第14篇 事务与锁入门，需要显式开始事务。

3.1 中的事务控制

# 关闭自动提交，手动控制事务
conn.autocommit = False
try:
    # 张三扣款 200
    cursor.execute("UPDATE accounts SET balance = balance - 200 WHERE account_name = '张三'")
    print(f"扣款后张三余额: ", end="")
    cursor.execute("SELECT balance FROM accounts WHERE account_name = '张三'")
    print(cursor.fetchone()[0])
    
    # 李四加款 200
    cursor.execute("UPDATE accounts SET balance = balance + 200 WHERE account_name = '李四'")
    cursor.execute("SELECT balance FROM accounts WHERE account_name = '李四'")
    print(f"到账后李四余额: {cursor.fetchone()[0]}")
    
    # 模拟故障：如果李四余额超过 1000 就回滚（演示用）
    cursor.execute("SELECT balance FROM accounts WHERE account_name = '李四'")
    if cursor.fetchone()[0] > 1000:
        raise Exception("李四余额异常！触发回滚")
    
    conn.commit()
    print(" 事务提交成功")
except Exception as e:
    conn.rollback()
    print(f" 事务回滚: {e}")
# 查看最终余额
cursor.execute("SELECT account_name, balance FROM accounts")
print("n 最终余额：")
for row in cursor.fetchall():
    print(f"  {row[0]}: ¥{row[1]}")

预期输出：

扣款后张三余额: 800.00
到账后李四余额: 700.00
 事务提交成功
 最终余额：
  张三: ¥800.00
  李四: ¥700.00
  王五: ¥2000.00

3.2 事务回滚的原子性验证

故意制造异常，观察回滚效果：

# 恢复原始数据
cursor.execute("UPDATE accounts SET balance = 1000 WHERE account_name = '张三'")
cursor.execute("UPDATE accounts SET balance = 500 WHERE account_name = '李四'")
conn.commit()
try:
    cursor.execute("UPDATE accounts SET balance = balance - 200 WHERE account_name = '张三'")
    print(" 张三已扣款 200")
    
    # 故意写错表名制造异常
    cursor.execute("UPDATE accountsss SET balance = balance + 200 WHERE account_name = '李四'")
    
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f" 回滚: {e}")
# 验证张三的钱是否回来了
cursor.execute("SELECT balance FROM accounts WHERE account_name = '张三'")
print(f"张三余额: ¥{cursor.fetchone()[0]}")

预期输出：

 张三已扣款 200
 回滚: 1146 (42S02): Table 'shop.accountsss' doesn't exist
张三余额: ¥1000.00

张三的扣款被撤销了，这就是原子性——中间的失败导致整个事务回滚到初始状态。

4. 并发问题与锁的直观感受

当多个事务同时操作同一行数据时，MySQL 会使用锁来保证隔离性。

4.1 行锁演示：两个线程同时修改同一行

我们用一个 脚本模拟两个用户同时给张三转账：

conn.autocommit = False
def transfer_from_a():
    """线程 A：从张三转出 100"""
    conn_a = mysql.connector.connect(
        host="127.0.0.1", port=3306,
        user="root", password="MyNewPass123!",
        database="shop"
    )
    conn_a.autocommit = False
    cursor_a = conn_a.cursor()
    
    print(" 线程A 开始，尝试锁定张三...")
    cursor_a.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = '张三'")
    print(" 线程A 获得锁，张三余额已扣减")
    time.sleep(3)  # 持有锁 3 秒，模拟业务处理
    conn_a.commit()
    print(" 线程A 提交完毕，释放锁")
    cursor_a.close()
    conn_a.close()
def transfer_from_b():
    """线程 B：也从张三转出 200"""
    time.sleep(0.5)  # 确保 A 先拿到锁
    conn_b = mysql.connector.connect(
        host="127.0.0.1", port=3306,
        user="root", password="MyNewPass123!",
        database="shop"
    )
    conn_b.autocommit = False
    cursor_b = conn_b.cursor()
    
    print(" 线程B 开始，尝试锁定张三...")
    start = time.time()
    cursor_b.execute("UPDATE accounts SET balance = balance - 200 WHERE account_name = '张三'")
    elapsed = time.time() - start
    print(f" 线程B 获得锁（等待了 {elapsed:.1f} 秒），张三余额已扣减")
    conn_b.commit()
    print(" 线程B 提交完毕")
    cursor_b.close()
    conn_b.close()
# 恢复余额
cursor.execute("UPDATE accounts SET balance = 1000 WHERE account_name = '张三'")
conn.commit()
print(f"初始张三余额: ¥1000.00n")
# 启动两个线程
t1 = threading.Thread(target=transfer_from_a)
t2 = threading.Thread(target=transfer_from_b)
t1.start()
t2.start()
t1.join()
t2.join()
# 查看最终余额
cursor.execute("SELECT balance FROM accounts WHERE account_name = '张三'")
print(f"n最终张三余额: ¥{cursor.fetchone()[0]}")

预期输出：

初始张三余额: ¥1000.00
 线程A 开始，尝试锁定张三...
 线程A 获得锁，张三余额已扣减
 线程B 开始，尝试锁定张三...
 线程A 提交完毕，释放锁
 线程B 获得锁（等待了 2.5 秒），张三余额已扣减
 线程B 提交完毕
最终张三余额: ¥700.00

关键观察：线程 B 被阻塞了约 2.5 秒（等待 A 释放锁），最终结果是 1000 – 100 – 200 = 700，数据完全正确mysql executeupdate，没有发生混乱。

4.2 表锁： vs

默认使用行锁mysql executeupdate MySQL 系列：第14篇 事务与锁入门，而 使用表锁——一个会话更新时，其他会话连查询都要等待。

# 演示 InnoDB 下读不阻塞读（MVCC 的功劳）
def read_account():
    conn_r = mysql.connector.connect(
        host="127.0.0.1", port=3306,
        user="root", password="MyNewPass123!",
        database="shop"
    )
    cursor_r = conn_r.cursor()
    start = time.time()
    cursor_r.execute("SELECT * FROM accounts WHERE account_name = '张三'")
    row = cursor_r.fetchone()
    elapsed = time.time() - start
    print(f" 读取张三余额: ¥{row[2]} (耗时 {elapsed:.4f} 秒，未被阻塞)")
    cursor_r.close()
    conn_r.close()
# 主线程开启事务并更新（不提交）
conn.autocommit = False
cursor.execute("UPDATE accounts SET balance = 9999 WHERE account_name = '张三'")
print(" 主事务已更新张三，未提交")
# 另一个线程尝试读取
t_read = threading.Thread(target=read_account)
t_read.start()
t_read.join()
conn.rollback()
print(" 主事务已回滚")

预期输出：

 主事务已更新张三，未提交
 读取张三余额: ¥700.00 (耗时 0.0012 秒，未被阻塞)
 主事务已回滚

即使主事务未提交，读操作仍瞬间完成并返回旧值（700 而非 9999）。这就是 MVCC（多版本并发控制） 的功劳：写不阻塞读，读不阻塞写。后续文章会深入 MVCC 原理。

5. … FOR ：主动加锁

有时我们需要“先读再改”，并且这中间不希望别人修改。这时用 … FOR 锁定读取的行：

conn.autocommit = False
# 锁定张三的行
cursor.execute("SELECT balance FROM accounts WHERE account_name = '张三' FOR UPDATE")
current_balance = cursor.fetchone()[0]
print(f"当前张三余额: ¥{current_balance}")
# 此时另一个线程若想修改张三，会被阻塞
# （可以自己在另一个终端或线程中测试）
if current_balance >= 300:
    cursor.execute("UPDATE accounts SET balance = balance - 300 WHERE account_name = '张三'")
    conn.commit()
    print(" 扣款 300 成功")
else:
    conn.rollback()
    print(" 余额不足")

应用场景：

6. 死锁初体验

死锁是指两个事务互相等待对方持有的锁，形成循环等待。

def op_a():
    conn_a = mysql.connector.connect(
        host="127.0.0.1", port=3306,
        user="root", password="MyNewPass123!",
        database="shop"
    )
    conn_a.autocommit = False
    c = conn_a.cursor()
    c.execute("UPDATE accounts SET balance = balance - 10 WHERE account_name = '张三'")
    print(" A 锁定张三")
    time.sleep(0.5)
    c.execute("UPDATE accounts SET balance = balance + 10 WHERE account_name = '李四'")
    print(" A 尝试锁定李四")
    conn_a.commit()
    c.close()
    conn_a.close()
def op_b():
    conn_b = mysql.connector.connect(
        host="127.0.0.1", port=3306,
        user="root", password="MyNewPass123!",
        database="shop"
    )
    conn_b.autocommit = False
    c = conn_b.cursor()
    c.execute("UPDATE accounts SET balance = balance - 10 WHERE account_name = '李四'")
    print(" B 锁定李四")
    time.sleep(0.5)
    c.execute("UPDATE accounts SET balance = balance + 10 WHERE account_name = '张三'")
    print(" B 尝试锁定张三")
    conn_b.commit()
    c.close()
    conn_b.close()
t1 = threading.Thread(target=op_a)
t2 = threading.Thread(target=op_b)
t1.start()
t2.start()
t1.join()
t2.join()

预期输出（ 会检测到死锁并回滚其中一个事务）：

 A 锁定张三
 B 锁定李四
 A 尝试锁定李四
 B 尝试锁定张三
 (其中一个线程抛出 Deadlock found 异常)

避免死锁的方法：

7. 动手试试：完善转账系统

基于 表mysql executeupdate，完成以下练习：

编写一个安全的转账函数：输入转出方、转入方、金额，内部使用事务 + FOR 确保一致性。模拟并发转账：用两个线程同时从张三分别转 100 元给李四和王五，验证最终余额是否正确。故意构造死锁：按照上面的死锁模式mysql executeupdate，用 SHOW 查看死锁日志。比较：关闭自动提交（=False）和不关闭自动提交的转账函数，哪种更可靠？

参考代码（第 1 题）：

def safe_transfer(conn, from_user, to_user, amount):
    conn.autocommit = False
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT balance FROM accounts WHERE account_name = %s FOR UPDATE", (from_user,))
        from_balance = cursor.fetchone()
        if not from_balance:
            raise Exception(f"用户 {from_user} 不存在")
        if from_balance[0]  {to_user} ¥{amount}")
    except Exception as e:
        conn.rollback()
        print(f" 转账失败: {e}")
    finally:
        conn.autocommit = True

8. 总结

今天我们系统入门了事务与锁：

事务与锁是并发编程的根基，下一篇我们将深入 存储引擎 的核心，理解页结构、行格式与缓冲池。下次见！

想了解更多还可以去各个平台搜索「IT策士」，一起升级 IT 思维 ！