assigning a serial number to a client from a pool of serial numbers

Question

Hello,

I have a sql server table of licence keys/serial numbers. Table structure is something like;

[ RecordId int, LicenceKey string, Status int (available, locked, used, expired etc.) AssignedTo int (customerId) .... ]

Through my ASP.NET application, when the user decides to buy a licence clicking the accept button, i need to reserve a licence key for the user. My approach is like, Select top 1 licenceKey from KeysTable Where Status = available Update KeysTable Set status = locked then return the key back to the application.

My concern is, if two asp.net threads access the same record and returns the same licencekey. What do you think is the best practice of doing such assignments ? Is there a well known aproach or a pattern to this kind of problem ? Where to use lock() statements if i need any ?

I'm using Sql Server 2005, stored procedures for data access, a DataLayer a BusinessLayer and Asp.Net GUI.

Thanks

Answer 1

I think that you should actually mark the key as unavailable in the same stored proc that you are querying for it, because otherwise there will always be some sort of race condition. Manually locking tables is not a good practise IMHO.

If you have a two staged process (e.g. like booking airline tickets), you could introduce a concept of reserving a key for a specified period of time (e.g. 30 mins), so that when you query for a new key, you reserve it at the same time.

EDIT: Locking in business logic probably would work if you can guarantee that only one process is going to change the database, but it is much better to do it on the database level, preferably in a single stored proc. To do it correctly you have to set the transaction level and use transactions in the database, just as @Adam Robinson suggested in his answer.

Answer 2

To achieve what you're talking about, you'll want to use a serializable transaction. To do this, follow this pattern:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION

--Execute select
--Execute update

COMMIT TRANSACTION

However, why do you have a table with every possible license key? Why not have a key generation algorithm, then create a new key when a user purchases it?

Answer 3

You could also try using locks (in SQL) in addition to transactions, to verify that only one thread has access at a time.

I believe that an application lock may be of help here.

Answer 4

There's probably no need to use explicit locks or transactions in this case.

In your stored procedure you can update the table and retrieve the license key in a single, atomic operation by using an OUTPUT clause in your UPDATE statement.

Something like this:

UPDATE TOP (1) KeysTable
SET Status = 'locked'
OUTPUT INSERTED.LicenseKey
-- if you want more than one column...
-- OUTPUT INSERTED.RecordID, INSERTED.LicenseKey
-- if you want all columns...
-- OUTPUT INSERTED.*
WHERE Status = 'available'