事务与并发控制

第十三章：事务与并发控制

💡 事务是数据库操作的基本单位，并发控制保证多用户同时访问时的数据一致性

13.1 事务概念

13.1.1 什么是事务

定义：事务是作为单个逻辑工作单元执行的一系列操作，这些操作要么全部成功，要么全部失败。

image-67bb16d7

13.1.2 事务的四大特性（ACID）

特性 英文 说明 保证方式
原子性 Atomicity 事务中的操作要么全部完成，要么全部不完成 Undo Log
一致性 Consistency 事务前后，数据库从一个一致状态转到另一个一致状态 其他三个特性保证
隔离性 Isolation 并发事务之间相互隔离，互不干扰 锁机制、MVCC
持久性 Durability 事务提交后，修改永久保存 Redo Log
image-b7d455db

13.2 事务控制语句

13.2.1 基本语法

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT 保存点名称;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT 保存点名称;

-- 释放保存点
RELEASE SAVEPOINT 保存点名称;

13.2.2 事务示例：银行转账

-- 转账场景：从账户A向账户B转账500元

-- 开始事务
START TRANSACTION;

-- 检查账户A余额
SELECT 余额 INTO @balance FROM 账户 WHERE 账户号 = 'A';

-- 如果余额不足
IF @balance < 500 THEN
    SELECT '余额不足' AS 结果;
    ROLLBACK;  -- 回滚事务
ELSE
    -- 从账户A扣款
    UPDATE 账户 SET 余额 = 余额 - 500 WHERE 账户号 = 'A';

    -- 向账户B存款
    UPDATE 账户 SET 余额 = 余额 + 500 WHERE 账户号 = 'B';

    -- 提交事务
    COMMIT;
    SELECT '转账成功' AS 结果;
END IF;

13.2.3 保存点示例

START TRANSACTION;

INSERT INTO 订单 VALUES (1, '客户A', NOW());
SAVEPOINT sp1;  -- 创建保存点1

INSERT INTO 订单明细 VALUES (1, '商品1', 10);
SAVEPOINT sp2;  -- 创建保存点2

INSERT INTO 订单明细 VALUES (1, '商品2', 20);

-- 如果第三个操作出问题，回滚到保存点2
ROLLBACK TO SAVEPOINT sp2;

-- 如果需要回滚更多，可以回滚到保存点1
-- ROLLBACK TO SAVEPOINT sp1;

-- 最终提交
COMMIT;

13.3 自动提交模式

13.3.1 查看和设置自动提交

-- 查看当前自动提交状态
SELECT @@autocommit;
SHOW VARIABLES LIKE 'autocommit';

-- 关闭自动提交
SET autocommit = 0;

-- 开启自动提交（默认）
SET autocommit = 1;

13.3.2 自动提交 vs 显式事务

模式 说明 使用场景
自动提交（默认） 每条SQL语句自动成为一个事务 单条语句操作
显式事务 使用BEGIN/COMMIT/ROLLBACK控制 多条语句需要原子操作
关闭自动提交 每次操作需要手动COMMIT 批量操作、需要回滚控制

13.4 事务隔离级别

13.4.1 并发问题

问题 说明 示例
脏读 读取到其他事务未提交的数据 A修改数据但未提交，B读取到了
不可重复读 同一事务内两次读取结果不同 A两次读取之间，B修改并提交了
幻读 同一事务内两次查询结果集不同 A两次查询之间，B插入了新数据

13.4.2 四种隔离级别

隔离级别 脏读 不可重复读 幻读 性能
READ UNCOMMITTED ✓可能 ✓可能 ✓可能 最高
READ COMMITTED ✗避免 ✓可能 ✓可能 较高
REPEATABLE READ（MySQL默认） ✗避免 ✗避免 ✓可能 中等
SERIALIZABLE ✗避免 ✗避免 ✗避免 最低

13.4.3 设置隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 或（旧版本）
SELECT @@tx_isolation;

-- 设置会话级别隔离
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 只对下一个事务生效
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

13.4.4 隔离级别示例

-- 演示脏读（需要两个会话）

-- 会话1：设置最低隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM 账户 WHERE 账户号 = 'A';  -- 假设余额1000

