Transaction Integrity

It's very important that the order transaction should succeed completely and the customer gets the goods they requested, or the transaction must fail unequivocally and we must handle this failure and deal with it as we are best able. For example we don't want to add their name and address to the database then find we are unable to complete the order leaving a name and delivery address in the database but an order with no items.

The most likely failure is that we have insufficient stock to complete the customer's order. Although we checked that the stock was available when the customer first added items to their basket, time has passed since then during which someone else may have bought the remaining stock. If we are unable to fully complete the customer's order, because we are short of one or more items, then we need to inform the customer and give them the option of either proceeding with the amounts available or canceling completely.

We face certain difficulties. We need to check that sufficient quantities of each item are in stock. But if we do that first, then try and update the database we may find in that fraction of a second another concurrent user has got in first and bought our items.

I have dealt with this by checking the items exist and removing them from the database in the same stored procedure. But what if the customer has ordered 10 different items and item 9 of the list is out of stock and the customer wants to completely cancel the order? Do we keep a list of items that have been removed from the stock table and use that information to put the items back on the shelf as it were?

It all seems to be getting a little complex and too much like hard work so lets make it easier for ourselves and enlist the help of Microsoft Transaction Server and the transaction functionality it makes available to ASP.

The central focus for transactions in ASP is the ObjectContext object. This has two methods, SetComplete and SetAbort and two events, OnTransactionCommit and OnTransactionAbort.

If we wish to rollback all the database writes made in a transaction, we call the SetAbort method. If our transaction processing has completed successfully then we make a call to SetComplete and all the database changes will become permanent. Calling SetAbort results in the OnTransactionAbort event firing and SetComplete in OnTransactionCommit being fired. Note that even if SetComplete is called in one part of the code, any other part of the code calling SetAbort will cause the transaction to abort.

Although MTS will rollback all database changes, it does not currently rollback any other changes that might have occurred, for example a change to a file on disk. Also, the database must support the XA protocol - a two phase protocol that allows applications and resource managers to communicate with a transaction manager - which currently limits it to SQL Server. A further limitation is that transaction support is only valid for one page, essentially the page becomes a transaction and this must be committed or aborted before any more pages are loaded otherwise the transaction will abort.

More Stored Procedures

Before we create the ProcessOrder.asp page we need to create the 3 stored procedures it requires.

First, we need a stored procedure to add the customer's and the new order's details to the database. The stored procedure consists of 2 insert statements which add the data from the forms filled in by the customer. Because we want to make sure any error is handled and the ASP page's transaction aborted we have added error checks after each insert. If an error occurs global variable @@Error will contain its value, otherwise it contains zero. Therefore if @@Error is not zero we end the stored procedure and return @@Error which our ASP code can pick up as a return parameter of an ADO Command object.

You'll remember that our Customer and Orders table both have a primary key field which is an identity field, inserting a row automatically puts the next number in sequence in those fields. We need to know this number for later when we add order items or want to get back the customer details. To do this we use the @@Identity variable which SQL Server populates with the last identity number inserted. Thus, the @@Identity values for CustId and OrderId are put in the output variables @CustId and @OrderId which we can access later in the ASP page.

CREATE PROCEDURE [NewOrder] 
( @Title varchar(4), @FirstName varchar(50), @LastName varchar(50), 
@Email varchar(75), @Street varchar(75), @City varchar(50),
@Locality varchar(50), @PostCode varchar(15), @Country varchar(50), 
@CCHolderName varchar(50), @CCType varchar(25), @CCNo varchar(20), 
@CCExpire varchar(7), @CustId int OUTPUT, @OrderId int OUTPUT)
AS

-- Insert Customer details
INSERT INTO Customer ( Title, FirstName, LastName, Email, Street, City, _
          Locality, PostCode, Country)
  VALUES ( @Title, @FirstName, @LastName, @Email, @Street, @City, _
       @Locality, @PostCode, @Country)

IF (@@ERROR <> 0) GOTO on_error

-- Retrieve the automatically generated CustId VALUE
SET @CustId = @@IDENTITY

-- Insert order details
INSERT INTO Orders (CustId,CCHolderName,CCType,CCNo,CCExpire,OrderDate)
  VALUES (@CustId,@CCHolderName,@CCType,@CCNo,@CCExpire,GetDate())

IF (@@ERROR <> 0) GOTO on_error

-- Retrieve VALUE automatically put into OrderId field
SET @OrderId = @@IDENTITY

RETURN(0)

on_error:

RETURN(@@ERROR)

Our second stored procedure is the one that adds each item of the order to the database. First we check stock levels are sufficient to fulfill the order. We raise an error if the levels are too low to let the calling ASP program know. We return zero if everything was successful or the error number otherwise.

CREATE PROCEDURE [AddOrderItem] 
( @OrderId int, @ItemId int, @Qty int)
AS

DECLARE @Return int

-- check sufficent stock available
IF (SELECT QtyInStock FROM Stock WHERE ItemId = @ItemId) < @Qty
BEGIN
SET @Return = 547
GOTO on_error
END

Next we reduce the stock levels by the order quantity.

-- deduct stock levels
UPDATE Stock
SET QtyInStock = QtyInStock - @Qty
WHERE ItemId = @ItemId

IF (@@ERROR <> 0) 
BEGIN
SET @Return = @@ERROR
GOTO on_error
END

Finally, we add the order item information to the OrderItem table.

-- add item to orderitem table
INSERT INTO OrderItem(OrderId,ItemId,Qty)
  VALUES (@OrderId, @ItemId, @Qty)

IF (@@ERROR <> 0) 
BEGIN
SET @Return = @@ERROR
GOTO on_error
END

RETURN 0

on_error:

RETURN @Return

Our final stored procedure for this page is ItemAvailability which is used to return stock item information and quantity.

CREATE PROCEDURE [ItemAvailability] 
( @ItemId int )
AS
SELECT ArtistName, Title, QtyInStock 
FROM Stock JOIN Artist ON Artist.ArtistId = Stock.ArtistId 
WHERE ItemId = @ItemId

Before we continue we need to give the database user MMCustomer Execute permissions to the three stored procedures.

Continued...