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

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