1.简介

　　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）时，会有多个实例同时去接受并处理消息。

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

2.实现步骤

2.1 创建测试数据库及表

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

use master

create database wms

go

use wms

go

create table inventory(

    material  int,

    quantity  int

)

2.2 创建ServiceBroker对象，搭建基础框架

创建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 inventoryio

on queue inventoryio_queue

(

    [inventory_contract]

);

2.3 开启会话，发送出库消息

--发送出库消息

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

结果如下：

2.4 创建库存更新的存储过程

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

代码如下：

create proc InventoryProc as

begin 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

2.5 修改队列，启用自动激活功能

启用队列的自动激活功能，激活的存储过程为上面创建的存储，设置最大读取器个数为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

2.6 插入库存，查看更新结果

insert into inventory

values(1,1)

select * from inventoryio_queue

select * 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)

3. 总结

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

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

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

巴特西

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