异步触发器发送短信

来源:互联网 发布:dota狐狸妈黑历史 知乎 编辑:程序博客网 时间:2024/06/05 07:51

ServiceBroker的队列存在自动激活ACTIVATION)功能,其中内部激活可以激活数据库存储过程接受和处理队列的消息,而且可以启动激活存储过程的多个实例(MAX_QUEUE_READERS。当SQLServer的SCHEDULER个数大于1(即多CPU)时,会有多个实例同时去接受并处理消息。


业务流程:

1)、将短信内容放到待发送表(如:sms_sends),触发器发送到队列

2)、SP遍历队列,调用接口发送。

启用SQL Server Service Broker 

 

ALTER DATABASEhyMall SET NEW_BROKER WITH ROLLBACKIMMEDIATE;

GO

ALTER DATABASEhyMall SET ENABLE_BROKER;

GO

ALTER DATABASEhyMall SET TRUSTWORTHY ON; --设置信任该数据库,不然有些SP无法执行

USE hyMall;

GO

 

 

 

--创建消息类型。使用时,可替换所有My__MSG名称。

create messagetype My__MSG

--创建约定

create contractMy__MSG_contract

(

    My__MSG sent by initiator

)

--创建客户端队列

create queueMy__MSG_client_queue

--创建客户端服务

create serviceMy__MSG_client onqueue My__MSG_client_queue;

--创建库存队列

create queueMy__MSG_queue

--创建库存更新服务

create serviceMy__MSG

on queueMy__MSG_queue([My__MSG_contract]);

 

 

 

触发器

ALTER TRIGGER[dbo].[触发器名称]

   ON  [dbo].[表名]

   AFTER INSERT

AS

BEGIN

 

    SET NOCOUNTON;

        declare@mobilevarchar(50)

        declare@msgvarchar(50)

        select @m=mobile,@n=msgFrom inserted

       

    declare @dialog_id uniqueidentifier

    begin dialog conversation @dialog_id

        FROM service My__MSG_client

        TO   service 'My__MSG'

        ON CONTRACT [My__MSG_contract]

        with encryption = off;

        SEND

        on conversation @dialog_idmessagetype My__MSG

        (

        '<xml>

        <mobile>'+@mobile+'</mobile>

        <msg>'+@msg+'</msg>

        </xml>'   

        );

END

 

 

存储过程

 

--存储过程

ALTER proc[dbo].[My__MSG_Proc]

as

 

    declare @dialog_id2 uniqueidentifier

    declare @message xml

    declare @mobile nvarchar(50);

    declare @msg nvarchar(500);

   

    while(1=1)

    begin

                    --begintransaction

                  waitfor(receive@dialog_id2 = conversation_handle,@message= message_body from [dbo].My__MSG_queue),timeout 5000;

                

                  if(@dialog_id2is not null)

                  begin

                     set @mobile = @message.value('(/xml/mobile)[1]','nvarchar(50)');

                     set @msg = @message.value('(/xml/msg)[1]','nvarchar(500)');

                     --- 业务操作

                         Declare @URLVARCHAR(200)

Set @URL=’http://www.baiud.com/?mobile=’+@mobile

                      EXEC HTTP@URL

                     ---

                         endconversation @dialog_id2;

 

                  end

                  IF (@@ROWCOUNT= 0) break;

                    --committransaction;

    end

 

 

启用队列

---启用队列的自动激活功能,激活的存储过程为上面创建的存储,设置最大读取器个数(并发)为.

 ALTER QUEUE[dbo].[My__MSG_queue]

 WITH STATUS= ON ,

 RETENTION = OFF ,

 ACTIVATION (

               STATUS = ON ,

               PROCEDURE_NAME = [dbo].[My__MSG_Proc],

               MAX_QUEUE_READERS = 1,

               EXECUTE AS N'dbo'

           ),

 POISON_MESSAGE_HANDLING(STATUS= OFF)

 

 

 

 

 

 

MAX_QUEUE_READERS不为1时,多个存储过程实例会同时并发进行,如果涉及库存问题,会造成库存信息不准确。

注:最终库存数量不一定为-3,需视Scheduler数量以及同时有多少个存储过程实例可以获得CPU来执行有关

解决方法:
一种是设置队列的自动激活的最大实例数为1,即不允许并发读取
另外就是在读取库存时,增加提示 with(holdlock),这样只允许一个实例读取库存表的一行数据,直到事务结束。

 

 

ALTER proc[dbo].[HTTP]

  @url varchar(2000)

as

 

declare @ServiceUrl varchar(500)

set @ServiceUrl=@url

Declare @Object as Int

Declare @ResponseText as Varchar(8000)

Exec sp_OACreate 'MSXML2.XMLHTTP', @ObjectOUT;

Exec sp_OAMethod @Object, 'open', NULL,'get',@ServiceUrl,'false'

Exec sp_OAMethod @Object, 'send'

Exec sp_OAMethod @Object, 'responseText',@ResponseText OUTPUT

Select @ResponseText    

Exec sp_OADestroy @Object

GO


0 0
原创粉丝点击