1. 引言
本文基于 MySQL 9.0，以珠宝行业为业务场景，系统讲解事务与并发控制的核心模式。内容涵盖建库建表、事务隔离级别、锁机制、死锁预防以及 Saga 分布式事务模式，并提供可直接运行的完整 SQL 脚本。
2. 环境准备与建库建表
介绍 MySQL 9.0 环境要求、数据库创建方式，以及珠宝行业核心业务表的设计，包括分类表、产品表、客户表、订单表、订单明细表、账户表、库存日志表、Saga 日志表和死锁演示表。
3. 基础数据准备
说明如何批量插入珠宝产品、客户等测试数据，为后续事务与并发演示提供数据基础。
4. 事务基础与隔离级别
讲解 MySQL 事务的 ACID 特性、四种隔离级别（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）在珠宝订单场景下的表现，以及脏读、不可重复读、幻读等问题的演示。
5. 锁机制与并发控制
深入分析 InnoDB 行锁、表锁、间隙锁、意向锁等机制，结合珠宝库存扣减、账户余额更新等场景，演示如何通过锁保证数据一致性，并介绍锁等待与超时处理。
6. 死锁预防与处理
通过珠宝订单与库存的交叉更新场景演示死锁的产生，总结统一加锁顺序、缩短事务长度、合理设置隔离级别等预防策略，并介绍死锁检测与重试机制。
7. Saga 模式与最终一致性
针对跨表长事务，介绍 Saga 分布式事务模式：将订单创建拆分为创建订单、扣库存、扣款、发货通知等本地事务，并实现对应的补偿操作，保证最终一致性。
8. 一致性验证与总结
提供订单金额、库存数量、Saga 状态等一致性检查脚本，并总结全文要点与后续优化方向。
-- ============================================================
-- MySQL 9.0 珠宝行业 - 事务与并发模式 完整合并脚本
-- geovindu
-- Transaction & Concurrency Patterns - Full Script
-- 一次性执行所有模块
-- ============================================================
-- 执行方式: mysql -u root -p < mysql9_full.sql
-- 或分步执行各模块文件便于调试
-- ============================================================
-- ============================================================
-- 模块: mysql9_schema.sql
-- ============================================================
-- ============================================================
-- MySQL 9.0 珠宝行业 - 建库建表
-- Transaction & Concurrency Patterns 基础表结构
-- ============================================================
-- 创建数据库
CREATE DATABASE IF NOT EXISTS jewelry_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
USE jewelry_db;
-- 删除已存在的表（注意外键依赖顺序）
DROP TABLE IF EXISTS saga_log;
DROP TABLE IF EXISTS inventory_log;
DROP TABLE IF EXISTS account_ledger;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS deadlock_demo;
-- 1. 分类表
CREATE TABLE categories (
category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL,
parent_id INT UNSIGNED DEFAULT NULL,
description VARCHAR(500) DEFAULT NULL,
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
INDEX idx_parent (parent_id),
FOREIGN KEY (parent_id) REFERENCES categories(category_id)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='珠宝分类表';
-- 2. 产品表
CREATE TABLE products (
product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category_id INT UNSIGNED NOT NULL,
material VARCHAR(50) NOT NULL COMMENT '材质：黄金/铂金/钻石/翡翠/红宝石等',
weight_gram DECIMAL(10,2) DEFAULT NULL COMMENT '重量(克)',
price DECIMAL(12,2) NOT NULL COMMENT '单价(元)',
stock_qty INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存数量',
gemstone_type VARCHAR(50) DEFAULT NULL COMMENT '宝石类型',
gemstone_carat DECIMAL(6,2) DEFAULT NULL COMMENT '宝石克拉',
color_grade VARCHAR(10) DEFAULT NULL COMMENT '颜色等级 D-E-F-G-H-I-J',
clarity_grade VARCHAR(10) DEFAULT NULL COMMENT '净度等级 FL/IF/VVS/VS/SI',
cut_grade VARCHAR(10) DEFAULT NULL COMMENT '切工等级 EX/VG/GD/Fair',
certificate VARCHAR(100) DEFAULT NULL COMMENT '证书编号 GIA/NGTC',
status ENUM('active','discontinued','reserved') NOT NULL DEFAULT 'active',
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
INDEX idx_category (category_id),
INDEX idx_material (material),
INDEX idx_status (status),
INDEX idx_product_name (product_name(50)),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='珠宝产品表';
-- 3. 客户表
CREATE TABLE customers (
customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
phone VARCHAR(20) DEFAULT NULL,
email VARCHAR(100) DEFAULT NULL,
vip_level ENUM('normal','silver','gold','platinum') NOT NULL DEFAULT 'normal',
total_spent DECIMAL(12,2) NOT NULL DEFAULT 0.00,
address VARCHAR(500) DEFAULT NULL,
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
INDEX idx_vip (vip_level),
INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='客户表';
-- 4. 订单表
CREATE TABLE orders (
order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(30) NOT NULL COMMENT '订单编号',
customer_id INT UNSIGNED NOT NULL,
order_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
status ENUM('pending','confirmed','paid','shipped','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending',
payment_method VARCHAR(20) DEFAULT NULL,
payment_date DATETIME(6) DEFAULT NULL,
shipping_addr VARCHAR(500) DEFAULT NULL,
notes VARCHAR(500) DEFAULT NULL,
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
INDEX idx_customer (customer_id),
INDEX idx_order_no (order_no),
INDEX idx_status (status),
INDEX idx_order_date (order_date),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='订单表';
-- 5. 订单明细表
CREATE TABLE order_items (
item_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id INT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL DEFAULT 1,
unit_price DECIMAL(12,2) NOT NULL COMMENT '成交单价',
subtotal DECIMAL(12,2) NOT NULL COMMENT '小计=quantity*unit_price',
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
INDEX idx_order (order_id),
INDEX idx_product (product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='订单明细表';
-- 6. 账户表（模拟客户账户余额）
CREATE TABLE account_ledger (
account_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
balance DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '账户余额',
version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
UNIQUE KEY uk_customer (customer_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='客户账户表';
-- 7. 库存日志表（审计追踪）
CREATE TABLE inventory_log (
log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id INT UNSIGNED NOT NULL,
change_type ENUM('in','out','adjust_in','adjust_out','reserved','unreserved') NOT NULL,
change_qty INT NOT NULL COMMENT '变动数量（正数增加，负数减少）',
before_qty INT UNSIGNED NOT NULL COMMENT '变动前库存',
after_qty INT UNSIGNED NOT NULL COMMENT '变动后库存',
order_id INT UNSIGNED DEFAULT NULL COMMENT '关联订单',
reference_no VARCHAR(50) DEFAULT NULL COMMENT '关联单号',
operator VARCHAR(50) DEFAULT 'system' COMMENT '操作人',
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
INDEX idx_product (product_id),
INDEX idx_order (order_id),
INDEX idx_created (created_at),
FOREIGN KEY (product_id) REFERENCES products(product_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='库存变动日志表';
-- 8. Saga事务日志表（分布式事务补偿追踪）
CREATE TABLE saga_log (
saga_id VARCHAR(64) NOT NULL PRIMARY KEY COMMENT 'Saga事务ID',
step_name VARCHAR(100) NOT NULL COMMENT '步骤名称',
step_order INT NOT NULL COMMENT '执行顺序',
status ENUM('pending','executing','completed','compensating','compensated','failed') NOT NULL DEFAULT 'pending',
request_data JSON DEFAULT NULL COMMENT '请求参数',
response_data JSON DEFAULT NULL COMMENT '响应结果',
error_message VARCHAR(500) DEFAULT NULL COMMENT '错误信息',
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
completed_at DATETIME(6) DEFAULT NULL,
INDEX idx_saga (saga_id),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='Saga事务日志表';
-- 9. 死锁演示表
CREATE TABLE deadlock_demo (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
resource_type VARCHAR(20) NOT NULL COMMENT '资源类型：inventory/account',
resource_id INT UNSIGNED NOT NULL COMMENT '资源ID',
balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
version INT UNSIGNED NOT NULL DEFAULT 0,
updated_at DATETIME(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
INDEX idx_resource (resource_type, resource_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
COMMENT='死锁演示表';
-- 插入基础分类数据
INSERT INTO categories (category_name, parent_id, description) VALUES
('戒指', NULL, '各类戒指'),
('项链', NULL, '各类项链'),
('手链', NULL, '各类手链'),
('耳环', NULL, '各类耳环'),
('钻石', '戒指', '钻石戒指'),
('翡翠', '项链', '翡翠项链'),
('红宝石', '戒指', '红宝石戒指'),
('蓝宝石', '耳环', '蓝宝石耳环');
SHOW TABLES;
SHOW VARIABLES LIKE 'version';
SELECT 'Schema创建完成' AS status;
-- ============================================================
-- 模块: mysql9_data.sql
-- ============================================================
-- ============================================================
-- MySQL 9.0 珠宝行业 - 批量测试数据
-- ============================================================
USE jewelry_db;
-- 插入产品分类
INSERT INTO categories (category_name, parent_id, description) VALUES
('钻戒', 1, '钻石戒指'),
('金戒', 1, '黄金/铂金戒指'),
('珠链', 2, '珍珠项链'),
('钻链', 2, '钻石项链'),
('金链', 2, '黄金项链'),
('手链', 3, '各类手链'),
('耳饰', 4, '各类耳环'),
('翡翠', 2, '翡翠饰品'),
('彩宝', 1, '彩色宝石戒指'),
('套装', NULL, '珠宝套装');
-- 插入50款珠宝产品
INSERT INTO products (product_name, category_id, material, weight_gram, price, stock_qty, gemstone_type, gemstone_carat, color_grade, clarity_grade, cut_grade, certificate, status) VALUES
('经典六爪钻戒', 1, '铂金', NULL, 28888.00, 15, '钻石', 1.00, 'D', 'VVS1', 'EX', 'GIA-2345678', 'active'),
('玫瑰金钻戒', 1, '18K玫瑰金', NULL, 15688.00, 20, '钻石', 0.50, 'E', 'VS1', 'EX', 'GIA-3456789', 'active'),
('黄金转运珠戒指', 2, '黄金', 3.50, 4580.00, 50, NULL, NULL, NULL, NULL, NULL, 'NGTC-A001', 'active'),
('铂金素圈戒指', 2, '铂金', 4.20, 5200.00, 40, NULL, NULL, NULL, NULL, NULL, NULL, 'active'),
('蓝宝石钻戒', 9, '铂金', NULL, 35800.00, 8, '蓝宝石', 1.20, NULL, NULL, NULL, 'GIA-4567890', 'active'),
('红宝石钻戒', 9, '18K玫瑰金', NULL, 42000.00, 5, '红宝石', 0.80, NULL, NULL, NULL, 'GIA-5678901', 'active'),
('翡翠吊坠项链', 8, 'K金', 12.50, 18800.00, 10, '翡翠', NULL, NULL, NULL, NULL, 'NGTC-B002', 'active'),
('钻石项链', 4, '铂金', 8.30, 32000.00, 12, '钻石', 0.30, 'F', 'VS2', 'VG', 'GIA-6789012', 'active'),
('黄金项链', 5, '黄金', 25.00, 12800.00, 30, NULL, NULL, NULL, NULL, NULL, 'NGTC-C003', 'active'),
('珍珠项链', 3, 'K金', 18.00, 6800.00, 25, '珍珠', NULL, NULL, NULL, NULL, NULL, 'active'),
('翡翠手镯', 8, 'K金', 55.00, 56000.00, 3, '翡翠', NULL, NULL, NULL, NULL, 'NGTC-D004', 'active'),
('钻石耳环', 7, '铂金', 5.60, 19800.00, 18, '钻石', 0.20, 'D', 'VVS2', 'EX', 'GIA-7890123', 'active'),
('黄金手链', 6, '黄金', 15.80, 7600.00, 35, NULL, NULL, NULL, NULL, NULL, 'NGTC-E005', 'active'),
('铂金手链', 6, '铂金', 10.50, 8900.00, 22, NULL, NULL, NULL, NULL, NULL, NULL, 'active'),
('红宝石项链', 4, '18K玫瑰金', 9.20, 28000.00, 8, '红宝石', 1.50, NULL, NULL, NULL, 'GIA-8901234', 'active'),
('蓝宝石耳环', 7, '铂金', 4.80, 22000.00, 10, '蓝宝石', 0.90, NULL, NULL, NULL, 'GIA-9012345', 'active'),
('翡翠戒指', 1, '18K黄金', 6.50, 25000.00, 6, '翡翠', NULL, NULL, NULL, NULL, 'NGTC-F006', 'active'),
('钻石手链', 6, '铂金', 7.80, 26800.00, 15, '钻石', 0.15, 'E', 'VS1', 'EX', 'GIA-0123456', 'active'),
('黄金吊坠', 5, '黄金', 18.00, 8500.00, 40, NULL, NULL, NULL, NULL, NULL, 'NGTC-G007', 'active'),
('珍珠耳环', 7, 'K金', 3.20, 3800.00, 50, '珍珠', NULL, NULL, NULL, NULL, NULL, 'active'),
('彩宝套装', 10, '铂金', 35.00, 88000.00, 2, '彩宝混合', NULL, NULL, NULL, NULL, 'GIA-1234567', 'active'),
('莫桑石钻戒', 1, '18K白金', NULL, 6800.00, 30, '莫桑石', 1.00, 'D', 'IF', 'EX', 'IGI-2345670', 'active'),
('坦桑石戒指', 9, '铂金', NULL, 18000.00, 7, '坦桑石', 2.50, NULL, NULL, NULL, 'GIA-3456780', 'active'),
('碧玺项链', 4, '18K玫瑰金', 11.00, 12000.00, 12, '碧玺', 3.00, NULL, NULL, NULL, 'GIA-4567800', 'active'),
('石榴石手链', 6, '18K黄金', 14.00, 5500.00, 20, '石榴石', 5.00, NULL, NULL, NULL, NULL, 'active'),
('海蓝宝石耳环', 7, '铂金', 5.00, 15000.00, 10, '海蓝宝石', 2.00, NULL, NULL, NULL, 'GIA-5678000', 'active'),
('橄榄石戒指', 1, '18K黄金', NULL, 8800.00, 15, '橄榄石', 1.80, NULL, NULL, NULL, NULL, 'active'),
('锆石项链', 4, '925银', 6.50, 1200.00, 100, '锆石', 0.50, 'D', 'VVS1', 'EX', NULL, 'active'),
('水晶手链', 6, 'K金', 8.00, 2800.00, 45, '水晶', NULL, NULL, NULL, NULL, NULL, 'active'),
('琥珀项链', 5, 'K金', 12.00, 4500.00, 25, '琥珀', NULL, NULL, NULL, NULL, NULL, 'active'),
('青金石耳环', 7, '18K黄金', 4.50, 3200.00, 30, '青金石', NULL, NULL, NULL, NULL, NULL, 'active'),
('绿松石戒指', 1, '银', NULL, 1800.00, 40, '绿松石', NULL, NULL, NULL, NULL, NULL, 'active'),
('南红玛瑙项链', 5, 'K金', 20.00, 8000.00, 15, '南红', NULL, NULL, NULL, NULL, 'NGTC-H008', 'active'),
(' ivory 象牙果手链', 6, '棉线', 5.00, 680.00, 60, NULL, NULL, NULL, NULL, NULL, 'active'),
('紫水晶吊坠', 8, '18K白金', 7.50, 3500.00, 35, '紫水晶', 2.00, NULL, NULL, NULL, NULL, 'active'),
('黄玉戒指', 1, '铂金', NULL, 12000.00, 8, '黄玉', 1.50, NULL, NULL, NULL, 'GIA-6780000', 'active'),
('托帕石项链', 4, '18K玫瑰金', 9.00, 6500.00, 18, '托帕石', 3.50, NULL, NULL, NULL, NULL, 'active'),
('月光石耳环', 7, '银', 4.00, 1500.00, 50, '月光石', 1.00, NULL, NULL, NULL, NULL, 'active'),
('石榴石项链', 5, '18K黄金', 16.00, 7200.00, 20, '石榴石', 4.00, NULL, NULL, NULL, NULL, 'active'),
('蛋白石戒指', 1, '银', NULL, 2500.00, 25, '蛋白石', 1.20, NULL, NULL, NULL, NULL, 'active'),
('红纹石手链', 6, 'K金', 10.00, 4800.00, 22, '红纹石', 3.00, NULL, NULL, NULL, NULL, 'active'),
('天河石项链', 5, '18K白金', 13.00, 3800.00, 28, '天河石', NULL, NULL, NULL, NULL, NULL, 'active'),
('东陵玉手镯', 6, 'K金', 60.00, 3000.00, 10, '东陵玉', NULL, NULL, NULL, NULL, NULL, 'active'),
('玛瑙戒指', 1, '银', NULL, 800.00, 80, '玛瑙', NULL, NULL, NULL, NULL, NULL, 'active'),
('玉髓手链', 6, 'K金', 12.00, 2200.00, 40, '玉髓', NULL, NULL, NULL, NULL, NULL, 'active'),
('孔雀石吊坠', 8, '18K黄金', 8.50, 3000.00, 20, '孔雀石', NULL, NULL, NULL, NULL, NULL, 'active'),
('虎眼石耳环', 7, '银', 3.80, 900.00, 55, '虎眼石', NULL, NULL, NULL, NULL, NULL, 'active'),
('黑曜石项链', 5, '牛皮绳', 15.00, 500.00, 70, '黑曜石', NULL, NULL, NULL, NULL, NULL, 'active'),
('白水晶戒指', 1, '18K白金', NULL, 1500.00, 35, '白水晶', 1.00, NULL, NULL, NULL, NULL, 'active');
-- 插入30个客户
INSERT INTO customers (customer_name, phone, email, vip_level, total_spent, address) VALUES
('张伟', '13800138001', 'zhangwei@email.com', 'platinum', 158000.00, '北京市朝阳区建国路88号'),
('李娜', '13800138002', 'lina@email.com', 'gold', 68000.00, '上海市浦东新区陆家嘴环路100号'),
('王磊', '13800138003', 'wanglei@email.com', 'silver', 25000.00, '广州市天河区天河路200号'),
('赵敏', '13800138004', 'zhaomin@email.com', 'platinum', 235000.00, '深圳市南山区科技园南路50号'),
('陈静', '13800138005', 'chenjing@email.com', 'gold', 89000.00, '杭州市西湖区文三路300号'),
('刘洋', '13800138006', 'liuyang@email.com', 'normal', 5600.00, '成都市锦江区春熙路88号'),
('杨光', '13800138007', 'yangguang@email.com', 'gold', 72000.00, '南京市鼓楼区中山北路100号'),
('黄丽', '13800138008', 'huangli@email.com', 'platinum', 186000.00, '武汉市江汉区解放大道688号'),
('周强', '13800138009', 'zhouqiang@email.com', 'silver', 18000.00, '重庆市渝中区解放碑步行街50号'),
('吴婷', '13800138010', 'wuting@email.com', 'gold', 95000.00, '西安市雁塔区高新路60号'),
('徐峰', '13800138011', 'xufeng@email.com', 'normal', 3200.00, '天津市和平区南京路120号'),
('孙莉', '13800138012', 'sunli@email.com', 'silver', 22000.00, '长沙市天心区芙蓉南路200号'),
('马军', '13800138013', 'majun@email.com', 'normal', 8500.00, '郑州市金水区花园路150号'),
('朱琳', '13800138014', 'zhulin@email.com', 'gold', 112000.00, '合肥市蜀山区黄山路300号'),
('胡涛', '13800138015', 'hutao@email.com', 'silver', 15000.00, '济南市历下区泺源大街80号'),
('林芳', '13800138016', 'linfang@email.com', 'platinum', 198000.00, '福州市鼓楼区五四路100号'),
('何勇', '13800138017', 'heyong@email.com', 'normal', 4500.00, '海口市龙华区滨海大道200号'),
('高敏', '13800138018', 'gaomin@email.com', 'gold', 78000.00, '昆明市五华区翠湖西路50号'),
('罗明', '13800138019', 'luoming@email.com', 'silver', 28000.00, '贵阳市南明区花果园大街100号'),
('谢静', '13800138020', 'xiejing@email.com', 'gold', 85000.00, '兰州市城关区张掖路80号'),
('唐磊', '13800138021', 'tanglei@email.com', 'normal', 6800.00, '乌鲁木齐市天山区解放路120号'),
('韩雪', '13800138022', 'hanxue@email.com', 'platinum', 165000.00, '沈阳市和平区中山路200号'),
('冯刚', '13800138023', 'fenggang@email.com', 'silver', 19000.00, '大连市中山区人民路50号'),
('董娜', '13800138024', 'dongna@email.com', 'gold', 92000.00, '青岛市市南区香港路80号'),
('袁飞', '13800138025', 'yuanfei@email.com', 'normal', 7200.00, '石家庄市长安区中山东路100号'),
('邓丽', '13800138026', 'dengli@email.com', 'silver', 21000.00, '南宁市民宁区民族大道150号'),
('彭强', '13800138027', 'pengqiang@email.com', 'normal', 4800.00, '南宁市青秀区金湖路200号'),
('蒋欣', '13800138028', 'jiangxin@email.com', 'gold', 88000.00, '拉萨市城关区北京西路50号'),
('沈毅', '13800138029', 'shenyi@email.com', 'silver', 16500.00, '银川市兴庆区解放西街80号'),
('姚蕾', '13800138030', 'yaolei@email.com', 'platinum', 175000.00, '兰州市城关区庆阳路100号');
-- 插入30个订单
INSERT INTO orders (order_no, customer_id, order_date, total_amount, status, payment_method, payment_date, shipping_addr) VALUES
('ORD-20240101-001', 1, '2024-01-15 10:30:00', 28888.00, 'delivered', '信用卡', '2024-01-15 10:31:00', '北京市朝阳区建国路88号'),
('ORD-20240102-002', 2, '2024-01-16 14:20:00', 15688.00, 'delivered', '支付宝', '2024-01-16 14:21:00', '上海市浦东新区陆家嘴环路100号'),
('ORD-20240103-003', 3, '2024-01-18 09:15:00', 4580.00, 'shipped', '微信支付', '2024-01-18 09:16:00', '广州市天河区天河路200号'),
('ORD-20240104-004', 4, '2024-01-20 16:45:00', 35800.00, 'paid', '信用卡', '2024-01-20 16:46:00', '深圳市南山区科技园南路50号'),
('ORD-20240105-005', 5, '2024-01-22 11:00:00', 18800.00, 'confirmed', '支付宝', '2024-01-22 11:01:00', '杭州市西湖区文三路300号'),
('ORD-20240106-006', 6, '2024-01-23 15:30:00', 1200.00, 'pending', NULL, NULL, '成都市锦江区春熙路88号'),
('ORD-20240107-007', 7, '2024-01-25 10:00:00', 32000.00, 'delivered', '信用卡', '2024-01-25 10:01:00', '南京市鼓楼区中山北路100号'),
('ORD-20240108-008', 8, '2024-01-26 13:20:00', 56000.00, 'shipped', '支付宝', '2024-01-26 13:21:00', '武汉市江汉区解放大道688号'),
('ORD-20240109-009', 9, '2024-01-28 09:45:00', 5200.00, 'paid', '微信支付', '2024-01-28 09:46:00', '重庆市渝中区解放碑步行街50号'),
('ORD-20240110-010', 10, '2024-01-30 14:00:00', 22000.00, 'confirmed', '信用卡', '2024-01-30 14:01:00', '西安市雁塔区高新路60号'),
('ORD-20240201-011', 11, '2024-02-01 10:30:00', 6800.00, 'pending', NULL, NULL, '天津市和平区南京路120号'),
('ORD-20240202-012', 12, '2024-02-03 16:00:00', 8900.00, 'shipped', '支付宝', '2024-02-03 16:01:00', '长沙市天心区芙蓉南路200号'),
('ORD-20240203-013', 13, '2024-02-05 11:15:00', 3800.00, 'delivered', '微信支付', '2024-02-05 11:16:00', '郑州市金水区花园路150号'),
('ORD-20240204-014', 14, '2024-02-06 15:30:00', 28000.00, 'paid', '信用卡', '2024-02-06 15:31:00', '合肥市蜀山区黄山路300号'),
('ORD-20240205-015', 15, '2024-02-08 09:00:00', 8500.00, 'confirmed', '支付宝', '2024-02-08 09:01:00', '济南市历下区泺源大街80号'),
('ORD-20240206-016', 16, '2024-02-10 14:30:00', 88000.00, 'shipped', '信用卡', '2024-02-10 14:31:00', '福州市鼓楼区五四路100号'),
('ORD-20240207-017', 17, '2024-02-12 10:00:00', 2500.00, 'cancelled', NULL, NULL, '海口市龙华区滨海大道200号'),
('ORD-20240208-018', 18, '2024-02-14 16:20:00', 19800.00, 'delivered', '支付宝', '2024-02-14 16:21:00', '昆明市五华区翠湖西路50号'),
('ORD-20240209-019', 19, '2024-02-15 11:45:00', 15000.00, 'paid', '微信支付', '2024-02-15 11:46:00', '贵阳市南明区花果园大街100号'),
('ORD-20240210-020', 20, '2024-02-17 13:00:00', 85000.00, 'confirmed', '信用卡', '2024-02-17 13:01:00', '兰州市城关区张掖路80号'),
('ORD-20240211-021', 21, '2024-02-19 09:30:00', 6800.00, 'pending', NULL, NULL, '乌鲁木齐市天山区解放路120号'),
('ORD-20240212-022', 22, '2024-02-20 15:00:00', 165000.00, 'shipped', '支付宝', '2024-02-20 15:01:00', '沈阳市和平区中山路200号'),
('ORD-20240213-023', 23, '2024-02-22 10:15:00', 19000.00, 'paid', '微信支付', '2024-02-22 10:16:00', '大连市中山区人民路50号'),
('ORD-20240214-024', 24, '2024-02-24 14:45:00', 92000.00, 'delivered', '信用卡', '2024-02-24 14:46:00', '青岛市市南区香港路80号'),
('ORD-20240215-025', 25, '2024-02-25 09:00:00', 7200.00, 'confirmed', '支付宝', '2024-02-25 09:01:00', '石家庄市长安区中山东路100号'),
('ORD-20240216-026', 26, '2024-02-26 16:30:00', 21000.00, 'shipped', '微信支付', '2024-02-26 16:31:00', '南宁市民宁区民族大道150号'),
('ORD-20240217-027', 27, '2024-02-28 11:00:00', 4800.00, 'pending', NULL, NULL, '南宁市青秀区金湖路200号'),
('ORD-20240218-028', 28, '2024-02-29 13:30:00', 88000.00, 'paid', '信用卡', '2024-02-29 13:31:00', '拉萨市城关区北京西路50号'),
('ORD-20240219-029', 29, '2024-03-01 10:00:00', 16500.00, 'confirmed', '支付宝', '2024-03-01 10:01:00', '银川市兴庆区解放西街80号'),
('ORD-20240220-030', 30, '2024-03-02 15:15:00', 175000.00, 'shipped', '信用卡', '2024-03-02 15:16:00', '兰州市城关区庆阳路100号');
-- 插入订单明细
INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal) VALUES
(1, 1, 1, 28888.00, 28888.00),
(2, 2, 1, 15688.00, 15688.00),
(3, 3, 1, 4580.00, 4580.00),
(4, 5, 1, 35800.00, 35800.00),
(5, 7, 1, 18800.00, 18800.00),
(6, 28, 1, 1200.00, 1200.00),
(7, 8, 1, 32000.00, 32000.00),
(8, 11, 1, 56000.00, 56000.00),
(9, 4, 1, 5200.00, 5200.00),
(10, 15, 1, 22000.00, 22000.00),
(11, 28, 2, 1200.00, 2400.00),
(12, 13, 1, 8900.00, 8900.00),
(13, 19, 1, 3800.00, 3800.00),
(14, 15, 1, 28000.00, 28000.00),
(15, 17, 1, 8500.00, 8500.00),
(16, 21, 1, 88000.00, 88000.00),
(17, 28, 1, 2500.00, 2500.00),
(18, 12, 1, 19800.00, 19800.00),
(19, 24, 1, 15000.00, 15000.00),
(20, 14, 1, 85000.00, 85000.00),
(21, 30, 1, 6800.00, 6800.00),
(22, 33, 1, 165000.00, 165000.00),
(23, 23, 1, 19000.00, 19000.00),
(24, 35, 1, 92000.00, 92000.00),
(25, 29, 1, 7200.00, 7200.00),
(26, 36, 1, 21000.00, 21000.00),
(27, 31, 1, 4800.00, 4800.00),
(28, 39, 1, 88000.00, 88000.00),
(29, 25, 1, 16500.00, 16500.00),
(30, 41, 1, 175000.00, 175000.00);
-- 插入账户数据
INSERT INTO account_ledger (customer_id, balance, version) VALUES
(1, 50000.00, 0), (2, 35000.00, 0), (3, 12000.00, 0),
(4, 80000.00, 0), (5, 45000.00, 0), (6, 3000.00, 0),
(7, 38000.00, 0), (8, 65000.00, 0), (9, 8000.00, 0),
(10, 52000.00, 0), (11, 2500.00, 0), (12, 18000.00, 0),
(13, 5000.00, 0), (14, 70000.00, 0), (15, 15000.00, 0),
(16, 95000.00, 0), (17, 1800.00, 0), (18, 42000.00, 0),
(19, 10000.00, 0), (20, 55000.00, 0), (21, 3500.00, 0),
(22, 78000.00, 0), (23, 12000.00, 0), (24, 60000.00, 0),
(25, 4000.00, 0), (26, 16000.00, 0), (27, 2800.00, 0),
(28, 58000.00, 0), (29, 11000.00, 0), (30, 85000.00, 0);
-- 插入死锁演示数据
INSERT INTO deadlock_demo (resource_type, resource_id, balance, version) VALUES
('inventory', 1, 100.00, 0),
('inventory', 2, 200.00, 0),
('account', 1, 5000.00, 0),
('account', 2, 8000.00, 0);
-- 插入库存日志
INSERT INTO inventory_log (product_id, change_type, change_qty, before_qty, after_qty, order_id, reference_no, operator) VALUES
(1, 'out', 1, 16, 15, 1, 'ORD-20240101-001', 'system'),
(2, 'out', 1, 21, 20, 2, 'ORD-20240102-002', 'system'),
(3, 'out', 1, 51, 50, 3, 'ORD-20240103-003', 'system'),
(5, 'out', 1, 9, 8, 4, 'ORD-20240104-004', 'system'),
(7, 'out', 1, 11, 10, 5, 'ORD-20240105-005', 'system'),
(28, 'out', 1, 101, 100, 6, 'ORD-20240106-006', 'system'),
(8, 'out', 1, 13, 12, 7, 'ORD-20240107-007', 'system'),
(11, 'out', 1, 31, 30, 8, 'ORD-20240108-008', 'system'),
(4, 'out', 1, 41, 40, 9, 'ORD-20240109-009', 'system'),
(15, 'out', 1, 23, 22, 10, 'ORD-20240110-010', 'system');
SELECT 'Data数据插入完成' AS status;
SELECT COUNT(*) AS product_count FROM products;
SELECT COUNT(*) AS customer_count FROM customers;
SELECT COUNT(*) AS order_count FROM orders;
-- ============================================================
-- 模块: mysql9_acid_isolation.sql
-- ============================================================
-- ============================================================
-- MySQL 9.0 珠宝行业 - ACID事务控制与隔离级别
-- ============================================================
USE jewelry_db;
-- ============================================================
-- 1. ACID 特性验证
-- ============================================================
-- 1.1 原子性(A)演示：转账事务（全部成功或全部回滚）
-- 场景：客户1给客户2转账5000元购买珠宝
SELECT '=== 1.1 原子性演示：转账事务 ===' AS step;
-- 查看初始余额
SELECT customer_id, balance FROM account_ledger WHERE customer_id IN (1, 2);
-- 开始事务
START TRANSACTION;
-- 扣款
UPDATE account_ledger SET balance = balance - 5000.00, version = version + 1
WHERE customer_id = 1 AND balance >= 5000.00;
-- 模拟扣款后发生异常（如库存不足），回滚整个事务
-- ROLLBACK; -- 实际执行时取消注释此行可看到回滚效果
-- 入账
UPDATE account_ledger SET balance = balance + 5000.00, version = version + 1
WHERE customer_id = 2;
-- 验证事务后余额
SELECT customer_id, balance FROM account_ledger WHERE customer_id IN (1, 2);
-- 如果一切正常，提交事务
COMMIT;
-- 再次验证（提交后余额已变更）
SELECT customer_id, balance FROM account_ledger WHERE customer_id IN (1, 2) AS 'commit_after';
-- 回滚到之前的状态（用于演示）
START TRANSACTION;
UPDATE account_ledger SET balance = balance + 5000.00, version = version + 1
WHERE customer_id = 2;
UPDATE account_ledger SET balance = balance - 5000.00, version = version + 1
WHERE customer_id = 1;
COMMIT;
-- 1.2 一致性(C)演示：库存一致性约束
SELECT '=== 1.2 一致性演示：库存约束 ===' AS step;
-- 查看产品1的当前库存
SELECT product_id, product_name, stock_qty FROM products WHERE product_id = 1;
-- 事务中扣库存 + 创建订单 + 记录日志，三者必须同时成功
START TRANSACTION;
-- 步骤1: 扣减库存
UPDATE products SET stock_qty = stock_qty - 2 WHERE product_id = 1 AND stock_qty >= 2;
-- 步骤2: 创建订单
INSERT INTO orders (order_no, customer_id, order_date, total_amount, status)
VALUES ('ORD-ACID-001', 1, NOW(6), 57776.00, 'pending');
-- 步骤3: 记录库存日志
INSERT INTO inventory_log (product_id, change_type, change_qty, before_qty, after_qty, order_id, reference_no)
SELECT 1, 'out', -2, stock_qty, stock_qty - 2, LAST_INSERT_ID(), 'ORD-ACID-001'
FROM products WHERE product_id = 1;
-- 验证中间状态
SELECT product_id, stock_qty FROM products WHERE product_id = 1;
SELECT COUNT(*) AS order_count FROM orders WHERE order_no = 'ORD-ACID-001';
-- 全部成功则提交
COMMIT;
-- 一致性破坏演示：如果不使用事务，中间状态可见
-- 模拟不一致场景（在单独会话中执行）
-- START TRANSACTION;
-- UPDATE products SET stock_qty = stock_qty - 5 WHERE product_id = 1;
-- -- 此时另一个会话查询会看到库存不一致
-- ROLLBACK;
-- 1.3 隔离性(I)演示：四种隔离级别
SELECT '=== 1.3 隔离级别演示 ===' AS step;
-- 查看当前隔离级别
SELECT @@transaction_isolation AS current_isolation_level;
-- 设置会话级隔离级别（READ COMMITTED - MySQL默认）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation AS rc_level;
-- READ UNCOMMITTED 演示
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation AS ru_level;
-- REPEATABLE READ 演示（MySQL默认）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@transaction_isolation AS rr_level;
-- SERIALIZABLE 演示
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@transaction_isolation AS serial_level;
-- 恢复默认隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 1.4 持久性(D)演示
SELECT '=== 1.4 持久性演示 ===' AS step;
-- 插入一条测试数据
START TRANSACTION;
INSERT INTO categories (category_name, parent_id, description) VALUES ('测试分类', NULL, '持久性测试');
SELECT * FROM categories WHERE category_name = '测试分类';
COMMIT;
-- 即使断开连接重连，数据依然存在（持久性保证）
SELECT * FROM categories WHERE category_name = '测试分类';
-- 清理测试数据
DELETE FROM categories WHERE category_name = '测试分类';
-- ============================================================
-- 2. 事务嵌套与保存点
-- ============================================================
SELECT '=== 2. 事务嵌套与保存点 ===' AS step;
-- MySQL不支持嵌套事务，但支持保存点(SAVEPOINT)
-- 场景：珠宝订单创建包含多个子步骤，某步失败只回滚部分
START TRANSACTION;
-- 步骤1: 创建订单
INSERT INTO orders (order_no, customer_id, order_date, total_amount, status)
VALUES ('ORD-SAVEPOINT-001', 1, NOW(6), 10000.00, 'pending');
SET @order_id = LAST_INSERT_ID();
-- 保存点1：订单创建成功
SAVEPOINT sp_order_created;
-- 步骤2: 插入订单明细
INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal)
VALUES (@order_id, 1, 1, 28888.00, 28888.00);
-- 保存点2：明细插入成功
SAVEPOINT sp_items_inserted;
-- 步骤3: 扣减库存（模拟失败：库存不足）
-- 假设产品1库存已不足，更新影响0行
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 1 AND stock_qty >= 1;
-- 检查库存扣减结果
SELECT ROW_COUNT() AS rows_affected;
-- 如果库存不足，回滚到保存点1（保留订单，删除明细）
-- ROLLBACK TO sp_order_created;
-- DELETE FROM order_items WHERE order_id = @order_id;
-- COMMIT;
-- 正常情况：提交所有更改
-- COMMIT;
-- 清理测试数据
DELETE FROM order_items WHERE order_id = @order_id;
DELETE FROM orders WHERE order_id = @order_id;
-- ============================================================
-- 3. XACT_ABORT 等效设置（autocommit与锁管理）
-- ============================================================
SELECT '=== 3. 自动提交与锁管理 ===' AS step;
-- 查看自动提交状态
SELECT @@autocommit AS autocommit_status;
-- 关闭自动提交（手动管理事务）
SET autocommit = 0;
-- 批量更新珠宝价格（模拟促销活动）
-- 所有更新在一个事务中完成
START TRANSACTION;
UPDATE products SET price = price * 0.9 WHERE category_id = 1 AND status = 'active'; -- 戒指类9折
UPDATE products SET price = price * 0.85 WHERE category_id = 4 AND status = 'active'; -- 钻石类85折
UPDATE products SET price = price * 0.95 WHERE material = '黄金' AND status = 'active'; -- 黄金类95折
-- 查看促销后价格
SELECT product_id, product_name, price AS promo_price FROM products WHERE status = 'active' LIMIT 10;
-- 验证无误后提交
COMMIT;
-- 恢复自动提交
SET autocommit = 1;
-- 恢复原价（撤销促销）
START TRANSACTION;
UPDATE products SET price = price / 0.9 WHERE category_id = 1 AND product_name LIKE '经典%';
UPDATE products SET price = price / 0.85 WHERE category_id = 4 AND gemstone_type = '钻石';
UPDATE products SET price = price / 0.95 WHERE material = '黄金';
COMMIT;
-- ============================================================
-- 4. 两阶段提交（2PC）原理演示
-- ============================================================
SELECT '=== 4. 两阶段提交原理演示 ===' AS step;
-- MySQL InnoDB的2PC通过binlog实现
-- 设置事务为同步binlog（保证崩溃恢复一致性）
SELECT @@sync_binlog AS sync_binlog_setting;
SELECT @@innodb_flush_log_at_trx_commit AS flush_log_setting;
-- 演示2PC流程（概念性）：
-- 阶段1 (Prepare): InnoDB将事务写入redo log并标记为prepare
-- 阶段2 (Commit): 写入binlog后，InnoDB提交事务并清除prepare标记
-- 模拟一个需要2PC保证的事务
START TRANSACTION;
-- 操作1: 更新订单状态
UPDATE orders SET status = 'paid', payment_date = NOW(6)
WHERE order_no = 'ORD-20240101-001' AND status = 'confirmed';
-- 操作2: 扣减账户余额
UPDATE account_ledger SET balance = balance - 28888.00, version = version + 1
WHERE customer_id = 1 AND balance >= 28888.00;
-- 操作3: 记录交易流水
INSERT INTO inventory_log (product_id, change_type, change_qty, before_qty, after_qty, order_id, reference_no, operator)
SELECT p.product_id, 'out', -1, p.stock_qty, p.stock_qty - 1, o.order_id, o.order_no, 'payment_system'
FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_no = 'ORD-20240101-001' LIMIT 1;
-- 两阶段提交完成
COMMIT;
-- 验证最终一致性
SELECT o.order_no, o.status, a.balance
FROM orders o JOIN account_ledger a ON o.customer_id = a.customer_id
WHERE o.order_no = 'ORD-20240101-001';
-- 恢复账户余额（演示用）
START TRANSACTION;
UPDATE account_ledger SET balance = balance + 28888.00, version = version + 1
WHERE customer_id = 1;
COMMIT;
SELECT 'ACID事务控制与隔离级别演示完成' AS status;
-- ============================================================
-- 模块: mysql9_locking.sql
-- ============================================================
-- ============================================================
-- MySQL 9.0 珠宝行业 - 锁机制演示
-- 悲观锁、乐观锁、死锁检测与预防
-- ============================================================
USE jewelry_db;
-- ============================================================
-- 1. 悲观锁演示（Pessimistic Locking）
-- ============================================================
SELECT '=== 1. 悲观锁演示 ===' AS step;
-- 1.1 FOR UPDATE（排他锁）- 购买珠宝时锁定库存行
-- 会话A执行：
-- START TRANSACTION;
-- SELECT stock_qty FROM products WHERE product_id = 1 FOR UPDATE;
-- -- 此时会话B尝试更新同一行会被阻塞
-- UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 1;
-- COMMIT;
-- 模拟购买流程（单会话演示）
START TRANSACTION;
-- 使用FOR UPDATE锁定库存行
SELECT product_id, product_name, stock_qty, price
FROM products WHERE product_id = 1 FOR UPDATE;
-- 验证库存充足
-- 检查stock_qty >= 1
-- 扣减库存
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 1;
-- 创建订单
INSERT INTO orders (order_no, customer_id, order_date, total_amount, status)
VALUES ('ORD-LOCK-001', 1, NOW(6), (SELECT price FROM products WHERE product_id = 1), 'pending');
-- 记录库存日志
INSERT INTO inventory_log (product_id, change_type, change_qty, before_qty, after_qty, order_id, reference_no)
SELECT 1, 'out', -1, stock_qty + 1, stock_qty, LAST_INSERT_ID(), 'ORD-LOCK-001'
FROM products WHERE product_id = 1;
-- 扣款
UPDATE account_ledger SET balance = balance - (SELECT price FROM products WHERE product_id = 1), version = version + 1
WHERE customer_id = 1;
COMMIT;
-- 1.2 LOCK IN SHARE MODE（共享锁）- 查看库存时不阻塞其他共享锁
-- 会话A执行：
-- START TRANSACTION;
-- SELECT stock_qty FROM products WHERE product_id = 1 LOCK IN SHARE MODE;
-- -- 其他会话也可以加共享锁，但无法更新直到本事务释放
-- COMMIT;
-- 模拟批量查询库存（共享锁）
START TRANSACTION;
SELECT product_id, product_name, stock_qty FROM products WHERE category_id = 1 LOCK IN SHARE MODE;
-- 其他会话可以并行读取，但无法修改这些行
COMMIT;
-- 1.3 表级锁（TABLE LOCK）- 批量操作时使用
-- 场景：日终结算，需要锁定整张订单表
LOCK TABLES orders WRITE, order_items WRITE;
-- 批量操作
INSERT INTO orders (order_no, customer_id, order_date, total_amount, status)
SELECT CONCAT('ORD-BATCH-', @seq := @seq + 1), customer_id, order_date, total_amount, 'batch_imported'
FROM (
SELECT 15 AS customer_id, '2024-03-01 10:00:00' AS order_date, 5000.00 AS total_amount
UNION ALL SELECT 16, '2024-03-01 10:05:00', 3200.00
UNION ALL SELECT 17, '2024-03-01 10:10:00', 8800.00
) AS batch_data, (SELECT @seq := 0) AS init;
UNLOCK TABLES;
-- ============================================================
-- 2. 乐观锁演示（Optimistic Locking）
-- ============================================================
SELECT '=== 2. 乐观锁演示 ===' AS step;
-- 2.1 基于版本号(version字段)的乐观锁
-- 场景：多个管理员同时修改同一款珠宝的价格
-- 查看当前版本
SELECT product_id, product_name, price, updated_at FROM products WHERE product_id = 1;
-- 方法1: 使用version字段 + WHERE条件
-- 会话A和会话B同时读取version=0
-- 会话A先更新：
UPDATE products SET price = 29888.00, version = version + 1
WHERE product_id = 1 AND version = 0;
-- 返回affected_rows = 1（成功）
-- 会话B后更新（version已变为1，条件不满足）：
-- UPDATE products SET price = 30888.00, version = version + 1
-- WHERE product_id = 1 AND version = 0;
-- 返回affected_rows = 0（失败，需要重试）
-- 验证版本变化
SELECT product_id, price, version FROM products WHERE product_id = 1;
-- 2.2 CAS（Compare-And-Swap）原子扣减库存
-- 场景：秒杀活动，多用户同时购买同一款珠宝
-- 使用WHERE条件作为CAS判断
-- 模拟秒杀扣库存
-- 用户1：
START TRANSACTION;
UPDATE products SET stock_qty = stock_qty - 1
WHERE product_id = 28 AND stock_qty > 0;
-- 检查影响行数：1=成功，0=库存不足
-- 用户2（几乎同时）：
-- START TRANSACTION;
-- UPDATE products SET stock_qty = stock_qty - 1
-- WHERE product_id = 28 AND stock_qty > 0;
-- COMMIT;
-- 验证最终库存
SELECT product_id, product_name, stock_qty FROM products WHERE product_id = 28;
-- 2.3 基于时间戳的乐观锁
-- 场景：珠宝信息编辑（名称、描述等）
-- 表中有updated_at时间戳，更新时检查时间戳是否被其他事务修改
-- 模拟编辑珠宝信息
START TRANSACTION;
-- 读取当前记录和时间戳
SELECT product_id, product_name, price, updated_at INTO @pid, @name, @price, @ts
FROM products WHERE product_id = 3;
-- 模拟业务处理延迟...
-- 更新时检查时间戳（如果updated_at已被其他事务修改，则更新失败）
UPDATE products SET product_name = @name, price = @price, updated_at = NOW(6)
WHERE product_id = @pid AND updated_at = @ts;
-- 检查影响行数判断是否成功
SELECT ROW_COUNT() AS update_result;
COMMIT;
-- ============================================================
-- 3. 死锁检测与预防
-- ============================================================
SELECT '=== 3. 死锁检测与预防 ===' AS step;
-- 3.1 查看死锁信息
SHOW ENGINE INNODB STATUS\G
-- 在\G输出中查找LATEST DETECTED DEADLOCK部分
-- 3.2 死锁预防策略演示
-- 策略1: 统一加锁顺序
-- 错误做法（死锁）：
-- 会话A: UPDATE products SET stock_qty=... WHERE product_id=1;
-- UPDATE account_ledger SET balance=... WHERE customer_id=1;
-- 会话B: UPDATE account_ledger SET balance=... WHERE customer_id=1;
-- UPDATE products SET stock_qty=... WHERE product_id=1;
-- -> 死锁！
-- 正确做法（统一顺序）：
-- 所有事务先锁products，再锁account_ledger
START TRANSACTION;
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 5 AND stock_qty > 0;
UPDATE account_ledger SET balance = balance - 35800.00, version = version + 1 WHERE customer_id = 4;
COMMIT;
-- 策略2: 设置锁超时
-- 避免事务长时间持有锁导致死锁
SET SESSION innodb_lock_wait_timeout = 10; -- 10秒超时
-- 演示锁超时
-- 会话A: START TRANSACTION; UPDATE products SET stock_qty=0 WHERE product_id=1 FOR UPDATE;
-- 会话B: START TRANSACTION; UPDATE products SET stock_qty=0 WHERE product_id=1 FOR UPDATE;
-- -> 会话B等待10秒后超时报错
-- 策略3: 减少锁持有时间
-- 避免在事务中执行耗时操作（如网络请求、复杂计算）
-- 错误做法：
-- START TRANSACTION;
-- SELECT ... FOR UPDATE;
-- -- 调用外部API / 睡眠5秒 / 用户输入...
-- UPDATE ...;
-- COMMIT;
-- 正确做法：尽快获取锁、尽快释放锁
START TRANSACTION;
SELECT stock_qty FROM products WHERE product_id = 1 FOR UPDATE;
-- 快速判断并更新
IF stock_qty > 0 THEN
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 1;
END IF;
COMMIT;
-- 3.3 死锁模拟与解决（使用演示表）
-- 注意：以下代码会产生死锁，仅用于演示理解
-- 需要在两个会话中交替执行才能触发死锁
-- 会话A:
-- START TRANSACTION;
-- UPDATE deadlock_demo SET balance = balance - 100 WHERE resource_id = 1 AND resource_type = 'account';
-- SLEEP(1); -- 故意延迟
-- UPDATE deadlock_demo SET balance = balance - 100 WHERE resource_id = 2 AND resource_type = 'inventory';
-- 会话B:
-- START TRANSACTION;
-- UPDATE deadlock_demo SET balance = balance - 100 WHERE resource_id = 2 AND resource_type = 'inventory';
-- SLEEP(1); -- 故意延迟
-- UPDATE deadlock_demo SET balance = balance - 100 WHERE resource_id = 1 AND resource_type = 'account';
-- 死锁发生后，MySQL会自动选择一个事务回滚（牺牲者）
-- 被选为牺牲者的事务会收到错误：Deadlock found when trying to get lock; try restarting transaction
-- 3.4 死锁重试机制（应用层实现）
-- MySQL 8.0+ 支持在SQL中捕获死锁错误并重试
-- 使用存储过程实现自动重试
-- 创建死锁重试存储过程
DELIMITER //
CREATE PROCEDURE safe_transfer_money(
IN from_customer INT UNSIGNED,
IN to_customer INT UNSIGNED,
IN amount DECIMAL(12,2),
IN max_retries INT
)
BEGIN
DECLARE retry_count INT DEFAULT 0;
DECLARE deadlock_error CONDITION FOR SQLSTATE '41000';
DECLARE EXIT HANDLER FOR deadlock_error
BEGIN
-- 死锁发生，重试
IF retry_count < max_retries THEN
SET retry_count = retry_count + 1;
RESIGNAL;
ELSE
RESIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Max retries exceeded due to deadlock';
END IF;
END;
retry_loop: LOOP
START TRANSACTION;
BEGIN
-- 统一加锁顺序：先锁id小的
IF from_customer < to_customer THEN
UPDATE account_ledger SET balance = balance - amount, version = version + 1
WHERE customer_id = from_customer AND balance >= amount;
UPDATE account_ledger SET balance = balance + amount, version = version + 1
WHERE customer_id = to_customer;
ELSE
UPDATE account_ledger SET balance = balance + amount, version = version + 1
WHERE customer_id = to_customer;
UPDATE account_ledger SET balance = balance - amount, version = version + 1
WHERE customer_id = from_customer AND balance >= amount;
END IF;
-- 检查扣款是否成功
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
END IF;
COMMIT;
LEAVE retry_loop;
EXCEPTION
WHEN deadlock_error THEN
ROLLBACK;
IF retry_count >= max_retries THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deadlock retry failed';
END IF;
ITERATE retry_loop;
END;
END LOOP;
END //
DELIMITER ;
-- 调用重试存储过程
CALL safe_transfer_money(1, 2, 1000.00, 3);
-- 查看结果
SELECT customer_id, balance FROM account_ledger WHERE customer_id IN (1, 2);
-- ============================================================
-- 4. 锁监控与诊断
-- ============================================================
SELECT '=== 4. 锁监控与诊断 ===' AS step;
-- 4.1 查看当前锁等待情况（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
-- 4.2 查看当前事务信息
SELECT * FROM performance_schema.replication_applier_status_by_worker;
SELECT trx_id, trx_state, trx_started, trx_rows_modified
FROM information_schema.innodb_trx;
-- 4.3 查看锁等待图
-- MySQL 8.0+ 支持可视化锁等待
-- SELECT * FROM sys.innodb_lock_waits;
-- 4.4 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G
-- 4.5 监控长事务
SELECT r.trx_id waiting_trx_id,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
-- 清理重试过程（可选）
-- DROP PROCEDURE IF EXISTS safe_transfer_money;
SELECT '锁机制演示完成' AS status;
-- ============================================================
-- 模块: mysql9_deadlock_saga.sql
-- ============================================================
-- ============================================================
-- MySQL 9.0 珠宝行业 - 死锁预防与Saga模式
-- 长事务补偿、最终一致性
-- ============================================================
USE jewelry_db;
-- ============================================================
-- 1. 死锁预防策略总结
-- ============================================================
SELECT '=== 1. 死锁预防策略 ===' AS step;
-- 策略总结（珠宝行业场景）：
-- 1. 统一加锁顺序：所有事务按相同的顺序访问资源（如先products后account_ledger）
-- 2. 缩短事务长度：避免在事务中做IO操作、网络调用
-- 3. 使用低隔离级别：尽量用READ COMMITTED而非REPEATABLE READ
-- 4. 使用索引：减少锁范围（行锁vs表锁）
-- 5. 设置锁超时：innodb_lock_wait_timeout避免无限等待
-- 6. 批量操作分片：大事务拆小事务，减少持有锁的时间
-- 验证锁超时设置
SELECT @@innodb_lock_wait_timeout AS lock_wait_timeout;
SELECT @@transaction_isolation AS current_isolation;
-- ============================================================
-- 2. Saga模式实现 - 珠宝订单创建流程
-- ============================================================
SELECT '=== 2. Saga模式：订单创建 ===' AS step;
-- Saga模式核心思想：
-- 将长事务拆分为多个本地事务，每个本地事务有对应的补偿操作
-- 正向步骤：创建订单 -> 扣库存 -> 扣款 -> 发货通知
-- 补偿步骤：取消订单 -> 恢复库存 -> 退款 -> 取消发货通知
-- 2.1 创建Saga编排存储过程
DELIMITER //
-- 订单创建Saga（正向流程）
CREATE PROCEDURE saga_create_order(
IN p_customer_id INT UNSIGNED,
IN p_product_id INT UNSIGNED,
IN p_quantity INT UNSIGNED,
IN p_order_no VARCHAR(30),
OUT p_saga_id VARCHAR(64),
OUT p_result VARCHAR(200)
)
BEGIN
DECLARE v_product_price DECIMAL(12,2);
DECLARE v_stock INT UNSIGNED;
DECLARE v_total DECIMAL(12,2);
DECLARE v_account_balance DECIMAL(12,2);
DECLARE v_order_id INT UNSIGNED;
DECLARE v_saga_id VARCHAR(64);
DECLARE v_step INT DEFAULT 0;
-- 生成Saga ID
SET v_saga_id = CONCAT('SG-', REPLACE(NOW(6), ':', ''), '-', p_customer_id);
SET p_saga_id = v_saga_id;
-- 记录Saga开始
INSERT INTO saga_log (saga_id, step_name, step_order, status, request_data)
VALUES (v_saga_id, 'ORDER_CREATE_START', 0, 'executing',
JSON_OBJECT('customer_id', p_customer_id, 'product_id', p_product_id, 'quantity', p_quantity));
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 异常时执行补偿
SET p_result = 'FAILED';
ROLLBACK;
CALL saga_compensate(v_saga_id);
END;
START TRANSACTION;
-- Step 1: 创建订单（本地事务1）
SET v_step = 1;
INSERT INTO orders (order_no, customer_id, order_date, total_amount, status)
VALUES (p_order_no, p_customer_id, NOW(6), 0, 'pending');
SET v_order_id = LAST_INSERT_ID();
INSERT INTO saga_log (saga_id, step_name, step_order, status, response_data)
VALUES (v_saga_id, 'ORDER_CREATED', 1, 'completed',
JSON_OBJECT('order_id', v_order_id));
-- Step 2: 获取产品信息并扣库存（本地事务2）
SET v_step = 2;
SELECT price, stock_qty INTO v_product_price, v_stock
FROM products WHERE product_id = p_product_id FOR UPDATE;
IF v_stock < p_quantity THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
END IF;
UPDATE products SET stock_qty = stock_qty - p_quantity WHERE product_id = p_product_id;
INSERT INTO saga_log (saga_id, step_name, step_order, status, response_data)
VALUES (v_saga_id, 'STOCK_DEDUCTED', 2, 'completed',
JSON_OBJECT('product_id', p_product_id, 'quantity', p_quantity));
-- Step 3: 扣款（本地事务3）
SET v_step = 3;
SET v_total = v_product_price * p_quantity;
SELECT balance INTO v_account_balance FROM account_ledger WHERE customer_id = p_customer_id FOR UPDATE;
IF v_account_balance < v_total THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
END IF;
UPDATE account_ledger SET balance = balance - v_total, version = version + 1
WHERE customer_id = p_customer_id;
INSERT INTO saga_log (saga_id, step_name, step_order, status, response_data)
VALUES (v_saga_id, 'PAYMENT_DEDUCTED', 3, 'completed',
JSON_OBJECT('amount', v_total, 'customer_id', p_customer_id));
-- Step 4: 插入订单明细
SET v_step = 4;
INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal)
VALUES (v_order_id, p_product_id, p_quantity, v_product_price, v_total);
-- Step 5: 记录库存日志
INSERT INTO inventory_log (product_id, change_type, change_qty, before_qty, after_qty, order_id, reference_no)
VALUES (p_product_id, 'out', -p_quantity, v_stock, v_stock - p_quantity, v_order_id, p_order_no);
-- Step 6: 更新订单状态和总金额
UPDATE orders SET total_amount = v_total, status = 'confirmed' WHERE order_id = v_order_id;
INSERT INTO saga_log (saga_id, step_name, step_order, status, response_data)
VALUES (v_saga_id, 'ORDER_COMPLETED', 4, 'completed',
JSON_OBJECT('order_id', v_order_id, 'total', v_total));
-- 所有步骤成功，提交事务
COMMIT;
-- 更新Saga状态
UPDATE saga_log SET status = 'completed', completed_at = NOW(6)
WHERE saga_id = v_saga_id AND step_order = 0;
SET p_result = 'SUCCESS';
END //
-- Saga补偿过程（反向回滚）
CREATE PROCEDURE saga_compensate(IN p_saga_id VARCHAR(64))
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_step_name VARCHAR(100);
DECLARE v_step_order INT;
DECLARE v_request JSON;
-- 按倒序读取需要补偿的步骤
DECLARE step_cursor CURSOR FOR
SELECT step_name, step_order, request_data
FROM saga_log
WHERE saga_id = p_saga_id AND step_order > 0
ORDER BY step_order DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
-- 标记Saga为补偿中
UPDATE saga_log SET status = 'compensating' WHERE saga_id = p_saga_id AND step_order = 0;
OPEN step_cursor;
read_steps: LOOP
FETCH step_cursor INTO v_step_name, v_step_order, v_request;
IF v_done THEN
LEAVE read_steps;
END IF;
-- 根据步骤名执行对应补偿逻辑
IF v_step_name = 'ORDER_COMPLETED' THEN
-- 补偿：更新订单为取消状态
UPDATE orders SET status = 'cancelled'
WHERE order_id = JSON_UNQUOTE(JSON_EXTRACT(v_request, '$.order_id'));
ELSEIF v_step_name = 'STOCK_DEDUCTED' THEN
-- 补偿：恢复库存
UPDATE products SET stock_qty = stock_qty + JSON_UNQUOTE(JSON_EXTRACT(v_request, '$.quantity'))
WHERE product_id = JSON_UNQUOTE(JSON_EXTRACT(v_request, '$.product_id'));
ELSEIF v_step_name = 'PAYMENT_DEDUCTED' THEN
-- 补偿：退款
UPDATE account_ledger SET balance = balance + JSON_UNQUOTE(JSON_EXTRACT(v_request, '$.amount')),
version = version + 1
WHERE customer_id = JSON_UNQUOTE(JSON_EXTRACT(v_request, '$.customer_id'));
ELSEIF v_step_name = 'ORDER_CREATED' THEN
-- 补偿：删除订单
DELETE FROM orders WHERE order_id = JSON_UNQUOTE(JSON_EXTRACT(v_request, '$.order_id'));
END IF;
-- 标记该步骤已补偿
UPDATE saga_log SET status = 'compensated', completed_at = NOW(6)
WHERE saga_id = p_saga_id AND step_name = v_step_name;
END LOOP;
CLOSE step_cursor;
-- 标记Saga完成补偿
UPDATE saga_log SET status = 'compensated', completed_at = NOW(6)
WHERE saga_id = p_saga_id AND step_order = 0;
END //
-- 订单取消Saga
CREATE PROCEDURE saga_cancel_order(
IN p_order_no VARCHAR(30),
OUT p_saga_id VARCHAR(64),
OUT p_result VARCHAR(200)
)
BEGIN
DECLARE v_order_id INT UNSIGNED;
DECLARE v_customer_id INT UNSIGNED;
DECLARE v_saga_id VARCHAR(64);
DECLARE v_product_id INT UNSIGNED;
DECLARE v_quantity INT UNSIGNED;
DECLARE v_unit_price DECIMAL(12,2);
DECLARE v_total DECIMAL(12,2);
SET v_saga_id = CONCAT('SG-CANCEL-', REPLACE(NOW(6), ':', ''), '-', p_order_no);
SET p_saga_id = v_saga_id;
-- 获取订单信息
SELECT order_id, customer_id INTO v_order_id, v_customer_id
FROM orders WHERE order_no = p_order_no AND status IN ('confirmed', 'paid');
IF v_order_id IS NULL THEN
SET p_result = 'ORDER_NOT_FOUND';
ELSE
START TRANSACTION;
-- 记录Saga开始
INSERT INTO saga_log (saga_id, step_name, step_order, status, request_data)
VALUES (v_saga_id, 'CANCEL_START', 0, 'executing',
JSON_OBJECT('order_no', p_order_no, 'order_id', v_order_id));
-- Step 1: 获取订单明细
SELECT product_id, quantity, unit_price, subtotal INTO v_product_id, v_quantity, v_unit_price, v_total
FROM order_items WHERE order_id = v_order_id LIMIT 1;
-- Step 2: 更新订单状态为取消
UPDATE orders SET status = 'cancelled', updated_at = NOW(6) WHERE order_id = v_order_id;
INSERT INTO saga_log (saga_id, step_name, step_order, status)
VALUES (v_saga_id, 'ORDER_CANCELLED', 1, 'completed');
-- Step 3: 恢复库存
UPDATE products SET stock_qty = stock_qty + v_quantity WHERE product_id = v_product_id;
INSERT INTO saga_log (saga_id, step_name, step_order, status)
VALUES (v_saga_id, 'STOCK_RESTORED', 2, 'completed');
-- Step 4: 退款
UPDATE account_ledger SET balance = balance + v_total, version = version + 1
WHERE customer_id = v_customer_id;
INSERT INTO saga_log (saga_id, step_name, step_order, status)
VALUES (v_saga_id, 'REFUND_DONE', 3, 'completed');
-- Step 5: 记录库存日志
INSERT INTO inventory_log (product_id, change_type, change_qty, order_id, reference_no)
VALUES (v_product_id, 'unreserved', v_quantity, v_order_id, CONCAT('CANCEL-', p_order_no));
INSERT INTO saga_log (saga_id, step_name, step_order, status)
VALUES (v_saga_id, 'LOG_RECORDED', 4, 'completed');
COMMIT;
-- 标记Saga完成
UPDATE saga_log SET status = 'completed', completed_at = NOW(6)
WHERE saga_id = v_saga_id AND step_order = 0;
SET p_result = 'SUCCESS';
END IF;
END //
DELIMITER ;
-- 2.2 执行订单创建Saga
SELECT '--- 执行订单创建Saga ---' AS action;
SET @saga_id = '';
SET @result = '';
CALL saga_create_order(1, 1, 1, 'SG-TEST-001', @saga_id, @result);
SELECT @saga_id AS saga_id, @result AS result;
-- 查看Saga执行日志
SELECT * FROM saga_log WHERE saga_id = @saga_id;
-- 查看订单状态
SELECT order_no, status, total_amount FROM orders WHERE order_no = 'SG-TEST-001';
-- 查看库存变化
SELECT product_id, stock_qty FROM products WHERE product_id = 1;
-- 查看账户余额变化
SELECT customer_id, balance FROM account_ledger WHERE customer_id = 1;
-- 2.3 执行订单取消Saga
SELECT '--- 执行订单取消Saga ---' AS action;
SET @cancel_saga_id = '';
SET @cancel_result = '';
CALL saga_cancel_order('SG-TEST-001', @cancel_saga_id, @cancel_result);
SELECT @cancel_saga_id AS cancel_saga_id, @cancel_result AS cancel_result;
-- 查看补偿日志
SELECT * FROM saga_log WHERE saga_id = @cancel_saga_id;
-- 验证最终状态
SELECT order_no, status FROM orders WHERE order_no = 'SG-TEST-001';
SELECT product_id, stock_qty FROM products WHERE product_id = 1;
SELECT customer_id, balance FROM account_ledger WHERE customer_id = 1;
-- ============================================================
-- 3. 通用Saga编排器（支持任意步骤组合）
-- ============================================================
SELECT '=== 3. 通用Saga编排器 ===' AS step;
-- 创建Saga步骤定义表
CREATE TABLE IF NOT EXISTS saga_steps (
step_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
saga_type VARCHAR(50) NOT NULL COMMENT 'Saga类型：create_order/cancel_order',
step_name VARCHAR(100) NOT NULL COMMENT '步骤名称',
step_order INT NOT NULL COMMENT '执行顺序',
step_proc VARCHAR(200) NOT NULL COMMENT '执行的存储过程',
compensate_proc VARCHAR(200) DEFAULT NULL COMMENT '补偿存储过程',
params JSON DEFAULT NULL COMMENT '步骤参数',
INDEX idx_saga_type (saga_type, step_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 填充订单创建的Saga步骤
TRUNCATE TABLE saga_steps;
INSERT INTO saga_steps (saga_type, step_name, step_order, step_proc, params) VALUES
('create_order', 'create_order', 1, 'saga_create_order',
JSON_OBJECT('customer_id', 1, 'product_id', 1, 'quantity', 1, 'order_no', 'SG-GENERIC-001')),
('create_order', 'deduct_stock', 2, 'saga_create_order', NULL),
('create_order', 'process_payment', 3, 'saga_create_order', NULL),
('create_order', 'send_notification', 4, 'saga_create_order', NULL);
-- 创建通用Saga执行器
DELIMITER //
CREATE PROCEDURE execute_saga(
IN p_saga_type VARCHAR(50),
IN p_params JSON,
OUT p_saga_id VARCHAR(64),
OUT p_result VARCHAR(500)
)
BEGIN
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_step_name VARCHAR(100);
DECLARE v_step_order INT;
DECLARE v_step_proc VARCHAR(200);
DECLARE v_current_params JSON;
DECLARE step_cursor CURSOR FOR
SELECT step_name, step_order, step_proc, params
FROM saga_steps
WHERE saga_type = p_saga_type
ORDER BY step_order;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
SET p_saga_id = CONCAT('SG-', p_saga_type, '-', UUID());
-- 记录Saga开始
INSERT INTO saga_log (saga_id, step_name, step_order, status, request_data)
VALUES (p_saga_id, 'SAGA_START', 0, 'executing', p_params);
START TRANSACTION;
OPEN step_cursor;
execute_steps: LOOP
FETCH step_cursor INTO v_step_name, v_step_order, v_step_proc, v_current_params;
IF v_done THEN
LEAVE execute_steps;
END IF;
-- 执行步骤（简化版，实际应根据step_name调用不同逻辑）
INSERT INTO saga_log (saga_id, step_name, step_order, status, response_data)
VALUES (p_saga_id, v_step_name, v_step_order, 'completed',
JSON_OBJECT('proc', v_step_proc, 'params', v_current_params));
END LOOP;
CLOSE step_cursor;
COMMIT;
-- 标记Saga完成
UPDATE saga_log SET status = 'completed', completed_at = NOW(6)
WHERE saga_id = p_saga_id AND step_order = 0;
SET p_result = 'SUCCESS';
END //
DELIMITER ;
-- 调用通用Saga编排器
SET @generic_saga_id = '';
SET @generic_result = '';
CALL execute_saga('create_order',
JSON_OBJECT('customer_id', 1, 'product_id', 1, 'quantity', 1),
@generic_saga_id, @generic_result);
SELECT @generic_saga_id AS saga_id, @generic_result AS result;
-- ============================================================
-- 4. 最终一致性验证
-- ============================================================
SELECT '=== 4. 最终一致性验证 ===' AS step;
-- 验证所有数据一致性
SELECT '--- 订单一致性检查 ---' AS check_type;
SELECT o.order_no, o.status, o.total_amount,
COALESCE(SUM(oi.subtotal), 0) AS items_total
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_no, o.status, o.total_amount
HAVING ABS(o.total_amount - items_total) > 0.01;
SELECT '--- 库存一致性检查 ---' AS check_type;
SELECT p.product_id, p.product_name, p.stock_qty,
COALESCE(SUM(CASE WHEN il.change_type = 'out' THEN ABS(il.change_qty)
WHEN il.change_type = 'in' THEN il.change_qty ELSE 0 END), 0) AS total_changed
FROM products p
LEFT JOIN inventory_log il ON p.product_id = il.product_id
GROUP BY p.product_id, p.product_name, p.stock_qty
HAVING p.stock_qty < 0;
SELECT '--- Saga状态汇总 ---' AS check_type;
SELECT saga_id, step_name, step_order, status, created_at
FROM saga_log
ORDER BY saga_id, step_order;
-- 清理测试Saga步骤
DROP TABLE IF EXISTS saga_steps;
SELECT 'Saga模式与死锁预防演示完成' AS status;
扫一扫
202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
