October 17, 2008 at 8:58 am
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.
October 17, 2008 at 10:32 am
If inventory is an issue, I might do it the way you have listed, but do you not backorder things?
If there's an issue with payment do you want to delete stuff? I might mark it as unpaid somehow, give the customer/order entry person some time (maybe a day or few days) to fix that. Deleting data and forcing people to reenter every time is a good way to really tick people off.
I might do the temp table first, check stock, and if it's not there for some reason, fail gracefully back to the user. If it is, then I'd start the transaction and hopefully things will go quicker, the temp table will already exist, you can get through there quickly, etc.
Typically people do not put all this in a transaction. They create the header as a single transaction. Then you create the line items as the person adds them. That persists the data in case there's a hiccup. You can always clean this stuff up later.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply