I am in the process of creating a ecommerce solution for a small business. The site will need to take into consideration concurrency issues. I have used SQL Server before but never taken advantage of its Transaction handling. I will really be extremely grateful to anyone who can help with advice and suggestions on whether I am on the right track.

I have two tables to hold order information.
Orders - holds generic order details
  Customer Details (email, name etc)
  Order Value Details (subtotal, tax, discounts, shipping etc)
  Shipping Details (address info)
  Billing Details (probably not needed as I will be used a payment gateway such as HSBC or PayPal)
OrderItems - holds details of each of the items that make up the order
  Order - associated order record
  Stock - associated stock record
  Title - short product desc
  UnitPrice - price per stock item
  Quantity - number of these items ordered

I need to write a single proc which will place this order, creating the order record, adding each order item - checking stock levels etc.

My proposed logic to this proc is:

-- steps:
    -- 1. create order record - set status to OrderBeingPlaced (OrderStatusID = 5)
    -- 2. create temp table for holding order items (order items details are passed in a comma-delimted string)
    -- 3. populate temp table
    -- foreach item in temp table
    -- 3. lock stock record for this orderitem
    -- 4. check stock levels (rollback, unlock stock records and return if stock cant satisfy quantity being ordered)
    -- 5. add order items ()
    -- 6. update stock levels (rollback, unlock stock records and return if stock cant satisfy quantity being ordered)
    -- 7. update status to OrderPlaced - commit and return new order id


WEBSITE TRYS TO TAKE PAYMENT (using payment processor)
        -- if successful update status to PaymentRecieved
        -- if unsuccessful need to call another proc to delete order record, delete order item records and release the stock

As a precaution I will also be creating a sql job (scheduled to run every day at some specific time) to check whether there are any Orders with status OrderPlaced for longer than x hours and if so again delete order record, delete order items and release stock.

Let me know if there is any information you need to help me.

Thanks for taking the time to read this.