一、总体原则
-
清晰分工
复杂业务拆分为「主存储过程 + 子存储过程」模式，主过程控制流程，子过程各司其职。 -
职责单一
每个存储过程只处理单一业务逻辑，避免流程交叉、职责混杂。 -
可测试性与可追踪性
必须支持独立调试和链路跟踪（Trace ID传递）。 -
性能优先
-
保证索引友好
-
尽量批量处理，减少循环
-
合理控制事务范围
-
避免锁表、长事务
-
-
安全性
-
防止SQL注入
-
避免非必要动态SQL
-
权限最小化设计
-
二、存储过程分类与命名规范
| 类型 | 命名规则 | 示例 |
|---|---|---|
| 主流程存储过程 | sp_<模块名>_<功能名>_main | sp_order_create_main |
| 子流程存储过程 | sp_<模块名>_<功能名>sub<子功能名> | sp_order_create_sub_discount |
| 功能性函数 | fn_<功能描述> | fn_calc_total_price |
| 触发器 | trg_<表名>_<动作> | trg_user_update |
| 视图 | view_<模块名>_<用途描述> | view_order_summary |
| 临时表 | tmp_<表名>_<用途描述> | tmp_order_processing |
| 日志表 | log_<模块名>_<用途描述> | log_proc_error |
| 错误码表 | error_code_<模块名> | error_code_system |
命名规范统一要求
-
全部小写，单词间使用下划线
_分隔。 -
不使用驼峰（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. 事务管理
| 层次 | 控制事务 | 记录日志 | 返回状态码 | Trace ID传递 |
|---|---|---|---|---|
| 主过程 Main | ✅ | ✅ | ✅ | ✅ |
| 子事务 SubTx（需要局部事务） | ✅（局部控制） | ✅ | ✅ | ✅ |
| 功能操作 Func（计算/查询） | ❌ | 可选 | 可选 | ✅ |
-
主过程负责全局事务（开启、提交、回滚）。
-
子过程如需局部事务，需在子过程内自处理，且必须回滚或明确提交。
-
出现异常时，不得自动回滚主事务，只反馈状态。
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
八、常用参数与变量约定表
| 名称 | 含义 |
|---|---|
| @trace_id | 链路追踪ID |
| @code | 状态码 |
| @msg | 状态信息 |
| @start_time / @end_time | 记录处理时间点 |
| @elapsed_ms | 总耗时（毫秒） |
| @error_code | 异常时的错误码 |
| @error_msg | 异常时的错误描述 |
理解了。
在您提供的基础上，我将继续用正式、专业的语气，完善《SQL Server 存储过程开发规范》中缺少的锁控制部分，并保持与原规范一致的风格。
以下是新增的锁控制规范章节（可直接合并到您的文档中）：
九、锁控制规范
在存储过程开发中，合理控制锁粒度与锁时长至关重要，直接关系到系统并发性与稳定性。必须遵循以下锁控制规范：
9.1 总体原则
-
锁粒度最小化：尽量锁定必要的数据行或数据页，避免全表锁。
-
锁时间最短化：尽可能缩短事务范围，减少锁持有时间。
-
避免隐式锁升级：通过合理索引与分页，避免系统将行锁自动升级为表锁。
-
必要时显式加锁：遇到高并发敏感区域，需明确指定锁策略，防止死锁和脏读。
9.2 常用锁控制方式
| 方法 | 说明 | 示例 |
|---|---|---|
| WITH (ROWLOCK) | 强制使用行锁，减少锁冲突 | SELECT * FROM table WITH (ROWLOCK) WHERE id = @id |
| WITH (UPDLOCK) | 在读取时加意向更新锁，避免并发更新冲突 | SELECT * FROM table WITH (UPDLOCK) WHERE id = @id |
| WITH (XLOCK) | 独占锁，读写期间禁止其他并发访问（仅限极端必要情况） | SELECT * FROM table WITH (XLOCK) WHERE id = @id |
| WITH (READPAST) | 跳过已被锁定的行，适合队列式读取 | SELECT TOP 1 * FROM table WITH (READPAST) WHERE status = 'pending' |
| SET TRANSACTION ISOLATION LEVEL | 控制事务隔离级别，平衡一致性与并发性 | SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
9.3 事务与锁的结合规范
-
事务范围要小，锁范围要窄：只在需要保持一致性的数据处理区段内部开启事务。
-
避免长事务：禁止在事务内进行复杂计算、长时间等待或调用外部服务。
-
必要时分段处理：对于批量更新、删除，应分批操作，每批控制在一定数量（如1000行以内）。
-
显式捕获死锁：使用 TRY...CATCH 块，在死锁出现时正确回滚并记录日志。
示例：
BEGIN TRYBEGIN TRANSACTION;-- 加锁读取SELECT * FROM order_detail WITH (UPDLOCK, ROWLOCK) WHERE order_id = @order_id;-- 更新逻辑UPDATE order_detailSET status = 'processed'WHERE order_id = @order_id;COMMIT TRANSACTION;
END TRY
BEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK 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 TRYBEGIN TRANSACTION;-- 业务操作UPDATE product_stock WITH (ROWLOCK, UPDLOCK)SET stock_qty = stock_qty - @qtyWHERE product_id = @product_id;COMMIT TRANSACTION;
END TRY
BEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;DECLARE @error_code INT = ERROR_NUMBER();DECLARE @error_msg NVARCHAR(4000) = ERROR_MESSAGE();-- 针对死锁错误，单独记录IF @error_code = 1205BEGINEXEC sp_log_deadlock @trace_id = @trace_id, @error_message = @error_msg;ENDELSEBEGINEXEC sp_log_error @trace_id = @trace_id, @error_code = @error_code, @error_msg = @error_msg;END
END CATCH
10.2 死锁日志结构建议
为后续问题定位与优化提供依据，死锁日志表推荐结构如下：
| 字段 | 类型 | 说明 |
|---|---|---|
| id | BIGINT IDENTITY | 主键，自增长 |
| trace_id | NVARCHAR(100) | 调用链追踪ID |
| occur_time | DATETIME | 死锁发生时间 |
| proc_name | NVARCHAR(200) | 存储过程名称 |
| error_message | NVARCHAR(MAX) | 错误详细信息 |
| sql_text | NVARCHAR(MAX) | 当前执行SQL（可选） |
| input_params | NVARCHAR(MAX) | 输入参数（可选） |
关键设计要点：
-
必须记录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)
BEGINBEGIN TRANSACTION;UPDATE TOP (@batch_size) order_detailSET 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
BEGINSET NOCOUNT ON;DECLARE @affected_rows INT = 1;WHILE (@affected_rows > 0)BEGINBEGIN TRANSACTION;UPDATE TOP (@batch_size) order_detailSET status = 'completed'WHERE status = 'pending';SET @affected_rows = @@ROWCOUNT;COMMIT TRANSACTION;END
END
小结
-
死锁处理，核心在于统一异常捕获、分类日志记录、可选重试保护。
-
大规模更新，核心在于控制事务体积、快速提交、分批处理。
这样既能保障数据一致性，又能最大化提升高并发场景下的吞吐量与稳定性。