Coldfusion: Locking database tables to check inventory on ecommerce purchase

I am working on an Ecommerce site in Coldfusion. There is a limited amount of inventory. When a person checks out, I need to check the inventory db table at that moment and make sure the product is 开发者_如何学运维still available. I'm concerned about the following scenerio happening:

Person 1 submits their cart for a product with just 1 left. The code begins charging their credit card, storing their login info.

Person 2 submits their cart just behind person 1 for the same product. Person 1's code hasn't gotten to the point where it decrements the inventory value for the product to 0. Therefore the code lets Person 2 buy the product.

What's the best way to prevent this? I'm thinking I need to put a cftransaction tag around the code that does all the charging, adding of login info, and finally decrementing the value. Does a simple cftransaction tag, lock the database from being read until it completes? I've read about Isolation Levels but I'm still confused. Do I need to set a special isolation level on that cftransaction?

You need to be careful in how much locking you do, as you could end up with transactions that time-out waiting for locks to release - and unhappy customers!

You also have another scenario to look out for: user 1 and user 2 put the same item in their carts. User 1 completes the checkout, all of the way through. User 2 gets onto a phone call, then comes back in 10 minutes to finish. Your process has to be smart enough to re-validate the cart items before the purchase completes.

My suggestion is to implement a "soft-buy" process where once a person starts the checkout, the items are marked as "not available" for anyone else. If the purchase completes, it is converted to a "hard-buy" and inventory is decremented. If the user does not complete the purchase, the "soft-buy" is released.

You can indicate the soft-buy using a flag in the database table or in a soft-buy transaction table, depending on how your inventory data is stored.

Just an idea, make use of HTML5 WebSockets or Flex Data Service to make inventory available real-time to the client?

I would recommend you to use exclusive cflock.

If User 1 is going to be first in the race condition, then he will have the first priority. As you know the lock is based on first come first serve basis. He locks it and since it is of exclusive lock the USer 2 cannot read or modify. This is avoid conflict of data. Always update the table once the payment is done.