Service Broker操作说明(异步执行)-表库云知库网

**ervice Broker操作说明*****

流程图：

根据您的需求，我为您设计了一套基于SQL Server Service Broker的企业级异步延迟执行方案。该方案能够安全、高效地处理存储在特定表中的存储过程调用任务，并满足延迟执行和高并发的要求。
以下是整个方案的架构图和核心组件说明：
flowchart TD
A[应用程序] -->|写入任务| B[tabDIYTable12402
任务表]
B --> C[InitiateDelayExecution
发起存储过程]
C -->|发送消息| D[DelayServiceQueue
服务队列]
D -->|激活| E[ProcessDelayQueue
处理存储过程]
E -->|延迟执行| F{等待指定秒数}
F --> G[执行目标存储过程]
G -->|更新回执| H[标记任务完成]
🔧 方案核心组件与流程
本方案的核心是利用Service Broker的队列机制实现异步处理，并通过数据库事务确保可靠性。

启用Service Broker并创建基础组件
首先，需要在您的数据库中启用Service Broker并创建必要的消息类型、合约和队列。
-- 启用Service Broker（请在执行前备份数据库，此操作需要独占连接）
ALTER DATABASE YourDatabase SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

-- 创建消息类型和合约
CREATE MESSAGE TYPE [//YourDomain/DelayRequest] VALIDATION = NONE;
CREATE CONTRACT [//YourDomain/DelayContract]
([//YourDomain/DelayRequest] SENT BY INITIATOR);

-- 创建队列，并配置激活存储过程
CREATE QUEUE DelayServiceQueue WITH STATUS = ON;
CREATE SERVICE [//YourDomain/DelayService]
ON QUEUE DelayServiceQueue ([//YourDomain/DelayContract]);

任务发起存储过程 (InitiateDelayExecution)
这个存储过程负责将一个新的延迟任务插入到业务表中，并向队列发送消息以触发异步处理。
CREATE PROCEDURE dbo.InitiateDelayExecution
@F108211 NVARCHAR(128), -- 存储过程名称
@F108218 INT, -- 延迟秒数
@F108212 NVARCHAR(MAX) = NULL, -- 以下为条件参数
@F108213 NVARCHAR(MAX) = NULL,
@F108214 NVARCHAR(MAX) = NULL,
@F108215 NVARCHAR(MAX) = NULL,
@F108216 NVARCHAR(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TaskID INT;

BEGIN TRY
BEGIN TRANSACTION;

-- 向任务表插入新记录，回执标记默认为0
INSERT INTO dbo.tabDIYTable12402 
(F108211, F108218, F108212, F108213, F108214, F108215, F108216, F108219)
VALUES 
(@F108211, @F108218, @F108212, @F108213, @F108214, @F108215, @F108216, 0);

SET @TaskID = SCOPE_IDENTITY();

-- 发送Service Broker消息，消息体包含任务ID
DECLARE @dialog_handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [//YourDomain/DelayService]
TO SERVICE '//YourDomain/DelayService'
ON CONTRACT [//YourDomain/DelayContract]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [//YourDomain/DelayRequest] (@TaskID);

COMMIT TRANSACTION;

END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- 应在此记录详细的错误日志
THROW;
END CATCH;
END;

消息处理存储过程 (ProcessDelayQueue)
这是方案的核心，它作为队列的激活存储过程，自动从队列接收消息，并执行延迟和处理逻辑。
CREATE PROCEDURE dbo.ProcessDelayQueue
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversation_handle UNIQUEIDENTIFIER;
DECLARE @message_body NVARCHAR(MAX);
DECLARE @message_type_name NVARCHAR(256);
DECLARE @TaskID INT;

WHILE (1 = 1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
```
    WAITFOR (
        RECEIVE TOP(1) 
            @conversation_handle = conversation_handle,
            @message_body = message_body,
            @message_type_name = message_type_name
        FROM dbo.DelayServiceQueue
    ), TIMEOUT 1000;

    IF @@ROWCOUNT = 0
    BEGIN
        COMMIT TRANSACTION;
        BREAK;
    END

    IF @message_type_name = '//YourDomain/DelayRequest'
    BEGIN
        SET @TaskID = CAST(@message_body AS INT);

        DECLARE @TargetSPName NVARCHAR(128), @DelaySeconds INT, @ExecFlag BIT;
        SELECT 
            @TargetSPName = F108211,
            @DelaySeconds = F108218,
            @ExecFlag = F108219
        FROM dbo.tabDIYTable12402 
        WHERE ID = @TaskID;

        IF @ExecFlag = 0
        BEGIN
            -- 使用安全的动态延迟时间构造方法
            DECLARE @DelayString NVARCHAR(10);
            -- 将秒数格式化为 HH:MM:SS 格式中的秒部分，确保两位数显示
            SET @DelayString = '00:00:' + RIGHT('00' + CAST(@DelaySeconds AS VARCHAR(8)), 8);

            -- 执行延迟等待
            WAITFOR DELAY @DelayString;

            DECLARE @SQL NVARCHAR(MAX);
            SET @SQL = 'EXEC ' + QUOTENAME(@TargetSPName);
            EXEC sp_executesql @SQL;

            UPDATE dbo.tabDIYTable12402 SET F108219 = 1 WHERE ID = @TaskID;
        END

        END CONVERSATION @conversation_handle;
    END

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    -- 记录错误日志
END CATCH;
```
END
END;
🚀 高并发与性能优化
在企业级高并发场景下，以下优化措施至关重要：
• 配置队列激活多实例：通过设置MAX_QUEUE_READERS > 1，允许多个处理实例并发消费队列消息，提高吞吐量。
• ALTER QUEUE DelayServiceQueue
• WITH ACTIVATION (
• STATUS = ON,
• PROCEDURE_NAME = dbo.ProcessDelayQueue,
• MAX_QUEUE_READERS = 5, -- 根据系统负载调整并发数
• EXECUTE AS SELF
);
• 保持事务简短：确保每个消息的处理事务尽可能短小，避免长时间锁定资源。
• 使用会话组锁定：Service Broker的会话组锁机制能自动保证同一相关消息组的顺序处理，同时允许不同组的消息并行处理。
🔒 安全性设计
为确保企业级应用的安全，需关注以下几点：
• 最小权限原则：为处理存储过程创建独立的数据库用户，仅授予其必要的执行权限（如对任务表的读/写权限、对目标存储过程的执行权限），避免使用过高权限的账户。
• 防止SQL注入：在动态执行存储过程名时，使用QUOTENAME()函数进行处理，避免潜在的注入风险。
• Service Broker安全配置：
o 对于内部通信，可以禁用加密（WITH ENCRYPTION = OFF）以减少开销。
o 若需跨实例通信，则应配置传输安全和对话安全，使用证书进行端点身份验证和消息加密。
💎 总结
这套方案将Service Broker的可靠异步消息传递与您业务表中的延迟控制逻辑相结合，实现了企业级应用所需的可靠性、安全性和高并发处理能力。其核心优势在于：
• 解耦：任务发起与执行分离，提升系统响应速度。
• 可靠：利用数据库事务，确保消息不丢失，任务必执行。
• 可控：通过任务表和回执标记，精确控制延迟与防重复。
• 可扩展：通过调整队列读者数量，可灵活应对不同级别的并发负载。
希望这份详细的方案对您有所帮助。如果您在具体实施过程中遇到任何问题，或者需要针对特定环节进行更深入的探讨，欢迎随时提出。

💎 触发器
Create TRIGGER trg_tabDIYTable12402_AfterInsertC
ON tabDIYTable12402
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON; -- 减少网络流量3

-- 🔄 防止递归触发[6,7](@ref)
IF TRIGGER_NESTLEVEL() > 1 -- 或使用 IF @@NESTLEVEL > 1
    RETURN;

-- 使用表变量批量处理插入的数据[5](@ref)
DECLARE @ProcessQueue TABLE (
    RowID INT IDENTITY(1,1),
    F108211 NVARCHAR(128),
    F108218 INT,
    F108212 NVARCHAR(200),
    F108213 NVARCHAR(200),
    F108214 NVARCHAR(200),
    F108215 NVARCHAR(200),
    F108216 NVARCHAR(200),
    F108219 BIT
);

-- 批量获取需要处理的数据[4](@ref)
INSERT INTO @ProcessQueue 
SELECT 
    F108211,
    ISNULL(F108218, 0),
    F108212,
    F108213,
    F108214,
    F108215,
    F108216,
    ISNULL(F108219, 0)
FROM inserted
WHERE ISNULL(F108219, 0) = 0 
    AND F108211 IS NOT NULL; -- 关键：过滤条件前置减少数据量

-- 声明处理变量
DECLARE @CurrentRowID INT = 1,
        @MaxRowID INT,
        @SPName NVARCHAR(128),
        @DelaySeconds INT,
        @Param1 NVARCHAR(200), 
        @Param2 NVARCHAR(200), 
        @Param3 NVARCHAR(200), 
        @Param4 NVARCHAR(200), 
        @Param5 NVARCHAR(200),
        @ExecFlag BIT,
        @SQL NVARCHAR(MAX),
        @DelayString VARCHAR(8);

SELECT @MaxRowID = MAX(RowID) FROM @ProcessQueue;

-- 使用循环处理每一行（可考虑改为基于集合的操作）
WHILE @CurrentRowID <= @MaxRowID
BEGIN
    SELECT 
        @SPName = F108211,
        @DelaySeconds = F108218,
        @Param1 = F108212,
        @Param2 = F108213,
        @Param3 = F108214,
        @Param4 = F108215,
        @Param5 = F108216,
        @ExecFlag = F108219
    FROM @ProcessQueue 
    WHERE RowID = @CurrentRowID;

    BEGIN TRY
        -- 🛡️ 增强安全性：验证存储过程是否存在[5](@ref)
        IF NOT EXISTS (
            SELECT 1 FROM sys.objects 
            WHERE name = @SPName AND type = 'P'
        )
        BEGIN
            RAISERROR('存储过程 "%s" 不存在', 16, 1, @SPName);
            CONTINUE; -- 跳过当前行继续处理
        END

        -- ⏰ 延迟执行优化：避免阻塞式等待[2](@ref)
        IF @DelaySeconds > 0
        BEGIN
            -- 考虑异步处理方案，当前保持原有逻辑
            SET @DelayString = '00:00:' + RIGHT('00' +'00' , 2);--“CAST(@DelaySeconds AS VARCHAR(8))” 用第二个'00'替代了，触发器立即执行
            WAITFOR DELAY @DelayString;
        END

        -- 🔐 动态SQL安全执行[1,5](@ref)
        SET @SQL = N'EXEC dbo.' + QUOTENAME(@SPName) +  
                   N' @Param1 = @P1, 
                      @Param2 = @P2, 
                      @Param3 = @P3, 
                      @Param4 = @P4, 
                      @Param5 = @P5;';

        EXEC sp_executesql 
            @SQL,
            N'@P1 NVARCHAR(200), @P2 NVARCHAR(200), @P3 NVARCHAR(200), @P4 NVARCHAR(200), @P5 NVARCHAR(200)',
            @Param1, @Param2, @Param3, @Param4, @Param5;

        -- ✅ 更新执行标记，避免递归触发[6](@ref)
        UPDATE t
        SET t.F108219 = 1
        FROM dbo.tabDIYTable12402 t
        INNER JOIN @ProcessQueue q ON t.F108211 = q.F108211 
            AND t.F108212 = q.F108212 
            AND t.F108213 = q.F108213 
            AND t.F108214 = q.F108214 
            AND t.F108215 = q.F108215 
            AND t.F108216 = q.F108216
        WHERE q.RowID = @CurrentRowID;

    END TRY
    BEGIN CATCH
        -- 🚨 增强错误处理[4,5](@ref)
        DECLARE @ErrorMsg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        -- 记录错误详情到日志表（建议创建）
        -- INSERT INTO ErrorLog(ErrorMsg, SPName, Param1, Timestamp) 
        -- VALUES(@ErrorMsg, @SPName, @Param1, GETDATE());

        -- 抛出业务友好的错误信息
        RAISERROR('执行存储过程 %s 时出错（参数: %s）: %s', 
            @ErrorSeverity, @ErrorState, @SPName, @Param1, @ErrorMsg);
    END CATCH

    SET @CurrentRowID += 1;
END

END;
💎 存储过程结构，注意每个存储过程都写标题变量6个
ALTER PROCEDURE [dbo].[TCXG]
@SPName NVARCHAR(200) = NULL,
@Param1 NVARCHAR(200) = NULL, -- 条件参数1，设置默认值NULL是个好习惯
@Param2 NVARCHAR(200) = NULL,
@Param3 NVARCHAR(200) = NULL,
@Param4 NVARCHAR(200) = NULL,
@Param5 NVARCHAR(200) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
update tabRepData set iEditting='-1' from tabDIYTable11620 B where DataID=B.F101206 and RepID=B.F101208
END
相关基础操作：

-- 检查是否已启用
SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabase';

-- 如果未启用，则执行以下语句
-- 注意: 这需要独占数据库访问权，确保没有其他连接正在使用该数据库。
ALTER DATABASE YourDatabase SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

要查看 SQL Server 中 Service Broker 是否启用，最直接的方法是查询系统视图。具体操作和后续步骤可参考下表：
步骤操作/查询说明与解释

检查状态
SELECT name, is_broker_enabled FROM sys.databases WHERE name = '您的数据库名'; 查询 sys.databases视图。如果 is_broker_enabled字段值为 1，表示已启用；值为 0 则表示未启用
。
启用 Service Broker
ALTER DATABASE 您的数据库名 SET ENABLE_BROKER; 如果检查发现未启用（值为0），执行此命令来启用
。注意：此操作需要独占数据库连接，如果遇到阻塞，可先执行 ALTER DATABASE 您的数据库名 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;将数据库设置为单用户模式，执行完启用命令后，再执行 ALTER DATABASE 您的数据库名 SET MULTI_USER;恢复多用户模式
。
使用 NEW_BROKER
ALTER DATABASE 您的数据库名 SET NEW_BROKER; 在某些特殊情况下（例如怀疑 Service Broker 标识符损坏导致问题），可以使用此选项。它会启用 Broker 并为数据库生成一个全新的唯一标识符，但会清除所有现有的会话
。
禁用 Service Broker
ALTER DATABASE 您的数据库名 SET DISABLE_BROKER; 如果需要关闭 Service Broker 功能，可以执行此命令

过程触发器表结构
模板表名: 过程触发器_主表数据库表名: tabDIYTable12402
表别名: 过程触发器_主表模板查询:

字段编号字段ID 字段名称字段别名字段类型
F108210 108,210 过程描述过程描述字符(50以下)
F108211 108,211 过程名称过程名称字符(20以下)
F108212 108,212 变量1 变量1 字符(20以下)
F108213 108,213 变量2 变量2 字符(20以下)
F108214 108,214 变量3 变量3 字符(20以下)
F108215 108,215 变量4 变量4 字符(20以下)
F108216 108,216 变量5 变量5 字符(20以下)
F108217 108,217 创建时间创建时间日期时间
F108218 108,218 延迟秒数延迟秒数数量(整数)
F108219 108,219 回执状态回执状态数量(整数)

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

Service Broker操作说明(异步执行)

近期文章

近期评论

Service Broker操作说明(异步执行)

相关文章

表库SQL函数大全

将单一字符窜按分隔符进行拆分的视图方法

表库DIY数据库表系统字段

SQL逐行累计代码

近期文章

近期评论