SQL 写入与事务隔离

第 4 章 · DML、事务 ACID 与隔离级别

本章目标：掌握 INSERT / UPDATE / DELETE 语法与约束冲突处理；理解 ACID 与 InnoDB 事务；BEGIN / COMMIT / ROLLBACK；隔离级别 READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE；SAVEPOINT 部分回滚；死锁概念与排查；在 shop_db 模拟下单写多表；对照 django-web @transaction.atomic 与 fastapi-web Session 事务。

学时建议：4～5 小时（含 2 小时双会话实验）

前置：python-database ch02～ch03；样本表与数据已就绪。

4.1 场景说明：下单必须「全写或全不写」

虚构接口 POST https://api.example.com/api/v1/orders 需原子完成：

1. 插入 orders 一行（user_id、status=pending、total_amount）；
2. 插入多条 order_lines（product_id、quantity、unit_price 快照）；
3. （扩展）扣减库存、改 status=paid——任一步失败则全部撤销。

本章用 纯 SQL 事务 演练；ORM 事务边界见 django-web ch09、fastapi-web ch05 session.commit()。

字段约定：unit_price 取自当前 products.price（分）；users.password_hash 不参与下单 DML。

4.2 INSERT：单行、多行与 INSERT ... SELECT

USE shop_db;

-- 单行
INSERT INTO products (slug, title, category, price, is_published)
VALUES ('sql-pocket', 'SQL 口袋书', 'books', 3500, 1);

-- 多行
INSERT INTO products (slug, title, category, price, is_published) VALUES
('hdmi-cable', 'HDMI 线 2m', 'accessories', 8900, 0),
('notebook-a5', 'A5 笔记本', 'accessories', 1299, 1);

-- INSERT ... SELECT（从查询结果插入）
INSERT INTO order_lines (order_id, product_id, quantity, unit_price)
SELECT 1, p.id, 1, p.price
FROM products p
WHERE p.slug = 'notebook-a5'
  AND NOT EXISTS (
    SELECT 1 FROM order_lines ol
    WHERE ol.order_id = 1 AND ol.product_id = p.id
  );

-- 获取自增 id（同会话）
SELECT LAST_INSERT_ID() AS new_product_id;

4.3 UPDATE：条件更新与安全写法

-- 商品涨价（分）
UPDATE products
SET price = 3299
WHERE slug = 'go-handbook';

-- 批量上架某分类
UPDATE products
SET is_published = 1
WHERE category = 'accessories' AND is_published = 0;

-- 订单支付成功
UPDATE orders
SET status = 'paid'
WHERE id = 2 AND status = 'pending';

-- 务必带 WHERE（除非全表更新 intentional）
-- UPDATE products SET is_published = 0;  -- 危险！

检查影响行数：

UPDATE orders SET status = 'cancelled' WHERE id = 9999;
SELECT ROW_COUNT() AS affected;  -- 0 表示未更新到

乐观锁选修（版本号，框架常用）：

ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 0;

UPDATE products
SET price = 3599, version = version + 1
WHERE slug = 'go-handbook' AND version = 2;
-- affected=0 表示并发冲突，应用层重试

4.4 DELETE 与 ON DELETE 行为预习

-- 删除未上架且无订单行的商品
DELETE p FROM products p
LEFT JOIN order_lines ol ON ol.product_id = p.id
WHERE p.is_published = 0 AND ol.id IS NULL AND p.slug = 'hdmi-cable';

-- 仅删测试行
DELETE FROM order_lines WHERE order_id = 999;

-- 清空表（极危险，教学禁用 TRUNCATE 外键表）
-- TRUNCATE TABLE order_lines;

ch05 将定义 FOREIGN KEY ... ON DELETE RESTRICT/CASCADE；删除 orders 前须先删 order_lines。

4.5 ACID 与 InnoDB 事务基础

START TRANSACTION;  -- 或 BEGIN;

INSERT INTO orders (user_id, status, total_amount)
VALUES (1, 'pending', 1299);

SET @oid = LAST_INSERT_ID();

INSERT INTO order_lines (order_id, product_id, quantity, unit_price)
SELECT @oid, id, 1, price FROM products WHERE slug = 'notebook-a5';

UPDATE orders SET total_amount = (
  SELECT SUM(quantity * unit_price) FROM order_lines WHERE order_id = @oid
) WHERE id = @oid;

COMMIT;
-- ROLLBACK;  -- 若任一步出错则回滚

autocommit：默认每条语句自动提交；显式事务需 START TRANSACTION。

4.6 隔离级别概览

-- 查看与设置（会话级）
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

\* InnoDB RR 通过 MVCC + gap lock 减少幻读；不是 SQL 标准完全杜绝。

脏读实验（两个 mysql 会话 A/B）：

会话 A                          会话 B
START TRANSACTION;
UPDATE products SET price=1
  WHERE slug='go-handbook';
                                SET SESSION ... READ UNCOMMITTED;
                                START TRANSACTION;
                                SELECT price ...;  -- 可能读到 1（脏读）
ROLLBACK;
                                -- B 读到的价格无效

RC vs RR：RC 每次 SELECT 新快照；RR 同一事务内 repeatable。

4.7 SAVEPOINT 部分回滚

START TRANSACTION;

INSERT INTO orders (user_id, status, total_amount) VALUES (1, 'pending', 5000);
SET @oid = LAST_INSERT_ID();

SAVEPOINT after_order;

INSERT INTO order_lines (order_id, product_id, quantity, unit_price)
VALUES (@oid, 1, 1, 2999);

SAVEPOINT after_line1;

-- 第二条明细故意失败或业务取消
INSERT INTO order_lines (order_id, product_id, quantity, unit_price)
VALUES (@oid, 99999, 1, 100);  -- 若外键存在则失败

-- 回滚到 after_order，撤销所有明细但保留 orders
ROLLBACK TO SAVEPOINT after_order;

-- 或 RELEASE SAVEPOINT after_line1;
COMMIT;