My employer sells packages to events, such as U2’s current
Vertigo concert tour. Since numerous clients and prospects visit our Web site to buy a package, we deemed it necessary to give them a 10-minute window
in which to complete the transaction. We devised a way to place a “hold”
on the number of tickets requested and then release the tickets back into the
available inventory if the 10-minute window expires. This is a great solution
if your e-commerce site offers a limited number of items that often sell out
quickly.

How I built the solution

First, I created a table called OnLineOrders,
which contains two columns of interest:

  • DateTimeEntered: a DateTime
    column whose default is GetDate().
  • HoldExpires: a calculated column whose formula is DateAdd(mi,
    10, [DateTimeEntered]).

For simplicity’s sake, on subsequent queries regarding
available inventory, I chose to add another column called IsHold
(bit).

In this application, deciding how much inventory is
available involves several business rules of little general interest. There is
a view (and a similar stored procedure) which determines the actual number by
looking in several “bins” and then subtracting the tickets currently
on hold for the Web site users.

If the Web user doesn’t complete a transaction in 10 minutes,
the hold is released. To achieve this, I created a simple stored procedure
called HoldsRelease_ap, which looks like this:

<sql>

CREATE PROCEDURE [dbo].[ReleaseHolds_ap] 

AS
    UPDATE OnLineOrders
    SET IsHold = 0
    WHERE HoldExpires < CURRENT_TIMESTAMP
    AND IsHold = 1

GO

</sql>

Follow these steps to schedule this procedure to run every
10 minutes:

  1. Open
    Enterprise Manager, select the database and its
    Stored Procedures node.
  2. Right-click
    in the Stored Procedures window and select New Stored Procedure.
  3. Write
    the stored procedure, check its syntax, and save it.
  4. On
    the Enterprise Manager menu, select Tools | Job Scheduling. This invokes a
    wizard whose first page contains an Option button set from which you’ll
    want to select the Transact SQL Command. Click Next.
  5. On
    the next page, in the big box, type EXEC
    HoldsRelease_ap     and click Next.
  6. This
    invokes a form that enables you to control the job. The current selection
    is Run Now, but instead you’ll want to select Recurring Job. This enables
    the Schedule button, which you should press.
  7. Select
    the options that are appropriate to your needs. In my case, the options
    were Run Daily, Every 10 Minutes.
  8. The
    next page allows you to augment your job by sending e-mail or net send
    messages. In my case neither was relevant, but your needs may differ.

SQL Server will now run the job every 10 minutes, changing
the IsHold flag on the appropriate rows in the OnLineOrders table. The inventory calculations will then
ignore the rows affected.

Tip: If
management decides to change the window length from say 10 minutes to 20
minutes, here’s how you would adjust the job schedule in Enterprise Manager:

  1. Expand
    the Management node.
  2. Expand
    the SQL Server Agent node.
  3. Select
    Jobs.
  4. Right-click
    on the job of interest.
  5. Select
    Properties.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!