第11课: 事务处理

ACID特性、事务隔离级别、锁机制

事务基础概念

事务的ACID特性

特性	说明	重要性
Atomicity（原子性）	事务是不可分割的工作单元，要么全部成功，要么全部失败回滚	确保数据操作的完整性，避免部分执行导致的数据不一致
Consistency（一致性）	事务执行前后，数据库从一个一致性状态转变为另一个一致性状态	保证业务规则和数据完整性约束不被破坏
Isolation（隔离性）	多个事务并发执行时，一个事务的执行不应影响其他事务的执行	防止并发事务相互干扰，确保数据一致性
Durability（持久性）	事务一旦提交，其结果就是永久性的，即使系统崩溃也不会丢失	保证数据的持久存储，防止数据丢失

事务控制语句

-- 示例1: 开始事务
START TRANSACTION;
BEGIN;  -- 简写形式

-- 示例2: 提交事务
COMMIT;

-- 示例3: 回滚事务
ROLLBACK;

-- 示例4: 保存点
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT transfer;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 如果出错，可以回滚到保存点
ROLLBACK TO transfer;
-- 否则提交
COMMIT;

自动提交模式

-- 查看自动提交状态
SHOW VARIABLES LIKE 'autocommit';

-- 禁用自动提交
SET autocommit = 0;

-- 启用自动提交
SET autocommit = 1;

事务隔离级别

事务隔离级别定义了并发事务之间的隔离程度，不同的隔离级别会影响并发性能和数据一致性。

隔离级别	脏读	不可重复读	幻读	并发性能	适用场景
READ UNCOMMITTED（读未提交）	可能	可能	可能	最高	对数据一致性要求低的场景
READ COMMITTED（读已提交）	不可能	可能	可能	高	大多数应用场景
REPEATABLE READ（可重复读）	不可能	不可能	可能	中	对数据一致性要求较高的场景
SERIALIZABLE（串行化）	不可能	不可能	不可能	最低	对数据一致性要求极高的场景

设置隔离级别

-- 会话级别设置
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

-- 查看当前隔离级别
SELECT @@tx_isolation;
SELECT @@global.tx_isolation;

-- MySQL 8.0+
SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;

并发问题

-- 脏读（Dirty Read）：读取到其他事务未提交的数据
-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 未提交

-- 事务B
SELECT balance FROM accounts WHERE id = 1;  -- 读取到未提交的数据

-- 不可重复读（Non-repeatable Read）：同一事务中多次读取同一数据，结果不同
-- 事务A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 事务A
SELECT balance FROM accounts WHERE id = 1;  -- 900（与第一次读取不同）
COMMIT;

-- 幻读（Phantom Read）：同一事务中多次查询同一范围的数据，结果集不同
-- 事务A
START TRANSACTION;
SELECT COUNT(*) FROM accounts WHERE balance > 500;  -- 5

-- 事务B
START TRANSACTION;
INSERT INTO accounts (balance) VALUES (600);
COMMIT;

-- 事务A
SELECT COUNT(*) FROM accounts WHERE balance > 500;  -- 6（与第一次读取不同）
COMMIT;

锁机制

MySQL使用锁来实现事务的隔离性，不同的存储引擎和隔离级别使用不同的锁策略。

锁的类型

锁类型	说明	适用场景
共享锁（S锁）	读锁，多个事务可以同时持有	SELECT语句
排他锁（X锁）	写锁，只允许一个事务持有	INSERT、UPDATE、DELETE语句
行锁	锁定单行数据	InnoDB存储引擎
表锁	锁定整个表	MyISAM存储引擎、某些DDL操作
意向锁	表级锁，表示事务将要对表中的行加锁	InnoDB存储引擎

显式加锁

-- 共享锁（读锁）
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（写锁）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 表级锁
LOCK TABLES accounts WRITE;
-- 操作完成后解锁
UNLOCK TABLES;

死锁

-- 死锁示例
-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 锁定id=1的行

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- 锁定id=2的行

-- 事务A
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待事务B释放id=2的锁

-- 事务B
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- 等待事务A释放id=1的锁
-- 此时发生死锁

死锁预防与解决

事务最佳实践

实战场景：转账操作

-- 完整的转账事务示例
START TRANSACTION;

-- 检查余额
SELECT balance INTO @balance FROM accounts WHERE id = 1 FOR UPDATE;
IF @balance < 100 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
END IF;

-- 扣减转出账户
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 增加转入账户
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 记录转账日志
INSERT INTO transfer_logs (from_account, to_account, amount, transfer_time)
VALUES (1, 2, 100, NOW());

-- 提交事务
COMMIT;

-- 如果出错，自动回滚
-- ROLLBACK;

事务性能优化