SQL Server 存储过程开发规范

一、总体原则

• 清晰分工
  复杂业务拆分为「主存储过程 + 子存储过程」模式，主过程控制流程，子过程各司其职。

• 职责单一
  每个存储过程只处理单一业务逻辑，避免流程交叉、职责混杂。

• 可测试性与可追踪性
  必须支持独立调试和链路跟踪（Trace ID传递）。

• 性能优先

  • 保证索引友好

  • 尽量批量处理，减少循环

  • 合理控制事务范围

  • 避免锁表、长事务

• 安全性

  • 防止SQL注入

  • 避免非必要动态SQL

  • 权限最小化设计

二、存储过程分类与命名规范

命名规范统一要求

• 全部小写，单词间使用下划线 _ 分隔。

• 不使用驼峰（camelCase）或帕斯卡（PascalCase）。

• 禁止中文、拼音。

三、存储过程结构标准

1. 头部注释（必需）

/*
 * 名称: sp_order_create_main
 * 类型: 主流程存储过程
 * 作者: 张三 (zhangsan@example.com)
 * 创建时间: 2025-04-28
 * 版本: 1.0.0
 * 说明: 创建订单并初始化状态
 * 更新记录:
 *  - 2025-05-01 张三: 增加库存扣减子过程调用
 */

2. 输入输出参数规范

• 所有参数统一小写+蛇形命名。

• 输入参数必须标明数据类型、必要时标注默认值。

• 出参必须包括：

  • @code（状态码，0表示成功，非0为失败）

  • @msg（错误或成功信息）

  • @trace_id（链路追踪标识）

示例：

CREATE PROCEDURE sp_order_create_main
    @user_id BIGINT,
    @order_amount DECIMAL(18,2),
    @trace_id UNIQUEIDENTIFIER,
    @code INT OUTPUT,
    @msg NVARCHAR(500) OUTPUT
AS
BEGIN
    -- 过程逻辑
END

四、事务与异常控制规范

1. 事务管理

• 主过程负责全局事务（开启、提交、回滚）。

• 子过程如需局部事务，需在子过程内自处理，且必须回滚或明确提交。

• 出现异常时，不得自动回滚主事务，只反馈状态。

2. 异常与日志记录

• 主过程统一捕获异常，记录到日志表。

• 子过程返回错误码与消息，不得直接打印日志。

标准日志表设计：

CREATE TABLE dbo.log_proc_error (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    trace_id UNIQUEIDENTIFIER NOT NULL,
    proc_name NVARCHAR(200) NOT NULL,
    error_code INT NOT NULL,
    error_msg NVARCHAR(2000) NOT NULL,
    stack_trace NVARCHAR(MAX) NULL,
    input_params NVARCHAR(MAX) NULL,
    extra_info NVARCHAR(MAX) NULL,
    create_time DATETIME2(3) DEFAULT SYSDATETIME()
);

五、编码规范

• 不允许使用 SELECT *，必须明确列字段。

• 所有SQL语句应使用合理缩进和必要注释。

• 重要逻辑必须分段注释，例如：

-- Step 1: 校验用户有效性
-- Step 2: 创建订单基础信息
-- Step 3: 调用库存扣减子过程

• 临时表统一加前缀，例如 tmp_，中间备份表加 bak_ 前缀。

• 所有数据库表字段、存储过程、视图必须添加注释（COMMENT EXTENDED属性或文档备注）。

六、Trace ID 规范

• 所有存储过程必须传递 @trace_id。

• 每一级过程调用时必须携带 @trace_id，用于链路追踪和异常排查。

七、版本管理与变更记录

• 存储过程代码统一纳入Git版本管理。

• 每次提交时必须填写：

  • 变更说明

  • 时间

  • 责任人

示例 Commit Message：

feat(sp_order_create_main): 支持优惠券扣减流程，by 张三 2025-05-01

八、常用参数与变量约定表

理解了。
在您提供的基础上，我将继续用正式、专业的语气，完善《SQL Server 存储过程开发规范》中缺少的锁控制部分，并保持与原规范一致的风格。
以下是新增的锁控制规范章节（可直接合并到您的文档中）：

九、锁控制规范

在存储过程开发中，合理控制锁粒度与锁时长至关重要，直接关系到系统并发性与稳定性。必须遵循以下锁控制规范：

9.1 总体原则

• 锁粒度最小化：尽量锁定必要的数据行或数据页，避免全表锁。

• 锁时间最短化：尽可能缩短事务范围，减少锁持有时间。

• 避免隐式锁升级：通过合理索引与分页，避免系统将行锁自动升级为表锁。

• 必要时显式加锁：遇到高并发敏感区域，需明确指定锁策略，防止死锁和脏读。

9.2 常用锁控制方式

9.3 事务与锁的结合规范

• 事务范围要小，锁范围要窄：只在需要保持一致性的数据处理区段内部开启事务。

• 避免长事务：禁止在事务内进行复杂计算、长时间等待或调用外部服务。

