08-事务与并发控制

事务与并发控制

事务是数据库操作的基本单位，保证数据的一致性和可靠性。

什么是事务

事务 (Transaction) 是一组 SQL 语句的逻辑单元，要么全部执行成功，要么全部回滚。

经典案例：转账

-- 张三向李四转账 1000 元
-- 这两条语句必须作为一个整体执行
 
UPDATE accounts SET balance = balance - 1000 WHERE name = '张三';
UPDATE accounts SET balance = balance + 1000 WHERE name = '李四';
 
-- 如果第一条成功，第二条失败，数据就会不一致
-- 这就是为什么需要事务

ACID 特性

事务必须满足 ACID 四大特性：

特性	英文	说明
原子性	Atomicity	事务是不可分割的最小单位，要么全部成功，要么全部失败
一致性	Consistency	事务执行前后，数据库从一个一致状态变到另一个一致状态
隔离性	Isolation	并发事务之间相互隔离，互不干扰
持久性	Durability	事务提交后，数据永久保存，即使系统崩溃也不会丢失

事务操作

基本语法

-- 开启事务
START TRANSACTION;
-- 或
BEGIN;
 
-- 执行 SQL 操作
UPDATE accounts SET balance = balance - 1000 WHERE name = '张三';
UPDATE accounts SET balance = balance + 1000 WHERE name = '李四';
 
-- 提交事务（使更改永久生效）
COMMIT;
 
-- 回滚事务（撤销所有更改）
ROLLBACK;

自动提交

-- 查看自动提交状态
SELECT @@autocommit;
 
-- 关闭自动提交
SET autocommit = 0;
 
-- 开启自动提交
SET autocommit = 1;

默认情况下，MySQL 的 autocommit 是开启的，每条 SQL 语句都会自动提交。

保存点 (Savepoint)

保存点允许在事务中设置回滚点，实现部分回滚。

START TRANSACTION;
 
INSERT INTO orders (user_id, amount) VALUES (1, 100);
SAVEPOINT sp1;
 
INSERT INTO orders (user_id, amount) VALUES (2, 200);
SAVEPOINT sp2;
 
INSERT INTO orders (user_id, amount) VALUES (3, 300);
 
-- 回滚到 sp2，撤销第三条插入
ROLLBACK TO sp2;
 
-- 回滚到 sp1，撤销第二、三条插入
ROLLBACK TO sp1;
 
-- 释放保存点
RELEASE SAVEPOINT sp1;
 
COMMIT;

并发问题

当多个事务同时访问数据库时，可能出现以下问题：

1. 脏读 (Dirty Read)

一个事务读取到另一个事务未提交的数据。

-- 事务 A                           -- 事务 B
BEGIN;
UPDATE users SET balance = 1000     
WHERE id = 1;
                                    BEGIN;
                                    SELECT balance FROM users WHERE id = 1;
                                    -- 读到 1000（未提交的数据）
ROLLBACK;
-- balance 回滚为原值
                                    -- 事务 B 读到的 1000 是脏数据

2. 不可重复读 (Non-repeatable Read)

同一事务内，多次读取同一数据得到不同结果。

-- 事务 A                           -- 事务 B
BEGIN;
SELECT balance FROM users WHERE id = 1;
-- 读到 500
                                    BEGIN;
                                    UPDATE users SET balance = 1000 WHERE id = 1;
                                    COMMIT;
SELECT balance FROM users WHERE id = 1;
-- 读到 1000（两次读取结果不同）
COMMIT;

3. 幻读 (Phantom Read)

同一事务内，多次查询返回的行数不同。

-- 事务 A                           -- 事务 B
BEGIN;
SELECT COUNT(*) FROM users WHERE age > 20;
-- 返回 5 条
                                    BEGIN;
                                    INSERT INTO users (name, age) VALUES ('新用户', 25);
                                    COMMIT;
SELECT COUNT(*) FROM users WHERE age > 20;
-- 返回 6 条（多了一条"幻影"数据）
COMMIT;

事务隔离级别

SQL 标准定义了四种隔离级别，用于解决上述并发问题：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	❌	❌	❌
READ COMMITTED	✅	❌	❌
REPEATABLE READ	✅	✅	❌
SERIALIZABLE	✅	✅	✅

✅ 表示可以防止该问题，❌ 表示可能发生

设置隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;
 
-- 设置会话级别的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- 只对下一个事务生效
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

各隔离级别详解

READ UNCOMMITTED（读未提交）