-- 会话2：修改数据但不提交
START TRANSACTION;
UPDATE 账户 SET 余额 = 500 WHERE 账户号 = 'A';
-- 不提交！

-- 会话1：再次读取
SELECT * FROM 账户 WHERE 账户号 = 'A';  -- 读到500（脏读！）

-- 会话2：回滚
ROLLBACK;

-- 会话1：此时账户A实际余额还是1000，但会话1已经读到了500

13.5 锁机制

13.5.1 锁的类型

锁类型 说明 兼容性
共享锁（S锁/读锁） 允许多个事务同时读取 与其他S锁兼容
排他锁（X锁/写锁） 只允许一个事务独占 与任何锁都不兼容

13.5.2 锁的粒度

粒度 说明 优点 缺点
表锁 锁定整个表 开销小，加锁快 并发度低
行锁 锁定单行 并发度高 开销大，可能死锁

13.5.3 InnoDB 行锁

-- 共享锁（读锁）
SELECT * FROM 表名 WHERE 条件 LOCK IN SHARE MODE;
-- MySQL 8.0+ 新语法
SELECT * FROM 表名 WHERE 条件 FOR SHARE;

-- 排他锁（写锁）
SELECT * FROM 表名 WHERE 条件 FOR UPDATE;

-- 示例
START TRANSACTION;
SELECT * FROM 账户 WHERE 账户号 = 'A' FOR UPDATE;  -- 加排他锁
UPDATE 账户 SET 余额 = 余额 - 100 WHERE 账户号 = 'A';
COMMIT;

13.5.4 死锁

image-3d23ed5f

死锁检测和处理：

-- 查看InnoDB状态（包含死锁信息）
SHOW ENGINE INNODB STATUS\G

-- 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看当前锁
SELECT * FROM information_schema.INNODB_LOCKS;

-- MySQL会自动检测死锁，并回滚其中一个事务

13.6 事务最佳实践

13.6.1 事务设计原则

原则 说明
事务尽量短 长事务会占用资源，增加锁冲突
避免交互式事务 不要在事务中等待用户输入
合理选择隔离级别 根据业务需求选择适当的隔离级别
处理死锁 设计时避免死锁，程序中处理死锁异常
使用索引 避免表锁，减少锁范围

13.6.2 事务示例：完整的转账存储过程

DELIMITER //
CREATE PROCEDURE transfer(
    IN from_account VARCHAR(20),
    IN to_account VARCHAR(20),
    IN amount DECIMAL(10,2),
    OUT result VARCHAR(50)
)
BEGIN
    DECLARE from_balance DECIMAL(10,2);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result = '转账失败：系统错误';
    END;

    -- 开始事务
    START TRANSACTION;

    -- 加锁查询余额（避免并发问题）
    SELECT 余额 INTO from_balance
    FROM 账户
    WHERE 账户号 = from_account
    FOR UPDATE;

    -- 检查余额
    IF from_balance IS NULL THEN
        SET result = '转账失败：账户不存在';
        ROLLBACK;
    ELSEIF from_balance < amount THEN
        SET result = '转账失败：余额不足';
        ROLLBACK;
    ELSE
        -- 扣款
        UPDATE 账户 SET 余额 = 余额 - amount
        WHERE 账户号 = from_account;

        -- 存款
        UPDATE 账户 SET 余额 = 余额 + amount
        WHERE 账户号 = to_account;

        -- 提交
        COMMIT;
        SET result = '转账成功';
    END IF;
END //
DELIMITER ;

-- 调用
CALL transfer('A', 'B', 500, @result);
SELECT @result;

13.7 快速参考

-- 事务控制
START TRANSACTION;  -- 或 BEGIN;
COMMIT;
ROLLBACK;
SAVEPOINT 名称;
ROLLBACK TO SAVEPOINT 名称;

-- 自动提交
SELECT @@autocommit;
SET autocommit = 0;

-- 隔离级别
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 锁
SELECT ... LOCK IN SHARE MODE;  -- 共享锁
SELECT ... FOR UPDATE;          -- 排他锁

⬅️ 用户管理与权限控制 🏠 00-数据库 ➡️ 01-PostgreSQL 基础与 MySQL 对照