sql: Transaction & Concurrency Patterns using sql server 2019
-- ============================================================================
-- 文件：sqlserver2019_schema.sql
-- 说明：SQL Server 2019 珠宝行业 - 建库建表
-- 执行顺序：第 1 步
-- ============================================================================
-- 1. 创建数据库
IF DB_ID('JewelryDemo') IS NOT NULL
DROP DATABASE JewelryDemo;
GO
CREATE DATABASE JewelryDemo
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'JewelryDemo',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\JewelryDemo.mdf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB
)
LOG ON
(
NAME = N'JewelryDemo_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\JewelryDemo_log.ldf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB
);
GO
USE JewelryDemo;
GO
-- 1.1 珠宝分类表（邻接表层次模型）
CREATE TABLE jewelry_category (
cat_id INT IDENTITY(1,1) PRIMARY KEY,
parent_id INT NULL,
cat_name VARCHAR(50) NOT NULL,
cat_level TINYINT NOT NULL DEFAULT 1,
sort_order INT DEFAULT 0,
is_active BIT DEFAULT 1,
CONSTRAINT FK_category_parent FOREIGN KEY (parent_id) REFERENCES jewelry_category(cat_id) ON DELETE SET NULL
);
GO
-- 1.2 产品表
CREATE TABLE products (
product_id INT IDENTITY(1,1) PRIMARY KEY,
cat_id INT NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_code VARCHAR(30) NOT NULL,
metal_type VARCHAR(20) DEFAULT '黄金',
gem_type VARCHAR(20) DEFAULT '无',
carat_weight DECIMAL(8,2) DEFAULT 0.00,
unit_price DECIMAL(12,2) NOT NULL,
stock_qty INT DEFAULT 0,
is_active BIT DEFAULT 1,
create_time DATETIME2 DEFAULT SYSUTCDATETIME(),
CONSTRAINT FK_products_category FOREIGN KEY (cat_id) REFERENCES jewelry_category(cat_id),
CONSTRAINT CK_products_metal CHECK (metal_type IN ('黄金','铂金','18K金','银','其他')),
CONSTRAINT CK_products_gem CHECK (gem_type IN ('钻石','红宝石','蓝宝石','翡翠','珍珠','无')),
CONSTRAINT CK_products_price CHECK (unit_price >= 0),
CONSTRAINT UQ_products_code UNIQUE (product_code)
);
GO
-- 1.3 客户表
CREATE TABLE customers (
customer_id INT IDENTITY(1,1) PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(100),
membership VARCHAR(20) DEFAULT '普通',
city VARCHAR(30) DEFAULT '深圳',
reg_date DATE DEFAULT CAST(SYSUTCDATETIME() AS DATE),
total_spent DECIMAL(14,2) DEFAULT 0.00,
CONSTRAINT UQ_customers_phone UNIQUE (phone),
CONSTRAINT CK_customers_membership CHECK (membership IN ('普通','银卡','金卡','钻石','黑金'))
);
GO
-- 1.4 订单主表
CREATE TABLE orders (
order_id INT IDENTITY(1,1) PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
sales_channel VARCHAR(20) DEFAULT '线下门店',
store_id INT NULL,
total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
discount DECIMAL(4,2) DEFAULT 1.00,
status VARCHAR(20) DEFAULT '待付款',
remark VARCHAR(200) NULL,
CONSTRAINT FK_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CONSTRAINT CK_orders_channel CHECK (sales_channel IN ('线下门店','天猫旗舰店','微信小程序','抖音直播','经销商')),
CONSTRAINT CK_orders_status CHECK (status IN ('待付款','已付款','已发货','已完成','已取消','已退货'))
);
GO
-- 1.5 订单明细表
CREATE TABLE order_items (
item_id INT IDENTITY(1,1) PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(12,2) NOT NULL,
discount DECIMAL(4,2) DEFAULT 1.00,
subtotal AS (ROUND(quantity * unit_price * discount, 2)) PERSISTED,
CONSTRAINT FK_orderitems_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
CONSTRAINT FK_orderitems_product FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT CK_orderitems_qty CHECK (quantity > 0)
);
GO
-- 1.6 库存表（用于并发控制演示）
CREATE TABLE inventory (
inventory_id INT IDENTITY(1,1) PRIMARY KEY,
product_id INT NOT NULL,
warehouse VARCHAR(30) NOT NULL DEFAULT '深圳仓',
quantity INT NOT NULL DEFAULT 0,
reserved_qty INT NOT NULL DEFAULT 0,
version ROWVERSION,
CONSTRAINT FK_inventory_product FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT CK_inventory_qty CHECK (quantity >= 0 AND reserved_qty >= 0)
);
GO
-- 1.7 账户表（用于事务转账演示）
CREATE TABLE accounts (
account_id INT IDENTITY(1,1) PRIMARY KEY,
account_name VARCHAR(50) NOT NULL,
balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
version ROWVERSION,
CONSTRAINT CK_accounts_balance CHECK (balance >= 0)
);
GO
-- 1.8 死锁演示表
CREATE TABLE deadlock_demo (
id INT IDENTITY(1,1) PRIMARY KEY,
data_col VARCHAR(50) NOT NULL,
update_time DATETIME2 DEFAULT SYSUTCDATETIME()
);
GO
-- 1.9 Saga流程表（用于Saga模式实现）
CREATE TABLE saga_log (
saga_id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
step_name VARCHAR(50) NOT NULL,
order_id INT NULL,
customer_id INT NULL,
product_id INT NULL,
quantity INT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
error_message VARCHAR(500) NULL,
created_at DATETIME2 DEFAULT SYSUTCDATETIME(),
completed_at DATETIME2 NULL,
PRIMARY KEY (saga_id, step_name, order_id)
);
GO
-- 1.10 创建索引
CREATE INDEX idx_category_parent ON jewelry_category(parent_id);
CREATE INDEX idx_category_level ON jewelry_category(cat_level);
CREATE INDEX idx_products_category ON products(cat_id);
CREATE INDEX idx_products_code ON products(product_code);
CREATE INDEX idx_products_metal ON products(metal_type);
CREATE INDEX idx_products_active ON products(is_active);
CREATE INDEX idx_customers_membership ON customers(membership);
CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_channel ON orders(sales_channel);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_channel_date ON orders(sales_channel, order_date);
CREATE INDEX idx_orderitems_order ON order_items(order_id);
CREATE INDEX idx_orderitems_product ON order_items(product_id);
CREATE INDEX idx_inventory_product ON inventory(product_id);
CREATE INDEX idx_saga_status ON saga_log(status) WHERE status IN ('PENDING', 'RUNNING');
GO
-- 1.11 添加表注释（使用扩展属性）
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'珠宝分类表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'jewelry_category';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'珠宝产品表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'products';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'客户表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'customers';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'订单主表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'orders';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'订单明细表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'order_items';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'库存表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'inventory';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'账户表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'accounts';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'死锁演示表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'deadlock_demo';
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'Saga流程日志表', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'saga_log';
GO
-- 1.12 开启数据库级功能
ALTER DATABASE JewelryDemo SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE JewelryDemo SET READ_COMMITTED_SNAPSHOT ON;
GO
SELECT 'sqlserver2019_schema.sql 执行完成' AS result;
GO
-- ============================================================================
-- 文件：sqlserver2019_data.sql
-- 说明：插入分类、产品、客户、订单、明细、库存、账户、死锁演示数据
-- 执行顺序：第 2 步（需先执行 sqlserver2019_schema.sql）
-- ============================================================================
USE JewelryDemo;
GO
-- 2.1 分类数据（三级树形结构）
INSERT INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES
(1, NULL, '珠宝首饰', 1, 1),
(2, NULL, '投资金条', 1, 2),
(3, NULL, '定制服务', 1, 3),
(10, 1, '戒指', 2, 1),
(11, 1, '项链', 2, 2),
(12, 1, '手镯', 2, 3),
(13, 1, '耳环', 2, 4),
(14, 1, '吊坠', 2, 5),
(15, 1, '胸针', 2, 6),
(100, 10, '钻石戒指', 3, 1),
(101, 10, '黄金戒指', 3, 2),
(102, 10, '翡翠戒指', 3, 3),
(110, 11, '钻石项链', 3, 1),
(111, 11, '珍珠项链', 3, 2),
(112, 11, '黄金项链', 3, 3),
(120, 12, '黄金手镯', 3, 1),
(121, 12, '铂金手镯', 3, 2),
(130, 13, '钻石耳环', 3, 1),
(131, 13, '珍珠耳环', 3, 2),
(140, 14, '翡翠吊坠', 3, 1),
(141, 14, '钻石吊坠', 3, 2);
GO
-- 2.2 产品数据（50条）
INSERT INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES
(100, '永恒之心钻戒', 'R-DIA-001', '铂金', '钻石', 1.00, 58888.00, 5),
(100, '星光钻戒', 'R-DIA-002', '18K金', '钻石', 0.50, 28800.00, 12),
(100, '经典六爪钻戒', 'R-DIA-003', '铂金', '钻石', 0.80, 42800.00, 8),
(101, '福字黄金戒指', 'R-GOL-001', '黄金', '无', 0.00, 8900.00, 30),
(101, '古法金戒指', 'R-GOL-002', '黄金', '无', 0.00, 12600.00, 20),
(102, '翡翠玉戒指', 'R-JAD-001', '黄金', '翡翠', 0.00, 15800.00, 10),
(110, '海洋之泪项链', 'N-DIA-001', '铂金', '钻石', 2.00, 128000.00, 3),
(110, '锁骨链', 'N-DIA-002', '18K金', '钻石', 0.30, 15800.00, 25),
(111, '南洋珍珠项链', 'N-PEA-001', '黄金', '珍珠', 0.00, 36800.00, 8),
(111, '淡水珍珠项链', 'N-PEA-002', '银', '珍珠', 0.00, 2800.00, 50),
(112, '龙凤黄金项链', 'N-GOL-001', '黄金', '无', 0.00, 22800.00, 15),
(120, '古法金手镯', 'B-GOL-001', '黄金', '无', 0.00, 35800.00, 10),
(120, '传承金手镯', 'B-GOL-002', '黄金', '无', 0.00, 28600.00, 18),
(121, '铂金光面手镯', 'B-PLA-001', '铂金', '无', 0.00, 18900.00, 12),
(130, '钻石耳钉', 'E-DIA-001', '铂金', '钻石', 0.50, 22800.00, 20),
(130, '钻石耳环', 'E-DIA-002', '18K金', '钻石', 1.00, 45800.00, 6),
(131, '珍珠耳坠', 'E-PEA-001', '黄金', '珍珠', 0.00, 5800.00, 35),
(140, '帝王绿翡翠吊坠', 'P-JAD-001', '黄金', '翡翠', 0.00, 68000.00, 4),
(140, '冰种翡翠吊坠', 'P-JAD-002', '18K金', '翡翠', 0.00, 28800.00, 12),
(141, '钻石吊坠', 'P-DIA-001', '铂金', '钻石', 0.60, 32800.00, 15),
(100, '群镶钻戒', 'R-DIA-004', '18K金', '钻石', 0.40, 19800.00, 18),
(101, '转运珠戒指', 'R-GOL-003', '黄金', '无', 0.00, 6800.00, 40),
(110, '心形钻石项链', 'N-DIA-003', '铂金', '钻石', 1.50, 88000.00, 5),
(112, '足金项链', 'N-GOL-002', '黄金', '无', 0.00, 18600.00, 22),
(120, '花丝金手镯', 'B-GOL-003', '黄金', '无', 0.00, 42000.00, 6),
(130, '群镶钻石耳环', 'E-DIA-003', '铂金', '钻石', 0.80, 36800.00, 10),
(141, '水滴钻石吊坠', 'P-DIA-002', '18K金', '钻石', 0.40, 18800.00, 20),
(100, '玫瑰金钻戒', 'R-DIA-005', '18K金', '钻石', 0.60, 25800.00, 14),
(111, 'Akoya珍珠项链', 'N-PEA-003', '铂金', '珍珠', 0.00, 28800.00, 10),
(140, '飘花翡翠吊坠', 'P-JAD-003', '黄金', '翡翠', 0.00, 38800.00, 8),
(101, '花戒', 'R-GOL-004', '黄金', '无', 0.00, 5600.00, 50),
(121, '钻石铂金手镯', 'B-PLA-002', '铂金', '钻石', 0.30, 52800.00, 4),
(131, '金珠珍珠耳环', 'E-PEA-002', '黄金', '珍珠', 0.00, 3800.00, 45),
(112, '十字金刚杵项链', 'N-GOL-003', '黄金', '无', 0.00, 15800.00, 20),
(141, '群镶钻石吊坠', 'P-DIA-003', '铂金', '钻石', 1.20, 68000.00, 3),
(100, '排钻戒', 'R-DIA-006', '铂金', '钻石', 0.90, 38800.00, 10),
(110, 'Y字钻石项链', 'N-DIA-004', '18K金', '钻石', 0.20, 12800.00, 30),
(120, '推拉金手镯', 'B-GOL-004', '黄金', '无', 0.00, 16800.00, 25),
(130, '珍珠钻石耳环', 'E-DIA-004', '18K金', '钻石', 0.25, 16800.00, 18),
(140, '满绿翡翠吊坠', 'P-JAD-004', '黄金', '翡翠', 0.00, 128000.00, 2),
(101, '开口黄金戒指', 'R-GOL-005', '黄金', '无', 0.00, 7800.00, 35),
(111, '巴洛克珍珠项链', 'N-PEA-004', '银', '珍珠', 0.00, 1800.00, 60),
(121, '珐琅铂金手镯', 'B-PLA-003', '铂金', '无', 0.00, 22800.00, 8),
(131, '大溪地珍珠耳环', 'E-PEA-003', '铂金', '珍珠', 0.00, 12800.00, 12),
(141, '心形钻石吊坠', 'P-DIA-004', '铂金', '钻石', 0.50, 28800.00, 10),
(100, '扭臂钻戒', 'R-DIA-007', '18K金', '钻石', 0.70, 32800.00, 12),
(110, '锁骨钻石项链', 'N-DIA-005', '铂金', '钻石', 0.40, 22800.00, 18),
(120, '珐琅金手镯', 'B-GOL-005', '黄金', '无', 0.00, 38800.00, 8),
(130, '流苏钻石耳环', 'E-DIA-005', '18K金', '钻石', 0.60, 28800.00, 15),
(140, '紫罗兰翡翠吊坠', 'P-JAD-005', '18K金', '翡翠', 0.00, 48800.00, 6);
GO
-- 2.3 客户数据（30条）
INSERT INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES
('张美玲', '13800138001', 'zhangml@email.com', '钻石', '深圳', '2022-03-15', 286000.00),
('李华', '13800138002', 'lihua@email.com', '金卡', '广州', '2022-06-20', 128000.00),
('王芳', '13800138003', 'wangfang@email.com', '黑金', '北京', '2021-01-10', 568000.00),
('赵强', '13800138004', 'zhaoq@email.com', '银卡', '上海', '2023-02-28', 45000.00),
('陈静', '13800138005', 'chenj@email.com', '钻石', '深圳', '2022-08-15', 328000.00),
('刘洋', '13800138006', 'liuyang@email.com', '普通', '成都', '2023-05-01', 12800.00),
('周婷', '13800138007', 'zhout@email.com', '金卡', '杭州', '2022-11-20', 98000.00),
('吴磊', '13800138008', 'wulei@email.com', '普通', '武汉', '2023-08-10', 8900.00),
('郑雪', '13800138009', 'zhengx@email.com', '银卡', '南京', '2023-01-05', 36800.00),
('孙丽', '13800138010', 'sunli@email.com', '钻石', '深圳', '2021-09-18', 428000.00),
('马丽', '13800138011', 'mali@email.com', '金卡', '广州', '2022-04-22', 158000.00),
('朱伟', '13800138012', 'zhuw@email.com', '普通', '重庆', '2023-06-15', 22800.00),
('胡敏', '13800138013', 'hum@email.com', '银卡', '西安', '2022-12-01', 58800.00),
('林涛', '13800138014', 'lint@email.com', '黑金', '深圳', '2021-05-20', 688000.00),
('何燕', '13800138015', 'hey@email.com', '金卡', '长沙', '2022-09-10', 118000.00),
('高飞', '13800138016', 'gaof@email.com', '普通', '郑州', '2023-03-25', 15800.00),
('罗琳', '13800138017', 'luol@email.com', '钻石', '北京', '2021-11-08', 358000.00),
('梁军', '13800138018', 'liangj@email.com', '银卡', '天津', '2023-04-12', 42800.00),
('宋佳', '13800138019', 'songj@email.com', '金卡', '苏州', '2022-07-30', 138000.00),
('韩梅', '13800138020', 'hanm@email.com', '普通', '青岛', '2023-09-05', 6800.00),
('唐勇', '13800138021', 'tangy@email.com', '钻石', '深圳', '2021-08-15', 488000.00),
('冯娟', '13800138022', 'fengj@email.com', '金卡', '东莞', '2022-10-20', 168000.00),
('曹阳', '13800138023', 'caoy@email.com', '普通', '佛山', '2023-07-18', 19800.00),
('许娜', '13800138024', 'xun@email.com', '银卡', '珠海', '2022-05-08', 68800.00),
('邓超', '13800138025', 'dengc@email.com', '黑金', '广州', '2021-03-22', 728000.00),
('萧红', '13800138026', 'xiaoh@email.com', '金卡', '厦门', '2022-08-28', 148000.00),
('程亮', '13800138027', 'chengl@email.com', '普通', '合肥', '2023-10-01', 12800.00),
('潘婷', '13800138028', 'pant@email.com', '钻石', '深圳', '2021-12-15', 398000.00),
('袁丽', '13800138029', 'yuanl@email.com', '银卡', '南昌', '2023-02-14', 48800.00),
('蒋文', '13800138030', 'jiangw@email.com', '金卡', '宁波', '2022-11-05', 108000.00);
GO
-- 2.4 订单主表（30条）
INSERT INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES
(1, 1, '2024-01-05 10:30:00', '线下门店', 1, 58888.00, 0.95, '已完成'),
(2, 2, '2024-01-08 14:20:00', '天猫旗舰店', NULL, 28800.00, 1.00, '已完成'),
(3, 3, '2024-01-12 09:15:00', '线下门店', 2, 128000.00, 0.90, '已完成'),
(4, 4, '2024-01-15 16:45:00', '微信小程序', NULL, 8900.00, 1.00, '已完成'),
(5, 5, '2024-01-20 11:00:00', '抖音直播', NULL, 15800.00, 0.88, '已完成'),
(6, 6, '2024-01-25 13:30:00', '线下门店', 1, 22800.00, 1.00, '已完成'),
(7, 7, '2024-02-01 10:00:00', '天猫旗舰店', NULL, 36800.00, 0.92, '已完成'),
(8, 8, '2024-02-05 15:20:00', '微信小程序', NULL, 5800.00, 1.00, '已完成'),
(9, 9, '2024-02-10 09:30:00', '线下门店', 3, 42800.00, 0.95, '已完成'),
(10, 10, '2024-02-14 18:00:00', '天猫旗舰店', NULL, 88000.00, 0.90, '已完成'),
(11, 11, '2024-02-20 14:00:00', '抖音直播', NULL, 18600.00, 0.85, '已完成'),
(12, 12, '2024-03-01 10:15:00', '线下门店', 1, 35800.00, 1.00, '已完成'),
(13, 13, '2024-03-05 16:30:00', '微信小程序', NULL, 28800.00, 1.00, '已完成'),
(14, 14, '2024-03-10 11:45:00', '线下门店', 2, 68000.00, 0.92, '已完成'),
(15, 15, '2024-03-15 09:00:00', '天猫旗舰店', NULL, 15800.00, 1.00, '已完成'),
(16, 16, '2024-03-20 14:30:00', '抖音直播', NULL, 12800.00, 0.88, '已完成'),
(17, 17, '2024-03-25 10:00:00', '线下门店', 1, 45800.00, 0.95, '已完成'),
(18, 18, '2024-04-01 15:00:00', '微信小程序', NULL, 22800.00, 1.00, '已完成'),
(19, 19, '2024-04-05 11:30:00', '天猫旗舰店', NULL, 32800.00, 0.90, '已完成'),
(20, 20, '2024-04-10 09:45:00', '线下门店', 3, 5800.00, 1.00, '已完成'),
(21, 21, '2024-04-15 16:00:00', '抖音直播', NULL, 128000.00, 0.85, '已完成'),
(22, 22, '2024-04-20 10:30:00', '线下门店', 2, 28800.00, 0.95, '已完成'),
(23, 23, '2024-04-25 14:15:00', '微信小程序', NULL, 18900.00, 1.00, '已完成'),
(24, 24, '2024-05-01 09:00:00', '天猫旗舰店', NULL, 68000.00, 0.90, '已完成'),
(25, 25, '2024-05-05 15:30:00', '线下门店', 1, 42000.00, 1.00, '已完成'),
(26, 26, '2024-05-10 11:00:00', '抖音直播', NULL, 16800.00, 0.88, '已完成'),
(27, 27, '2024-05-15 10:45:00', '微信小程序', NULL, 8900.00, 1.00, '已完成'),
(28, 28, '2024-05-20 16:30:00', '天猫旗舰店', NULL, 52800.00, 0.92, '已完成'),
(29, 29, '2024-05-25 13:00:00', '线下门店', 2, 28800.00, 1.00, '已完成'),
(30, 30, '2024-06-01 09:30:00', '天猫旗舰店', NULL, 12800.00, 1.00, '已完成');
GO
-- 2.5 订单明细表
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES
(1, 1, 1, 58888.00, 0.95),
(2, 2, 1, 28800.00, 1.00),
(3, 7, 1, 128000.00, 0.90),
(4, 4, 1, 8900.00, 1.00),
(5, 8, 1, 15800.00, 0.88),
(6, 15, 1, 22800.00, 1.00),
(7, 9, 1, 36800.00, 0.92),
(8, 17, 1, 5800.00, 1.00),
(9, 12, 1, 42800.00, 0.95),
(10, 23, 1, 88000.00, 0.90),
(11, 24, 1, 18600.00, 0.85),
(12, 12, 1, 35800.00, 1.00),
(13, 20, 1, 28800.00, 1.00),
(14, 18, 1, 68000.00, 0.92),
(15, 8, 1, 15800.00, 1.00),
(16, 37, 1, 12800.00, 0.88),
(17, 16, 1, 45800.00, 0.95),
(18, 24, 1, 18600.00, 1.00),
(19, 21, 1, 32800.00, 0.90),
(20, 17, 1, 5800.00, 1.00),
(21, 7, 1, 128000.00, 0.85),
(22, 20, 1, 28800.00, 0.95),
(23, 14, 1, 18900.00, 1.00),
(24, 18, 1, 68000.00, 0.90),
(25, 25, 1, 42000.00, 1.00),
(26, 39, 1, 16800.00, 0.88),
(27, 4, 1, 8900.00, 1.00),
(28, 31, 1, 52800.00, 0.92),
(29, 20, 1, 28800.00, 1.00),
(30, 37, 1, 12800.00, 1.00);
GO
-- 2.6 库存数据
INSERT INTO inventory (product_id, warehouse, quantity, reserved_qty) VALUES
(1, '深圳仓', 5, 0), (2, '深圳仓', 12, 0), (3, '深圳仓', 8, 0), (4, '深圳仓', 30, 0), (5, '深圳仓', 20, 0),
(6, '深圳仓', 10, 0), (7, '深圳仓', 3, 0), (8, '深圳仓', 25, 0), (9, '深圳仓', 8, 0), (10, '深圳仓', 50, 0),
(11, '深圳仓', 15, 0), (12, '深圳仓', 10, 0), (13, '深圳仓', 18, 0), (14, '深圳仓', 12, 0), (15, '深圳仓', 20, 0),
(16, '深圳仓', 6, 0), (17, '深圳仓', 35, 0), (18, '深圳仓', 4, 0), (19, '深圳仓', 12, 0), (20, '深圳仓', 15, 0),
(21, '深圳仓', 18, 0), (22, '深圳仓', 40, 0), (23, '深圳仓', 5, 0), (24, '深圳仓', 22, 0), (25, '深圳仓', 6, 0),
(26, '深圳仓', 10, 0), (27, '深圳仓', 20, 0), (28, '深圳仓', 14, 0), (29, '深圳仓', 10, 0), (30, '深圳仓', 8, 0),
(31, '深圳仓', 50, 0), (32, '深圳仓', 4, 0), (33, '深圳仓', 45, 0), (34, '深圳仓', 20, 0), (35, '深圳仓', 3, 0),
(36, '深圳仓', 10, 0), (37, '深圳仓', 30, 0), (38, '深圳仓', 25, 0), (39, '深圳仓', 18, 0), (40, '深圳仓', 2, 0),
(41, '深圳仓', 35, 0), (42, '深圳仓', 60, 0), (43, '深圳仓', 8, 0), (44, '深圳仓', 12, 0), (45, '深圳仓', 10, 0),
(46, '深圳仓', 12, 0), (47, '深圳仓', 18, 0), (48, '深圳仓', 8, 0), (49, '深圳仓', 15, 0), (50, '深圳仓', 6, 0);
GO
-- 2.7 账户数据（用于事务转账演示）
INSERT INTO accounts (account_name, balance) VALUES
('珠宝商城营收账户', 5000000.00),
('供应商结算账户', 1000000.00),
('员工工资账户', 500000.00),
('营销活动账户', 200000.00),
('仓储物流账户', 300000.00);
GO
-- 2.8 死锁演示数据
INSERT INTO deadlock_demo (data_col) VALUES ('死锁演示行A'), ('死锁演示行B'), ('死锁演示行C'), ('死锁演示行D');
GO
SELECT 'sqlserver2019_data.sql 执行完成' AS result;
GO
-- ============================================================================
-- 文件：sqlserver2019_transactions.sql
-- 说明：ACID事务控制、5种隔离级别演示、事务嵌套与保存点、XACT_ABORT
-- 执行顺序：第 3 步（需先执行 schema + data）
-- ============================================================================
USE JewelryDemo;
GO
-- ============================================================================
-- 场景1：ACID 事务控制 - 珠宝采购转账
-- 演示：从营收账户转账到供应商账户，保证原子性
-- ============================================================================
-- 1.1 基本事务控制（BEGIN TRAN / COMMIT / ROLLBACK）
PRINT '=== 1.1 基本事务控制：珠宝采购转账 ===';
GO
BEGIN TRAN;
BEGIN TRY
-- 从营收账户扣款
UPDATE accounts SET balance = balance - 50000.00
WHERE account_name = '珠宝商城营收账户';
-- 给供应商账户入账
UPDATE accounts SET balance = balance + 50000.00
WHERE account_name = '供应商结算账户';
-- 记录操作日志
INSERT INTO saga_log (step_name, order_id, status, error_message)
VALUES ('采购转账完成', NULL, 'COMPLETED', NULL);
COMMIT TRAN;
PRINT '事务提交成功：营收账户 -> 供应商账户 50000元';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrSeverity INT = ERROR_SEVERITY();
DECLARE @ErrState INT = ERROR_STATE();
RAISERROR(@ErrMsg, @ErrSeverity, @ErrState);
END CATCH
GO
-- 1.2 显式回滚演示（模拟库存不足时取消订单）
PRINT '=== 1.2 显式回滚：模拟库存不足取消订单 ===';
GO
DECLARE @order_id INT = 999;
DECLARE @product_id INT = 1;
DECLARE @qty INT = 100;
DECLARE @available INT;
-- 检查可用库存（可用 = 库存 - 预占）
SELECT @available = quantity - reserved_qty
FROM inventory WITH (UPDLOCK, ROWLOCK)
WHERE product_id = @product_id;
IF @available >= @qty
BEGIN
BEGIN TRAN;
BEGIN TRY
-- 扣减库存
UPDATE inventory SET quantity = quantity - @qty
WHERE product_id = @product_id;
-- 创建订单
INSERT INTO orders (customer_id, order_date, sales_channel, total_amount, status)
VALUES (1, SYSUTCDATETIME(), '线下门店', 5888800.00, '待付款');
SET @order_id = SCOPE_IDENTITY();
-- 插入明细
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount)
VALUES (@order_id, @product_id, @qty, 58888.00, 1.00);
COMMIT TRAN;
PRINT '事务提交：订单 ' + CAST(@order_id AS VARCHAR) + ' 创建成功';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
PRINT '事务回滚：库存不足或异常，已撤销所有更改';
PRINT '错误信息：' + ERROR_MESSAGE();
END CATCH
END
ELSE
BEGIN
PRINT '库存不足！可用: ' + CAST(@available AS VARCHAR) + ', 需要: ' + CAST(@qty AS VARCHAR);
END
GO
-- ============================================================================
-- 场景2：5种隔离级别演示
-- ============================================================================
PRINT '=== 2. 隔离级别演示 ===';
GO
-- 2.1 读取未提交数据（READ UNCOMMITTED / NOLOCK）- 脏读
PRINT '--- 2.1 READ UNCOMMITTED（脏读风险）---';
GO
-- 会话A：开始事务但不提交
-- BEGIN TRAN;
-- UPDATE products SET unit_price = 99999.00 WHERE product_id = 1;
-- （不提交，不关闭会话）
-- 会话B：使用 NOLOCK 读取未提交数据（可能读到脏数据）
SELECT product_name, unit_price
FROM products WITH (NOLOCK)
WHERE product_id = 1;
-- 可能读到 99999.00（未提交的修改）或原始值
-- 会话B：使用 READCOMMITTED（默认，无脏读）
SELECT product_name, unit_price
FROM products
WHERE product_id = 1;
-- 等待会话A提交或回滚后读取正确值
GO
-- 2.2 可重复读（REPEATABLE READ）
PRINT '--- 2.2 REPEATABLE READ（防止脏读和不可重复读）---';
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
-- 第一次读取
SELECT COUNT(*) AS order_count FROM orders WHERE customer_id = 1;
-- 会话A在此期间可能插入新订单（但SELECT不会读到）
-- 第二次读取（保证相同）
SELECT COUNT(*) AS order_count FROM orders WHERE customer_id = 1;
COMMIT TRAN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
-- 2.3 可序列化（SERIALIZABLE）- 最强隔离
PRINT '--- 2.3 SERIALIZABLE（防止幻读）---';
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
-- 范围查询加范围锁
SELECT * FROM products WHERE cat_id = 100 AND unit_price > 20000;
-- 其他会话无法在范围内插入数据
COMMIT TRAN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
-- 2.4 快照隔离（SNAPSHOT ISOLATION）
PRINT '--- 2.4 SNAPSHOT ISOLATION（行版本控制）---';
GO
-- 数据库已开启：ALTER DATABASE JewelryDemo SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
-- 读取当前快照，不受其他未提交事务影响
SELECT product_name, unit_price FROM products WHERE product_id = 1;
-- 即使其他会话修改了数据，这里仍读到快照时刻的值
COMMIT TRAN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
-- 2.5 已提交读快照（READ COMMITTED SNAPSHOT）
PRINT '--- 2.5 READ COMMITTED SNAPSHOT（默认RCSI）---';
GO
-- 数据库已开启：ALTER DATABASE JewelryDemo SET READ_COMMITTED_SNAPSHOT ON;
-- 默认隔离级别下自动使用行版本控制，无需额外设置
SELECT product_name, unit_price FROM products WHERE product_id = 1;
-- 不会阻塞写入事务，也不会读到脏数据
GO
-- ============================================================================
-- 场景3：事务嵌套与保存点（SAVE TRANSACTION）
-- ============================================================================
PRINT '=== 3. 事务嵌套与保存点 ===';
GO
BEGIN TRAN outer_tran;
PRINT '外层事务开始';
-- 第一步：插入客户
INSERT INTO customers (customer_name, phone, membership, city)
VALUES ('测试客户', '13800000001', '普通', '深圳');
DECLARE @cust_id INT = SCOPE_IDENTITY();
PRINT '插入客户成功，ID=' + CAST(@cust_id AS VARCHAR);
SAVE TRAN step1;
-- 第二步：创建订单
INSERT INTO orders (customer_id, order_date, sales_channel, total_amount, status)
VALUES (@cust_id, SYSUTCDATETIME(), '微信小程序', 28800.00, '待付款');
DECLARE @ord_id INT = SCOPE_IDENTITY();
PRINT '插入订单成功，ID=' + CAST(@ord_id AS VARCHAR);
SAVE TRAN step2;
-- 第三步：插入明细（模拟此处出错）
BEGIN TRY
-- 故意插入重复款号制造冲突
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount)
VALUES (@ord_id, 99999, 1, 100.00, 1.00);
-- 外键约束会失败（product_id=99999不存在）
END TRY
BEGIN CATCH
PRINT '明细插入失败，回滚到step2保存点';
ROLLBACK TRAN step2;
-- 回滚到step2后，订单也被回滚了
-- 但客户数据在step1之后，需要决定是否保留
-- 可以选择回滚到step1保留客户
-- ROLLBACK TRAN step1;
-- 或者回滚整个事务
-- ROLLBACK TRAN outer_tran;
END CATCH
-- 清理：回滚外层事务（因为明细失败了）
ROLLBACK TRAN outer_tran;
PRINT '外层事务已回滚';
GO
-- ============================================================================
-- 场景4：XACT_ABORT 设置
-- ============================================================================
PRINT '=== 4. XACT_ABORT 演示 ===';
GO
-- 设置XACT_ABORT ON：任何运行时错误自动回滚整个事务
SET XACT_ABORT ON;
BEGIN TRAN;
-- 即使发生约束冲突，整个事务也会自动回滚
INSERT INTO products (cat_id, product_name, product_code, unit_price)
VALUES (100, '测试产品', 'TEST-001', 100.00);
-- 重复插入相同product_code会失败
INSERT INTO products (cat_id, product_name, product_code, unit_price)
VALUES (100, '测试产品2', 'TEST-001', 200.00);
-- 此语句失败，但由于XACT_ABORT=ON，整个事务自动回滚
-- 第一条INSERT也会被撤销
SET XACT_ABORT OFF;
GO
-- 对比：XACT_ABORT OFF 时的行为
PRINT '=== 4b. XACT_ABORT OFF 对比 ===';
GO
SET XACT_ABORT OFF;
BEGIN TRAN;
INSERT INTO products (cat_id, product_name, product_code, unit_price)
VALUES (100, '测试产品X', 'TEST-X-001', 100.00);
-- 假设这里发生错误...
-- 错误语句失败，但事务仍然活跃，前面的INSERT不会自动回滚
-- 需要手动ROLLBACK
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN;
PRINT '手动回滚';
END
SET XACT_ABORT ON;
GO
-- ============================================================================
-- 场景5：分布式事务（MSDTC）
-- ============================================================================
PRINT '=== 5. 分布式事务演示 ===';
GO
-- SQL Server 2019 支持分布式事务（需配置MSDTC）
-- 跨服务器查询时使用分布式事务
/*
BEGIN DISTRIBUTED TRAN;
-- 本地操作
UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 1;
-- 远程服务器操作（需配置链接服务器）
-- UPDATE [RemoteServer].JewelryDemo2.dbo.products
-- SET stock_qty = stock_qty - 1 WHERE product_id = 1;
COMMIT TRAN;
END TRAN;
*/
PRINT '分布式事务示例（需配置链接服务器后启用）';
GO
-- ============================================================================
-- 场景6：事务隔离级别查询
-- ============================================================================
PRINT '=== 6. 查看当前事务隔离级别 ===';
GO
SELECT
session_id,
transaction_isolation_level,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'RepeatableRead'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS isolation_level_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO
PRINT 'sqlserver2019_transactions.sql 执行完成';
GO
-- ============================================================================
-- 文件：sqlserver2019_concurrency.sql
-- 说明：悲观锁、乐观锁、死锁检测与预防、行版本控制、时序表
-- 执行顺序：第 4 步（需先执行 schema + data）
-- ============================================================================
USE JewelryDemo;
GO
-- ============================================================================
-- 场景1：悲观锁（Pessimistic Locking）
-- ============================================================================
PRINT '=== 1. 悲观锁演示 ===';
GO
-- 1.1 UPDLOCK - 更新锁（读取时加更新锁，防止其他事务修改）
PRINT '--- 1.1 UPDLOCK：安全读取待处理订单 ---';
GO
BEGIN TRAN;
-- 使用 UPDLOCK 读取订单并锁定，防止其他会话同时修改
SELECT o.order_id, o.customer_id, o.total_amount, o.status
FROM orders o WITH (UPDLOCK, ROWLOCK)
WHERE o.order_id = 1 AND o.status = '待付款';
-- 安全地更新订单状态
UPDATE orders SET status = '已付款'
WHERE order_id = 1 AND status = '待付款';
COMMIT TRAN;
GO
-- 1.2 ROWLOCK - 行级锁（减少锁粒度）
PRINT '--- 1.2 ROWLOCK：行级锁更新库存 ---';
GO
DECLARE @product_id INT = 1;
DECLARE @qty INT = 2;
BEGIN TRAN;
-- 行级锁读取库存
DECLARE @current_stock INT;
SELECT @current_stock = quantity FROM inventory WITH (ROWLOCK, UPDLOCK)
WHERE product_id = @product_id;
IF @current_stock >= @qty
BEGIN
UPDATE inventory SET quantity = quantity - @qty
WHERE product_id = @product_id;
PRINT '库存扣减成功：产品' + CAST(@product_id AS VARCHAR) + ' 扣' + CAST(@qty AS VARCHAR);
END
ELSE
BEGIN
PRINT '库存不足！当前: ' + CAST(@current_stock AS VARCHAR);
END
COMMIT TRAN;
GO
-- 1.3 TABLOCKX - 表排他锁（批量操作时使用）
PRINT '--- 1.3 TABLOCKX：批量更新所有产品折扣 ---';
GO
BEGIN TRAN;
-- 表级排他锁，防止其他会话读取或修改
UPDATE products WITH (TABLOCKX)
SET unit_price = unit_price * 0.90 -- 全场9折
WHERE is_active = 1;
-- 记录操作
INSERT INTO saga_log (step_name, status)
VALUES ('批量折扣更新', 'COMPLETED');
COMMIT TRAN;
GO
-- 1.4 HOLDLOCK - 保持锁直到事务结束（相当于SERIALIZABLE）
PRINT '--- 1.4 HOLDLOCK：防止幻读 ---';
GO
BEGIN TRAN;
-- HOLDLOCK 保持共享锁直到事务结束
SELECT * FROM products WITH (HOLDLOCK, ROWLOCK)
WHERE cat_id = 100 AND unit_price > 20000;
-- 其他会话在此期间无法插入符合条件的行
COMMIT TRAN;
GO
-- ============================================================================
-- 场景2：乐观锁（Optimistic Locking）- 使用 ROWVERSION
-- ============================================================================
PRINT '=== 2. 乐观锁演示（ROWVERSION）===';
GO
-- inventory 和 accounts 表已定义 ROWVERSION 列
-- 2.1 乐观锁更新模式
PRINT '--- 2.1 乐观锁：库存更新（版本号检查）---';
GO
DECLARE @product_id INT = 1;
DECLARE @qty INT = 1;
DECLARE @current_version VARBINARY(8);
-- 第一步：读取数据和版本号
DECLARE @quantity INT;
SELECT @quantity = quantity, @current_version = version
FROM inventory WHERE product_id = @product_id;
PRINT '读取到库存=' + CAST(@quantity AS VARCHAR) + ', 版本=' + CAST(@current_version AS VARCHAR);
-- 第二步：在应用层做业务逻辑...
-- （模拟延迟）
-- 第三步：更新时检查版本号是否变化
UPDATE inventory
SET quantity = quantity - @qty
WHERE product_id = @product_id
AND version = @current_version; -- 乐观锁检查
IF @@ROWCOUNT = 0
BEGIN
PRINT '并发冲突！其他会话已修改此行数据，请重新读取并重试';
END
ELSE
BEGIN
PRINT '更新成功（版本一致，无并发冲突）';
END
GO
-- 2.2 乐观锁重试机制
PRINT '--- 2.2 乐观锁重试机制 ---';
GO
DECLARE @retry INT = 3;
DECLARE @success BIT = 0;
DECLARE @product_id INT = 1;
DECLARE @qty INT = 1;
WHILE @retry > 0 AND @success = 0
BEGIN
DECLARE @current_version VARBINARY(8);
DECLARE @quantity INT;
-- 读取数据和版本
SELECT @quantity = quantity, @current_version = version
FROM inventory WHERE product_id = @product_id;
-- 模拟业务处理
-- WAITFOR DELAY '00:00:01';
-- 尝试更新（带版本检查）
UPDATE inventory
SET quantity = quantity - @qty
WHERE product_id = @product_id
AND version = @current_version;
IF @@ROWCOUNT > 0
SET @success = 1;
ELSE
BEGIN
SET @retry = @retry - 1;
PRINT '冲突，剩余重试次数: ' + CAST(@retry AS VARCHAR);
-- 可选：等待后重试
-- WAITFOR DELAY '00:00:00.500';
END
END
IF @success = 1
PRINT '最终更新成功';
ELSE
PRINT '达到最大重试次数，更新失败';
GO
-- ============================================================================
-- 场景3：死锁检测与预防
-- ============================================================================
PRINT '=== 3. 死锁检测与预防 ===';
GO
-- 3.1 查看当前死锁监控状态
PRINT '--- 3.1 检查死锁监控 ---';
GO
SELECT
name,
value,
value_in_use
FROM sys.configurations
WHERE name IN ('deadlock priority', 'deadlock monitoring');
GO
-- 3.2 设置死锁优先级
PRINT '--- 3.2 设置死锁优先级 ---';
GO
-- 低优先级：当前会话容易被选为死锁牺牲品
SET DEADLOCK_PRIORITY LOW;
BEGIN TRAN;
-- 模拟死锁场景A：先锁资源A，再尝试锁资源B
UPDATE deadlock_demo SET data_col = 'A modified' WHERE id = 1;
-- WAITFOR DELAY '00:00:05'; -- 模拟业务处理
UPDATE deadlock_demo SET data_col = 'B modified' WHERE id = 2;
COMMIT TRAN;
SET DEADLOCK_PRIORITY NORMAL;
GO
-- 3.3 死锁预防最佳实践
PRINT '--- 3.3 死锁预防最佳实践 ---';
GO
-- 实践1：按固定顺序访问表/行
-- 实践2：缩短事务持续时间
-- 实践3：使用适当的隔离级别（SNAPSHOT避免锁竞争）
-- 实践4：使用 WITH (ROWLOCK) 减少锁粒度
-- 实践5：避免在事务中等待用户输入
-- 3.4 捕获死锁事件（使用扩展事件）
PRINT '--- 3.4 捕获死锁事件 ---';
GO
-- 方法1：使用 trace flag 1222（输出死锁图到错误日志）
-- DBCC TRACEON(1222, -1);
-- 方法2：使用扩展事件（推荐）
/*
CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.deadlock_graph(
ACTION(sqlserver.client_app_name, sqlserver.database_name, sqlserver.session_id)
)
ADD TARGET package0.event_file(SET filename=N'C:\XEvents\DeadlockCapture.xel');
GO
START EVENT SESSION DeadlockCapture ON SERVER;
*/
PRINT '死锁捕获配置完成（扩展事件示例已注释）';
GO
-- 3.5 模拟死锁（仅供学习，生产环境勿用）
PRINT '--- 3.5 死锁模拟（两个会话交叉锁）---';
GO
-- 会话A执行：
/*
BEGIN TRAN;
UPDATE deadlock_demo SET data_col = 'A1' WHERE id = 1;
WAITFOR DELAY '00:00:02';
UPDATE deadlock_demo SET data_col = 'A2' WHERE id = 2;
COMMIT TRAN;
*/
-- 会话B同时执行：
/*
BEGIN TRAN;
UPDATE deadlock_demo SET data_col = 'B2' WHERE id = 2;
WAITFOR DELAY '00:00:02';
UPDATE deadlock_demo SET data_col = 'B1' WHERE id = 1;
COMMIT TRAN;
*/
-- 其中一个会话会被选为死锁牺牲品并收到错误
-- 错误号：1205，SQLSTATE: 40001
-- 处理死锁的TRY/CATCH
PRINT '--- 3.6 死锁错误处理 ---';
GO
BEGIN TRY
BEGIN TRAN;
UPDATE deadlock_demo SET data_col = 'test' WHERE id = 1;
WAITFOR DELAY '00:00:05'; -- 模拟长时间操作
UPDATE deadlock_demo SET data_col = 'test' WHERE id = 2;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
DECLARE @error_msg NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @error_num INT = ERROR_NUMBER();
IF @error_num = 1205 -- 死锁错误
BEGIN
PRINT '检测到死锁，可在此处实现重试逻辑';
PRINT '错误: ' + @error_msg;
END
ELSE
BEGIN
PRINT '其他错误: ' + @error_msg;
END
END CATCH
GO
-- ============================================================================
-- 场景4：行版本控制（Row Versioning）
-- ============================================================================
PRINT '=== 4. 行版本控制演示 ===';
GO
-- 4.1 查看行版本存储
PRINT '--- 4.1 行版本存储信息 ---';
GO
SELECT
DB_NAME() AS database_name,
snapshot_isolation_state,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'JewelryDemo';
GO
-- 4.2 快照隔离下的并发读取
PRINT '--- 4.2 快照隔离并发读取 ---';
GO
-- 会话A：修改数据但不提交
-- BEGIN TRAN;
-- UPDATE products SET unit_price = 99999 WHERE product_id = 1;
-- 会话B：快照隔离下不受影响
-- SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- SELECT unit_price FROM products WHERE product_id = 1;
-- 仍读到旧值（快照一致性）
-- 4.3 读取行版本历史（用于审计）
PRINT '--- 4.3 使用行版本进行审计 ---';
GO
-- 为库存表添加系统版本控制时序表
PRINT '--- 4.4 创建时序表（Temporal Table）---';
GO
-- 4.4 时序表（System-Versioned Temporal Table）
-- 需要先创建历史表
CREATE TABLE inventory_history (
inventory_id INT NOT NULL,
product_id INT NOT NULL,
warehouse VARCHAR(30) NOT NULL,
quantity INT NOT NULL,
reserved_qty INT NOT NULL,
version VARBINARY(8) NOT NULL,
SysStartTime DATETIME2 NOT NULL,
SysEndTime DATETIME2 NOT NULL
);
GO
-- 为 inventory 表启用系统版本控制
ALTER TABLE inventory
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT SYSUTCDATETIME(),
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999');
GO
ALTER TABLE inventory
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.inventory_history));
GO
-- 4.5 查询时序表历史数据
PRINT '--- 4.5 查询库存变更历史 ---';
GO
-- 查询某时刻的数据状态
DECLARE @as_of_time DATETIME2 = '2024-06-01 00:00:00';
SELECT
i.product_id,
i.quantity AS current_quantity,
h.quantity AS historical_quantity,
h.SysStartTime AS changed_at
FROM inventory i
LEFT JOIN inventory_history h ON i.inventory_id = h.inventory_id
WHERE i.product_id = 1;
-- 查询指定时间范围内的所有变更
SELECT
product_id,
quantity,
reserved_qty,
SysStartTime,
SysEndTime
FROM inventory_history
WHERE product_id = 1
AND SysEndTime > '2024-01-01'
ORDER BY SysStartTime DESC;
GO
-- 4.6 禁用时序表（如需修改表结构）
/*
ALTER TABLE inventory SET (SYSTEM_VERSIONING = OFF);
-- 修改表结构...
ALTER TABLE inventory SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.inventory_history));
*/
GO
PRINT 'sqlserver2019_concurrency.sql 执行完成';
GO
-- ============================================================================
-- 文件：sqlserver2019_saga.sql
-- 说明：Saga模式实现（订单创建/取消/通用编排器）、补偿事务、最终一致性
-- 执行顺序：第 5 步（需先执行 schema + data）
-- ============================================================================
USE JewelryDemo;
GO
-- ============================================================================
-- 场景1：订单创建 Saga（多步骤分布式事务）
-- ============================================================================
PRINT '=== 1. 订单创建 Saga ===';
GO
-- 1.1 存储过程：创建订单Saga（含补偿逻辑）
PRINT '--- 1.1 存储过程：CreateOrderSaga ---';
GO
CREATE OR ALTER PROCEDURE usp_CreateOrderSaga
@customer_id INT,
@product_id INT,
@quantity INT,
@order_id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @saga_id UNIQUEIDENTIFIER = NEWID();
DECLARE @product_name VARCHAR(100);
DECLARE @unit_price DECIMAL(12,2);
DECLARE @total_amount DECIMAL(14,2);
DECLARE @error_msg VARCHAR(500);
-- 记录Saga开始
INSERT INTO saga_log (saga_id, step_name, order_id, customer_id, product_id, quantity, status, created_at)
VALUES (@saga_id, 'ORDER_CREATE_START', NULL, @customer_id, @product_id, @quantity, 'RUNNING', SYSUTCDATETIME());
BEGIN TRY
BEGIN TRAN;
-- Step 1: 验证客户
DECLARE @cust_name VARCHAR(50);
SELECT @cust_name = customer_name FROM customers WHERE customer_id = @customer_id;
IF @@ROWCOUNT = 0
BEGIN
SET @error_msg = '客户不存在: ' + CAST(@customer_id AS VARCHAR);
GOTO ErrorHandler;
END
INSERT INTO saga_log (saga_id, step_name, customer_id, status, error_message)
VALUES (@saga_id, 'CUSTOMER_VALIDATED', @customer_id, 'COMPLETED', NULL);
-- Step 2: 验证产品并获取价格
SELECT @product_name = product_name, @unit_price = unit_price
FROM products WHERE product_id = @product_id AND is_active = 1;
IF @@ROWCOUNT = 0
BEGIN
SET @error_msg = '产品不存在或已下架: ' + CAST(@product_id AS VARCHAR);
GOTO ErrorHandler;
END
SET @total_amount = @unit_price * @quantity;
INSERT INTO saga_log (saga_id, step_name, product_id, status, error_message)
VALUES (@saga_id, 'PRODUCT_VALIDATED', @product_id, 'COMPLETED', NULL);
-- Step 3: 检查库存
DECLARE @stock INT;
SELECT @stock = quantity FROM inventory WHERE product_id = @product_id;
IF @stock < @quantity
BEGIN
SET @error_msg = '库存不足: 需要' + CAST(@quantity AS VARCHAR) + ', 可用' + CAST(@stock AS VARCHAR);
GOTO ErrorHandler;
END
INSERT INTO saga_log (saga_id, step_name, status, error_message)
VALUES (@saga_id, 'STOCK_CHECKED', NULL, 'COMPLETED', NULL);
-- Step 4: 创建订单
INSERT INTO orders (customer_id, order_date, sales_channel, total_amount, status)
VALUES (@customer_id, SYSUTCDATETIME(), '微信小程序', @total_amount, '待付款');
SET @order_id = SCOPE_IDENTITY();
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'ORDER_CREATED', @order_id, 'COMPLETED', NULL);
-- Step 5: 插入订单明细
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount)
VALUES (@order_id, @product_id, @quantity, @unit_price, 1.00);
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'ORDER_ITEM_CREATED', @order_id, 'COMPLETED', NULL);
-- Step 6: 扣减库存
UPDATE inventory SET quantity = quantity - @quantity
WHERE product_id = @product_id AND quantity >= @quantity;
IF @@ROWCOUNT = 0
BEGIN
SET @error_msg = '库存扣减失败（并发冲突）';
GOTO ErrorHandler;
END
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'STOCK_DEDUCTED', @order_id, 'COMPLETED', NULL);
-- Step 7: 更新客户累计消费
UPDATE customers SET total_spent = total_spent + @total_amount
WHERE customer_id = @customer_id;
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'CUSTOMER_UPDATED', @order_id, 'COMPLETED', NULL);
-- 所有步骤成功
UPDATE saga_log SET status = 'COMPLETED', completed_at = SYSUTCDATETIME()
WHERE saga_id = @saga_id AND status = 'RUNNING';
COMMIT TRAN;
PRINT 'Saga完成：订单 ' + CAST(@order_id AS VARCHAR) + ' 创建成功，总金额 ' + CAST(@total_amount AS VARCHAR);
RETURN 0;
ErrorHandler:
-- Saga失败，执行补偿操作
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
UPDATE saga_log SET status = 'FAILED', error_message = @error_msg, completed_at = SYSUTCDATETIME()
WHERE saga_id = @saga_id AND status = 'RUNNING';
EXEC usp_CompensateOrder @saga_id, @order_id, @product_id, @quantity;
PRINT 'Saga失败并已补偿：' + @error_msg;
RETURN 1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
UPDATE saga_log SET status = 'FAILED', error_message = ERROR_MESSAGE(), completed_at = SYSUTCDATETIME()
WHERE saga_id = @saga_id AND status = 'RUNNING';
EXEC usp_CompensateOrder @saga_id, @order_id, @product_id, @quantity;
PRINT 'Saga异常并已补偿：' + ERROR_MESSAGE();
RETURN 2;
END CATCH
END
GO
-- 1.2 补偿存储过程（逆序撤销）
PRINT '--- 1.2 存储过程：CompensateOrder（补偿）---';
GO
CREATE OR ALTER PROCEDURE usp_CompensateOrder
@saga_id UNIQUEIDENTIFIER,
@order_id INT NULL,
@product_id INT NULL,
@quantity INT NULL
AS
BEGIN
SET NOCOUNT ON;
-- 补偿步骤（逆序执行）：
-- 5. 恢复客户累计消费
IF @order_id IS NOT NULL
BEGIN
DECLARE @order_amount DECIMAL(14,2);
SELECT @order_amount = total_amount FROM orders WHERE order_id = @order_id;
IF @order_amount IS NOT NULL
BEGIN
UPDATE customers SET total_spent = total_spent - @order_amount
WHERE customer_id = (SELECT customer_id FROM orders WHERE order_id = @order_id);
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, 'COMPENSATE_CUSTOMER_REVERT', 'COMPLETED');
END
-- 4. 删除订单明细
DELETE FROM order_items WHERE order_id = @order_id;
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, 'COMPENSATE_ORDER_ITEMS_DELETED', 'COMPLETED');
-- 3. 删除订单
DELETE FROM orders WHERE order_id = @order_id;
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, 'COMPENSATE_ORDER_DELETED', 'COMPLETED');
END
-- 2. 恢复库存
IF @product_id IS NOT NULL AND @quantity IS NOT NULL
BEGIN
UPDATE inventory SET quantity = quantity + @quantity
WHERE product_id = @product_id;
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, 'COMPENSATE_STOCK_REVERT', 'COMPLETED');
END
-- 标记补偿完成
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, 'COMPENSATION_COMPLETED', 'COMPLETED');
END
GO
-- 1.3 执行订单创建Saga
PRINT '--- 1.3 执行订单创建Saga ---';
GO
DECLARE @new_order_id INT;
EXEC usp_CreateOrderSaga
@customer_id = 1,
@product_id = 1,
@quantity = 2,
@order_id = @new_order_id OUTPUT;
IF @new_order_id IS NOT NULL
PRINT '新订单ID: ' + CAST(@new_order_id AS VARCHAR);
GO
-- ============================================================================
-- 场景2：订单取消 Saga
-- ============================================================================
PRINT '=== 2. 订单取消 Saga ===';
GO
CREATE OR ALTER PROCEDURE usp_CancelOrderSaga
@order_id INT,
@reason VARCHAR(200) = '用户取消'
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @saga_id UNIQUEIDENTIFIER = NEWID();
DECLARE @product_id INT;
DECLARE @quantity INT;
DECLARE @customer_id INT;
-- 记录Saga开始
INSERT INTO saga_log (saga_id, step_name, order_id, status, error_message)
VALUES (@saga_id, 'ORDER_CANCEL_START', @order_id, 'RUNNING', @reason);
BEGIN TRY
BEGIN TRAN;
-- Step 1: 验证订单存在且状态可取消
SELECT @product_id = oi.product_id, @quantity = oi.quantity, @customer_id = o.customer_id
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = @order_id AND o.status IN ('待付款', '已付款');
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('订单不存在或不可取消', 16, 1);
END
-- Step 2: 更新订单状态为已取消
UPDATE orders SET status = '已取消' WHERE order_id = @order_id;
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'ORDER_CANCELLED', @order_id, 'COMPLETED', NULL);
-- Step 3: 恢复库存
UPDATE inventory SET quantity = quantity + @quantity
WHERE product_id = @product_id;
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'STOCK_RESTORED', @order_id, 'COMPLETED', NULL);
-- Step 4: 恢复客户累计消费
DECLARE @order_total DECIMAL(14,2);
SELECT @order_total = total_amount FROM orders WHERE order_id = @order_id;
UPDATE customers SET total_spent = total_spent - @order_total
WHERE customer_id = @customer_id;
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'CUSTOMER_REVERTED', @order_id, 'COMPLETED', NULL);
-- Step 5: 删除订单明细
DELETE FROM order_items WHERE order_id = @order_id;
INSERT INTO saga_log (saga_id, step_name, order_id, status)
VALUES (@saga_id, 'ORDER_ITEMS_DELETED', @order_id, 'COMPLETED', NULL);
-- 所有步骤成功
UPDATE saga_log SET status = 'COMPLETED', completed_at = SYSUTCDATETIME()
WHERE saga_id = @saga_id AND status = 'RUNNING';
COMMIT TRAN;
PRINT '订单取消Saga完成：订单 ' + CAST(@order_id AS VARCHAR) + ' 已取消';
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
UPDATE saga_log SET status = 'FAILED', error_message = ERROR_MESSAGE(), completed_at = SYSUTCDATETIME()
WHERE saga_id = @saga_id AND status = 'RUNNING';
PRINT '订单取消Saga失败：' + ERROR_MESSAGE();
RETURN 1;
END CATCH
END
GO
-- 执行订单取消Saga演示
PRINT '--- 2.2 执行订单取消Saga ---';
GO
EXEC usp_CancelOrderSaga @order_id = 1, @reason = '测试取消';
GO
-- ============================================================================
-- 场景3：通用Saga编排器
-- ============================================================================
PRINT '=== 3. 通用Saga编排器 ===';
GO
-- 3.1 Saga步骤定义表
PRINT '--- 3.1 Saga步骤定义 ---';
GO
CREATE TABLE saga_steps (
step_id INT IDENTITY(1,1) PRIMARY KEY,
saga_type VARCHAR(50) NOT NULL,
step_name VARCHAR(100) NOT NULL,
step_order INT NOT NULL,
procedure_name VARCHAR(200) NOT NULL,
compensate_procedure VARCHAR(200) NULL,
is_enabled BIT DEFAULT 1,
CONSTRAINT UQ_saga_type_step_order UNIQUE (saga_type, step_order)
);
GO
-- 插入Saga步骤定义
INSERT INTO saga_steps (saga_type, step_name, step_order, procedure_name, compensate_procedure) VALUES
('CreateOrder', '验证客户', 1, 'usp_SagaValidateCustomer', NULL),
('CreateOrder', '验证产品', 2, 'usp_SagaValidateProduct', NULL),
('CreateOrder', '检查库存', 3, 'usp_SagaCheckStock', NULL),
('CreateOrder', '创建订单', 4, 'usp_SagaCreateOrder', NULL),
('CreateOrder', '扣减库存', 5, 'usp_SagaDeductStock', 'usp_SagaRestoreStock'),
('CreateOrder', '更新客户', 6, 'usp_SagaUpdateCustomer', 'usp_SagaRevertCustomer'),
('CancelOrder', '验证订单', 1, 'usp_SagaValidateOrder', NULL),
('CancelOrder', '取消订单', 2, 'usp_SagaCancelOrder', NULL),
('CancelOrder', '恢复库存', 3, 'usp_SagaRestoreStock', NULL),
('CancelOrder', '恢复消费', 4, 'usp_SagaRevertCustomer', NULL);
GO
-- 3.2 通用Saga执行引擎
PRINT '--- 3.2 通用Saga执行引擎 ---';
GO
CREATE OR ALTER PROCEDURE usp_SagaOrchestrator
@saga_type VARCHAR(50),
@parameters NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @saga_id UNIQUEIDENTIFIER = NEWID();
DECLARE @step_order INT = 1;
DECLARE @step_name VARCHAR(100);
DECLARE @proc_name VARCHAR(200);
DECLARE @error_msg VARCHAR(500);
DECLARE @success BIT = 1;
-- 记录Saga开始
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, @saga_type + '_START', 'RUNNING');
DECLARE @failed_steps TABLE (step_name VARCHAR(100), compensate_proc VARCHAR(200));
BEGIN TRY
WHILE @success = 1
BEGIN
-- 获取下一步骤
SELECT TOP 1 @step_name = step_name, @proc_name = procedure_name
FROM saga_steps
WHERE saga_type = @saga_type AND step_order = @step_order AND is_enabled = 1
ORDER BY step_order;
IF @@ROWCOUNT = 0 BREAK;
-- 执行步骤
BEGIN TRY
DECLARE @sql NVARCHAR(MAX) = 'EXEC ' + @proc_name + ' @parameters = N''' + REPLACE(@parameters, '''', '''''') + '''';
EXEC sp_executesql @sql, N'@parameters NVARCHAR(MAX)', @parameters = @parameters;
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, @step_name, 'COMPLETED');
END TRY
BEGIN CATCH
SET @success = 0;
SET @error_msg = ERROR_MESSAGE();
INSERT INTO saga_log (saga_id, step_name, status, error_message)
VALUES (@saga_id, @step_name, 'FAILED', @error_msg);
DECLARE @comp_proc VARCHAR(200);
SELECT @comp_proc = compensate_procedure
FROM saga_steps
WHERE saga_type = @saga_type AND step_name = @step_name;
IF @comp_proc IS NOT NULL
INSERT INTO @failed_steps (step_name, compensate_proc) VALUES (@step_name, @comp_proc);
UPDATE saga_log SET status = 'FAILED', error_message = @error_msg
WHERE saga_id = @saga_id AND step_name = @saga_type + '_START';
END CATCH
SET @step_order = @step_order + 1;
END
-- 所有步骤成功
IF @success = 1
BEGIN
UPDATE saga_log SET status = 'COMPLETED', completed_at = SYSUTCDATETIME()
WHERE saga_id = @saga_id AND status IN ('RUNNING', 'FAILED');
PRINT 'Saga执行成功：' + @saga_type;
END
ELSE
BEGIN
-- 执行补偿（逆序）
DECLARE @comp_steps TABLE (id INT IDENTITY(1,1), compensate_proc VARCHAR(200));
INSERT INTO @comp_steps (compensate_proc)
SELECT compensate_proc FROM @failed_steps;
DECLARE @comp_id INT = (SELECT MAX(id) FROM @comp_steps);
WHILE @comp_id > 0
BEGIN
DECLARE @proc VARCHAR(200);
SELECT @proc = compensate_proc FROM @comp_steps WHERE id = @comp_id;
IF @proc IS NOT NULL AND @proc <> 'NULL'
BEGIN
BEGIN TRY
EXEC @proc @saga_id = @saga_id;
INSERT INTO saga_log (saga_id, step_name, status)
VALUES (@saga_id, 'COMP_' + @proc, 'COMPLETED');
END TRY
BEGIN CATCH
INSERT INTO saga_log (saga_id, step_name, status, error_message)
VALUES (@saga_id, 'COMP_' + @proc + '_FAILED', 'FAILED', ERROR_MESSAGE());
END CATCH
END
SET @comp_id = @comp_id - 1;
END
PRINT 'Saga失败并已执行补偿：' + @saga_type;
END
END TRY
BEGIN CATCH
PRINT 'Saga编排器异常：' + ERROR_MESSAGE();
END CATCH
END
GO
-- ============================================================================
-- 场景4：最终一致性监控
-- ============================================================================
PRINT '=== 4. 最终一致性监控 ===';
GO
-- 4.1 查看Saga执行状态
PRINT '--- 4.1 Saga执行状态监控 ---';
GO
SELECT
saga_id, step_name, order_id, status, error_message,
created_at, completed_at,
DATEDIFF(ms, created_at, ISNULL(completed_at, SYSUTCDATETIME())) AS duration_ms
FROM saga_log
ORDER BY created_at DESC;
GO
-- 4.2 查看失败的Saga
PRINT '--- 4.2 失败Saga监控 ---';
GO
SELECT DISTINCT
s.saga_id, s.step_name AS last_step, s.error_message, s.created_at, s.completed_at
FROM saga_log s
WHERE s.status = 'FAILED' AND s.step_name NOT LIKE 'COMP_%'
ORDER BY s.created_at DESC;
GO
PRINT 'sqlserver2019_saga.sql 执行完成';
GO
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号