第9课: 事务处理

ACID特性、事务控制、隔离级别

事务基础概念

事务是一组SQL操作的集合，这些操作要么全部成功，要么全部失败，保证数据的一致性。

ACID 特性

特性	英文	说明
原子性	Atomicity	事务中的所有操作要么全部完成，要么全部不完成
一致性	Consistency	事务前后数据的完整性必须保持一致
隔离性	Isolation	多个事务并发执行时，相互之间不受影响
持久性	Durability	事务提交后，对数据的修改是永久的

事务控制语句

事务执行流程示意图

-- 示例1: 基本事务操作
START TRANSACTION;  -- 或 BEGIN
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;  -- 提交事务

-- 示例2: 事务回滚
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
-- 发现错误，回滚事务
ROLLBACK;

-- 示例3: 转账事务（经典案例）
START TRANSACTION;

-- 检查余额
SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;

-- 扣款
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;

-- 到账
UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;

-- 记录转账日志
INSERT INTO transfer_log (from_user, to_user, amount, transfer_time)
VALUES (1, 2, 500, NOW());

COMMIT;

事务隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	不可能	可能	可能
REPEATABLE READ（默认）	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能

-- 示例4: 查看和设置隔离级别
-- 查看当前隔离级别
SELECT @@transaction_isolation;

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

锁机制

-- 示例5: 共享锁（读锁）
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 LOCK IN SHARE MODE;
-- 其他事务可以读，但不能写
COMMIT;

-- 示例6: 排他锁（写锁）
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;
-- 其他事务不能读也不能写
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;

死锁处理

-- 示例7: 可能导致死锁的场景
-- 事务1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- 等待获取 user_id=2 的锁
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

-- 事务2（同时执行）
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE user_id = 2;
-- 等待获取 user_id=1 的锁（死锁！）
UPDATE accounts SET balance = balance + 50 WHERE user_id = 1;
COMMIT;

-- 示例8: 避免死锁的方法
-- 按固定顺序访问资源
START TRANSACTION;
-- 总是先锁 user_id 小的记录
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

实战场景：电商下单事务

-- 示例9: 完整的下单流程
START TRANSACTION;

-- 1. 检查商品库存（加锁）
SELECT stock FROM products
WHERE product_id = 100 FOR UPDATE;

-- 2. 扣减库存
UPDATE products
SET stock = stock - 1
WHERE product_id = 100 AND stock > 0;

-- 3. 检查更新是否成功
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    -- 库存不足
END IF;

-- 4. 创建订单
INSERT INTO orders (user_id, total_amount, status, order_date)
VALUES (1001, 299.00, 'pending', NOW());

-- 5. 获取订单ID
SET @order_id = LAST_INSERT_ID();

-- 6. 创建订单明细
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 100, 1, 299.00);

-- 7. 扣减用户余额
UPDATE user_accounts
SET balance = balance - 299.00
WHERE user_id = 1001 AND balance >= 299.00;

-- 8. 提交事务
COMMIT;

-- 示例10: 批量操作事务
START TRANSACTION;

-- 批量更新商品价格
UPDATE products
SET price = price * 0.9
WHERE category = '电子产品' AND stock > 0;

-- 记录价格调整日志
INSERT INTO price_history (product_id, old_price, new_price, change_date)
SELECT product_id, price / 0.9, price, NOW()
FROM products
WHERE category = '电子产品' AND stock > 0;

COMMIT;