Creating 1K IDs, assigning a transactionID to all the rows with concurrency in mind.

Question

Hi,

1. A client is going to request 1K rows of IDs from the server.
2. I have to make sure we have 1K ID's with clientID = -1, if not, I have to insert 1K new ID's into the table.
3. I then have to link those 1K ID's with the clientID
4. return reserved 1K ID's to the client.

Is it as simple as wrapping all of this into a Transaction?

Answer 1

Yes, you could wrap these all within a single transaction, and other clients will not be able to see them until you commit.

Answer 2

You want to add a second table that holds the available IDs, like this:

AssignedIdsByClient:

• AssignedId int identity

• ClientId int - the customer you gave this ID range to

But when you need 1,000 ID's, don't insert 1,000 records into here: instead, take this AssignedID and multiply it times 1,000. So for example, if someone gets AssignedID 15, that means they own IDs from 15,000 to 15,999 in the other table where you actually need the ID's.

Otherwise, if you're trying to insert 1,000 records in a single transaction every time somebody wants an ID range, you're going to have a concurrency nightmare.