最低隔离级别，可以读取未提交的数据。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
-- 可能读到其他事务未提交的数据（脏读）

READ COMMITTED（读已提交）

只能读取已提交的数据，解决脏读问题。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- Oracle、SQL Server 默认级别
-- 每次 SELECT 都能看到最新已提交的数据

REPEATABLE READ（可重复读）

同一事务内多次读取结果一致，解决不可重复读问题。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- MySQL InnoDB 默认级别
-- 通过 MVCC 实现，在事务开始时创建快照

SERIALIZABLE（串行化）

最高隔离级别，完全串行执行，解决所有并发问题。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- 性能最差，会导致大量锁等待
-- 只在必要时使用

锁机制

锁是实现事务隔离的重要手段。

锁的类型

锁类型	说明	兼容性
共享锁 (S)	读锁，允许其他事务读	S 与 S 兼容
排他锁 (X)	写锁，不允许其他事务读写	X 与任何锁不兼容

行级锁

-- 共享锁（读锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0+
SELECT * FROM users WHERE id = 1 FOR SHARE;
 
-- 排他锁（写锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

表级锁

-- 加读锁
LOCK TABLES users READ;
 
-- 加写锁
LOCK TABLES users WRITE;
 
-- 解锁
UNLOCK TABLES;

死锁

死锁是两个或多个事务相互等待对方持有的锁。

-- 事务 A                           -- 事务 B
BEGIN;                              BEGIN;
UPDATE users SET name = 'A' 
WHERE id = 1;
                                    UPDATE users SET name = 'B' 
                                    WHERE id = 2;
UPDATE users SET name = 'A' 
WHERE id = 2;
-- 等待事务 B 释放 id=2 的锁
                                    UPDATE users SET name = 'B' 
                                    WHERE id = 1;
                                    -- 等待事务 A 释放 id=1 的锁
-- 死锁！MySQL 会自动检测并回滚其中一个事务

避免死锁

1. 按固定顺序访问表和行
2. 减小事务范围，尽快提交
3. 使用较低的隔离级别
4. 为常用查询添加索引，减少锁定范围

MVCC 多版本并发控制

InnoDB 使用 MVCC (Multi-Version Concurrency Control) 实现高效的并发控制。

工作原理

1. 每行数据都有隐藏列：创建版本号、删除版本号
2. 每个事务都有一个唯一的事务 ID
3. 读操作只读取对当前事务可见的版本
4. 写操作创建新版本，不影响其他事务读取

优势

• 读写不冲突，提高并发性能
• 实现一致性读（快照读）
• 避免了大量加锁操作

实践示例

转账事务

DELIMITER //
 
CREATE PROCEDURE transfer(
    IN from_id INT,
    IN to_id INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE from_balance DECIMAL(10,2);
    
    -- 开启事务
    START TRANSACTION;
    
    -- 检查余额（加锁）
    SELECT balance INTO from_balance 
    FROM accounts 
    WHERE id = from_id 
    FOR UPDATE;
    
    IF from_balance < amount THEN
        -- 余额不足，回滚
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    ELSE
        -- 扣款
        UPDATE accounts SET balance = balance - amount WHERE id = from_id;
        -- 加款
        UPDATE accounts SET balance = balance + amount WHERE id = to_id;
        -- 提交
        COMMIT;
    END IF;
END //
 
DELIMITER ;
 
-- 调用
CALL transfer(1, 2, 500.00);

库存扣减（防止超卖）

START TRANSACTION;
 
-- 使用 FOR UPDATE 锁定行
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
 
-- 检查库存
-- 如果库存足够
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
 
-- 检查更新是否成功
-- ROW_COUNT() 返回受影响的行数
 
COMMIT;

总结

概念	说明
ACID	原子性、一致性、隔离性、持久性
脏读	读取未提交的数据
不可重复读	同一事务内读取结果不一致
幻读	同一事务内查询行数变化
隔离级别	RU < RC < RR < Serializable
锁	共享锁 (S)、排他锁 (X)
MVCC	多版本并发控制，提高并发性能

最佳实践

1. 选择合适的隔离级别（通常 RC 或 RR）
2. 事务尽量简短，减少锁持有时间
3. 避免长事务，及时提交或回滚
4. 合理使用索引，减少锁定范围

---

上一节：07-约束与索引 下一节：09-视图与存储过程 — 视图、存储过程、触发器

---

sql 事务 acid 并发控制 锁