Running auctions on SQL Server 2005

  • Article

[Attachment: CommodityExchange.zip]

We studied auctions in our e-commerce and AI classes at U of M. In one of our assignments we were asked to configure an agent that played the Trading Agent Competition (TAC) which involved a variety of auctions for buying or trading airline tickets, hotel reservations and entertainment tickets. The agents participating in the competition talked to the auction server using a binary TCP protocol. In the real world, the protocol would require to have reliability and security and the app would require to scale to thousands of agents trading at any given time.

Of particular interest to me were the entertainment ticket auctions. These were traded using continuous double auctions where agents could both sell as well as buy entertainment tickets. Continuous double auctions (or CDAs) are very common in exchanges such as the stock market. Agents submit bids of the form (i, q, p). If q > 0, this indicates that the agent is willing to buy q units of item i for a unit price no greater than p. If q < 0, then it means that the agent is willing to sell -q units of item i for a unit price no lesser than p. The auctions clear continuosly, i.e. bids match immediately if possible; otherwise they remain standing in the auction. Price quotes can be issued anytime using a snapshot of the standing bids. The price quote for an item i is specified as the bid price (i.e. the price of the highest standing buy bid) and the ask price (i.e. the price of the lowest standing sell bid). For example, consider the standing bids for some commodity (Gas) as follows:

  1. (Gas, 10, 2.07)
  2. (Gas,   5, 2.23)
  3. (Gas,   6, 2.45)
  4. (Gas,  -7, 2.53)
  5. (Gas,  -3, 2.67)

The price quote for Gas is ask price 2.53 and bid price 2.45. Now if someone places a bid (Gas, -12, 2.14), it will immediately match bids 2 and 3 and the unmatched part (Gas, -1, 2.14) will remain standing.

To illustrate the power of SQL Server 2005 in running asychronous tasks and providing reliable message delivery using Service Broker, I decided to implement a very simple CDA server using just SQL Server 2005. The auction service runs as an internally activated stored proc. While it could have been done in just T-SQL, I wanted to dogfood the ServiceBrokerInterface library as well and so I wrote the stored proc in C#.

The attached file contains two solutions -- an updated version of the ServiceBrokerInterface library and the sample called CommodityExchange. CommodityExchange comprises of two projects -- ExchangeService, the internally activated CLR stored proc and TradingAgent, a Winforms client to participate in the auction.

Various agents that want to participate in the auction implement an [agent_service]. [agent_service]s talk to the [exchange_service] using the [auction_contract] contract which defines three message types -- [submit_bid_message], [retract_bid_message] sent by the [agent_service]s and [clear_bid_message] sent by exchange service. In order to participate in the auction, [agent_service]s begin a conversation with the [exchange_service]. The conversation handle uniquely identifies the agent's session. Agents can submit or retract bids of the form (i,  q, p) to the [exchange_service]. The stored proc that processes these messages is activated automatically by SQL Server. The stored proc executes the business logic governing CDAs, i.e. it attempts to match bids immediately in price order and store unmatched bids in a standing bids table. When bids are matched, the [exchange_service] sends notification messages to the agents involved. If the stored proc cannot keep up with the rate of incoming messages, SQL Server will activate multiple instances of the stored proc to run in parallel (configured to a max of 4), thus allowing the app to scale.

You can try the sample by building the solutions and running the install.cmd script. The script sets up the [exchange_service] in a database called [exchange_db] and also sets up two sample agent services [alice_agent_service] and [bob_agent_service] in their corresponding databases [alice_db] and [bob_db]. Public key certificates of service owners are exported and exchanged in order to use full dialog security.