ServiceBroker的队列存在自动激活(ACTIVATION)功能,其中内部激活可以激活数据库存储过程接受和处理队列的消息,而且可以启动激活存储过程的多个实例(MAX_QUEUE_READERS)。当SQLServer的SCHEDULER个数大于1(即多CPU)时,会有多个实例同时去接受并处理消息。
业务流程:
1)、将短信内容放到待发送表(如:sms_sends),触发器发送到队列
2)、SP遍历队列,调用接口发送。
启用SQL Server Service Broker
ALTER DATABASE hyMall SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE hyMall SET ENABLE_BROKER;
GO
ALTER DATABASE hyMall SET TRUSTWORTHY ON; --设置信任该数据库,不然有些SP无法执行
USE hyMall;
GO
--创建消息类型。使用时,可替换所有My__MSG名称。
create message type My__MSG
--创建约定
create contract My__MSG_contract
(
My__MSG sent by initiator
)
--创建客户端队列
create queue My__MSG_client_queue
--创建客户端服务
create service My__MSG_client on queue My__MSG_client_queue;
--创建库存队列
create queue My__MSG_queue
--创建库存更新服务
create service My__MSG
on queue My__MSG_queue([My__MSG_contract]);
触发器
ALTER TRIGGER [dbo].[触发器名称]
ON [dbo].[表名]
AFTER INSERT
AS
BEGIN
SET NOCOUNTON;
declare@mobile varchar(50)
declare@msg varchar(50)
select @m=mobile,@n=msg From 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_idmessage type 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_id2 is 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
---
end conversation @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