My client generates gift certificates that employers can use as Thanksgiving gifts, birthday presents, holiday bonuses, achievement awards, etc. His clients typically order anywhere from dozens to hundreds (sometimes thousands) of certificates at a time. During his busy season (toward the end of the year, when 80% of his annual orders come in) he has a staff of temporary workers to keep up with the demand.
The database tracks each certificate thru the distribution and redemption process by a unique serial number. It's up to the database to make sure that the serial numbers are in fact (a) unique and (b) sequential for any given customer. A further requirement is that, for a large order, we'd like to begin with a "nice" starting number (for example, 12345001 instead of 12344629, which otherwise would be the next available number; in such a case, 12344629 thru 12345000 would go unused, which is OK).
As long as there's only one person at a time generating certificate records, no problem. But when there are several different people doing data entry, I am concerned that 2 of them will initiate a batch of new certificates nearly simultaneously, and they'll end up dueling with each other for the next available cert number. We might end up with 2 series like
55, 56, 57, 59, 61, 63, 65 and 58, 60, 62, 64, 66, 67, 68, 69, 70
instead of the expected
55, 56, 57, 58, 59, 60, 61 and 62, 63, 64, 65, 66, 67, 68, 69, 70
Or, just as bad, both users might be expecting their own series to start on the next available "nice" number, like the 12345001 mentioned above, but only one of them will be able to.
To further complicate matters, we don't just initiate the sequencing process the very instant a button is clicked. The button is clicked, and then there are a few validity checks introduced, some of which might require user interaction (such as specifying a custom starting number), including the possibility of cancelling the attempt. These take variable amounts of time. During that time, somebody else may be trying to do the very same thing for a different customer.
Any best practices on how to avoid such dueling sequence-number generators?
PS: All data-entry people are in the same physical location, operating in real time over a LAN, so there are no internet latency issues.