• 必要时分段处理：对于批量更新、删除，应分批操作，每批控制在一定数量（如1000行以内）。

• 显式捕获死锁：使用 TRY...CATCH 块，在死锁出现时正确回滚并记录日志。

示例：

BEGIN TRY
    BEGIN TRANSACTION;

    -- 加锁读取
    SELECT * FROM order_detail WITH (UPDLOCK, ROWLOCK) WHERE order_id = @order_id;

    -- 更新逻辑
    UPDATE order_detail
    SET status = 'processed'
    WHERE order_id = @order_id;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- 错误日志处理
    EXEC sp_log_error @trace_id = @trace_id, @error_code = ERROR_NUMBER(), @error_msg = ERROR_MESSAGE();
END CATCH

9.4 特别说明

• 批量操作优先分页：例如大表更新，应采用 TOP(n) 分批更新策略，防止一次性锁表。

• 避免锁等待链：严禁在持锁状态下调用其他子存储过程，防止锁顺序反转导致死锁。

• 读一致性策略：只读操作，如非强一致性要求，应使用 READ COMMITTED SNAPSHOT 隔离级别，以减少锁竞争。

总结
锁控制是高并发数据库系统中不可忽视的一环。规范、审慎地使用锁，才能在保证正确性的同时，最大化系统吞吐量，避免死锁、锁表等严重问题。
在高负载系统中，应专门设立锁审计监控，及时发现异常锁持有与锁等待情况，持续优化存储过程性能

十、死锁检测与日志结构建议

10.1 死锁检测

• 统一死锁处理机制：所有可能引发死锁的存储过程，必须使用 TRY...CATCH 块进行异常捕获。

• 错误码识别：SQL Server 死锁错误码为 1205，应在 CATCH 块中专门识别并记录。

• 失败重试机制（可选）：对于幂等操作，死锁后允许在短时间内自动重试1-3次，避免单点失败。

示例：

BEGIN TRY
    BEGIN TRANSACTION;

    -- 业务操作
    UPDATE product_stock WITH (ROWLOCK, UPDLOCK)
    SET stock_qty = stock_qty - @qty
    WHERE product_id = @product_id;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    DECLARE @error_code INT = ERROR_NUMBER();
    DECLARE @error_msg NVARCHAR(4000) = ERROR_MESSAGE();

    -- 针对死锁错误，单独记录
    IF @error_code = 1205
    BEGIN
        EXEC sp_log_deadlock @trace_id = @trace_id, @error_message = @error_msg;
    END
    ELSE
    BEGIN
        EXEC sp_log_error @trace_id = @trace_id, @error_code = @error_code, @error_msg = @error_msg;
    END
END CATCH

10.2 死锁日志结构建议

为后续问题定位与优化提供依据，死锁日志表推荐结构如下：

关键设计要点：

• 必须记录trace_id，以支持跨服务链路追踪。

• 出错SQL或输入参数可以选择性截取，避免日志表无限膨胀。

• 必须定期归档死锁日志，如按月分区或搬迁。

十一、大规模批量更新的最佳实践

在处理百万级以上数据时，禁止直接大批量更新，必须分批、分段控制，降低锁竞争与事务压力。

11.1 分批更新策略

• 每次操作限定**TOP(N)**条记录，如1000或5000条。

• 更新成功后提交，避免长事务锁表。

• 避免分页偏移（OFFSET），优先通过游标或ID范围递增处理。

示例：

DECLARE @batch_size INT = 1000;
DECLARE @affected_rows INT;

SET @affected_rows = 1;

WHILE (@affected_rows > 0)
BEGIN
    BEGIN TRANSACTION;

    UPDATE TOP (@batch_size) order_detail
    SET status = 'completed'
    WHERE status = 'pending';

    SET @affected_rows = @@ROWCOUNT;

    COMMIT TRANSACTION;
END

11.2 批处理注意事项

• 保持稳定的批量大小，防止批次太小浪费资源，批次太大导致锁表。

• 结合时间窗口更新，如夜间低峰期批量处理，避免高峰期阻塞业务。

• 可配参数化，批量数量、超时时间可通过存储过程参数传入，提升灵活性。

示例：批量更新支持动态传入 batch_size

CREATE PROCEDURE proc_batch_update_order
    @batch_size INT = 1000
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @affected_rows INT = 1;

    WHILE (@affected_rows > 0)
    BEGIN
        BEGIN TRANSACTION;

        UPDATE TOP (@batch_size) order_detail
        SET status = 'completed'
        WHERE status = 'pending';

        SET @affected_rows = @@ROWCOUNT;

        COMMIT TRANSACTION;
    END
END

小结

1. 死锁处理，核心在于统一异常捕获、分类日志记录、可选重试保护。

2. 大规模更新，核心在于控制事务体积、快速提交、分批处理。

这样既能保障数据一致性，又能最大化提升高并发场景下的吞吐量与稳定性。