Search code examples
c#asp.netdesign-patternslockinglicense-key

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

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

Solution

  • 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'