Optimal solution for massive number of requests on one database table

Question

We have a system where customers are allocated a product on a first come first served basis.

Our products table contains an incrementing primary key that started at zero which we use to keep track of how many products have been allocated i.e. a user reserves a product and gets allocated 1, next user gets 2 etc.

The problem, is that potentially hundreds of thousands of users will access the system in any given hour. All of whom will be hitting this one table.

Since we need to ensure that each customer is only allocated one product and keep track of how many products have been allocated, we use a row lock for each customer accessing the system to ensure they write to the table before the next customer hits the system - i.e. enforcing the first come first served rule.

We are concerned about the bottleneck that is the processing time of each request coming into SQL Server 2008 Enterprise Edition and the row lock.

We can't use multiple servers as we need to ensure the integrity of the primay key so anything that requires replication isn't going to work.

Does anyone know of any good solutions that are particularly efficient at handling a massive number of requests on one database table?

A bit more info:
The table in question essentially contains two fields only - ID and CustomerID. The solution is for a free giveaway of a million products - hence the expectation of high demand and why using the incrementing primary key as a key makes sense for us - once the key hits a million, no more customers can register. Also, the products are all different so allocation of the correct key is important e.g. first 100 customers entered receieve a higher value product than the next 100 etc

Answer 1

First, to remove the issue of key generation, I would generate them all in advance. It's only 1m rows and it means you don't have to worry about managing the key generation process. It also means you don't have to worry about generating too many rows accidentally, because once you have the table filled, you will only do UPDATEs, not INSERTs.

One important question here is, are all 1m items identical or not? If they are, then it doesn't matter what order the keys are in (or even if they have an order), so as customers submit requests, you just 'try' to UPDATE the table something roughly like this:

UPDATE TOP(1) dbo.Giveaway -- you can use OUTPUT to return the key value here
SET CustomerID = @CurrentCustomerID
WHERE CustomerID IS NULL

IF @@ROWCOUNT = 0 -- no free items left
PRINT 'Bad luck'
ELSE
PRINT 'Winner'

If on the other hand the 1m items are different then you need another solution, e.g. item 1 is X, items 2-10 are Y, 11-50 are Z etc. In this case it's important to assign customers to keys in the order the requests are submitted, so you should probably look into a queuing system of some kind, perhaps using Service Broker. Each customer adds a request to the queue, then a stored procedure processes them one at a time and assigns them the MAX free key, then returns the details of what they won.