Lets take the typical .

Step 1: Check and see if the unit is available (Quantity).

Step 2: If so, go ahead and order the product.

Step 3: Transaction Complete.

Between Step 1 and Step 2, there is a chance another user (transaction) comes in and order the product thus is no longer available.

How do most systems handle this? Is there such an isolation level that will lock the table (product) table from inserting any records from any other transactions before Step 3 is completed?

Thanks!

In general, you want to keep database transactions/locks as short as possible - meaning that you don't want to lock the table at step 1 - what happens if that customer goes off for a cup of tea for 10 minutes...

One approach would have the first customer's order placing transaction subtract the required amount from the product table, and then to check the remaining quantity. If this is less that 0 then there cannot have been enough stock, so the transaction can be rolled back. The point to remember is that selecting from a table, even inside a serializable transaction, does not lock the table at all, so you can't check the stock level at the start of the transaction as it could still be modified by another transaction before your first one tried to modify the product quantity...

Matt