使用ServiceBroker自动激活模拟"秒杀"场景

　　SQL Server Service Broker 是SQL server里面比较独特的一个功能。它可帮助开发人员构建异步的松散耦合应用程序

　　ServiceBroker入门文章：http://blogs.msdn.com/b/apgcdsd/archive/2012/07/27/sql-server-service-broker-demo.aspx

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

　　所以，接下来以发送出库消息，接受并更新库存为例，模拟并发条件下的库存检查及更新过程。

代码如下：其中inventory为库存表，表中两个字段，产品和库存数量

use mastercreate database wmsgouse wms gocreate table inventory(    material  int,    quantity  int)

创建ServiceBroker服务对象，包括消息类型、约定、队列及服务等。简化起见，演示程序是运行在同一个数据库实例下的同一个数据库中

use wms--创建消息类型create message type inventoryio--创建约定create contract inventory_contract(    inventoryio sent by initiator)--创建客户端队列create queue inventory_client_queue--创建客户端服务create service inventory_client on queue inventory_client_queue;--创建库存队列create queue inventoryio_queue--创建库存更新服务create service inventoryioon queue inventoryio_queue(    [inventory_contract]);

--发送出库消息begin transaction    declare @dialog_id uniqueidentifier    begin dialog conversation @dialog_id        from service inventory_client        to service 'inventoryio'        on contract [inventory_contract]        with encryption = off;        send on conversation @dialog_id message type  inventoryio        (        '<InventoryUpdate>        <material>1</material>        <quantity>1</quantity>        </InventoryUpdate>'            ); commit transaction;

为了记下来测试并发情况，连续运行上面的代码四次。发送完后，查询目标服务的队列

select * from inventoryio_queue

结果如下：

接下里创建存储过程，该存储过程接受队列的出库指令，检查库存，当库存满足时，更新库存；库存不足时，回滚事务，消息重新回到队列

代码如下：

create PRoc InventoryProc asbegin transaction    declare @dialog_id uniqueidentifier    declare @message_body xml    declare @quantity int;    declare @material int;        waitfor(    receive        @dialog_id = conversation_handle,        @message_body = message_body     from [dbo].inventoryio_queue),timeout 5000;        if(@dialog_id is not null)    begin         set @quantity = @message_body.value('(/InventoryUpdate/quantity)[1]','int');        set @material = @message_body.value('(/InventoryUpdate/material)[1]','int');        --检查库存是否足够        if exists( select 1 from inventory where  material = @material and quantity>=@quantity)        begin             print 'come here'            --更新库存            Update inventory set quantity =quantity-@quantity where material = @material;        end        else         begin             rollback            return        end     end    end conversation @dialog_id; commit transaction; go

启用队列的自动激活功能，激活的存储过程为上面创建的存储，设置最大读取器个数为5.

ALTER QUEUE [dbo].[inventoryio_queue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[InventoryProc] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo'  ), POISON_MESSAGE_HANDLING (STATUS = OFF) 

注意：

POISON_MESSAGE_HANDLING的设置必须为OFF，将有害消息处理设置为 OFF 的队列在五个连续的事务回滚之后不会被禁用。否则默认为ON，队列在回滚5次后，会被禁用启用自动激活后，可以查看目前激活的实例select * from sys.dm_broker_activated_tasks

insert into inventoryvalues(1,1)select * from inventoryio_queueselect * from inventory

结果如下：

可以看到队列中已经没有消息了，库存数量已经减少，但更新结果不准确.原因是在更新库存之前，多个存储过程实例都读取了库存数，并判断出库存满足，然后对库存进行了更新。注：最终库存数量不一定为-3,需视Scheduler数量以及同时有多少个存储过程实例可以获得CPU来执行有关解决方法： 一种是设置队列的自动激活的最大实例数为1，即不允许并发读取另外就是在读取库存时，增加提示 with(holdlock)，这样只允许一个实例读取库存表的一行数据，直到事务结束。可查阅这篇文章：http://www.cnblogs.com/buaaboyi/archive/2011/08/30/2159860.html代码如下：

--检查库存是否足够        if exists( select 1 from inventory with(holdlock) where  material = @material and quantity>=@quantity)

本文演示了一个ServiceBroker单数据库的简单实例，并介绍了自动激活机制。简单涉及了SQLServer的CPU调度。同时，提及了SQLServer的 with(holdlock)提示

注：本文代码仅供演示使用而非实际应用于生产环境。如有问题及建议，请指正！^_^

参考文章：http://www.cnblogs.com/markj/archive/2013/03/31/2991777.html