globally unique integer based ID (sequential) for a given location

Question

Hi,

I need to create a unique ID for a given location, and the location's ID must be sequential. So its basically like a primary key, except that it is also tied to the locationID. So 3 different locations will all have ID's like 1,2,3,4,5,...,n

What is the best way to do this? I also need a safe way of getting the nextID for a given location, I'm guessing I can just put a transaction on the stored procedure that gets the next ID?

Answer 1

You'll want to wrap both the code to find the next ID and the code to save the row in the same transaction. You don't want (pseudocode):

transaction {
   id = getId
}

... other processing

transaction {
   createRowWithNewId
}

Because another object with that id could be saved during "... other processing"

Answer 2

One of the ways I've seen this done is by creating a table mapping the location to the next ID.

CREATE TABLE LocationID {
    Location varchar(32) PRIMARY KEY,
    NextID int DEFAULT(1)
}

Inside your stored procedure you can do an update and grab the current value while also incrementing the value:

...
UPDATE LocationID SET @nextID = NextID, NextID = NextID + 1 WHERE Location = @Location
...

The above may not be very portable and you may end up getting the incremented value instead of the current one. You can adjust the default for the column as desired.

Another thing to be cautious of is how often you'll be hitting this and if you're going to hit it from another stored procedure, or from application code. If it's from another stored procedure, then one at a time is probably fine. If you're going to hit it from application code, you might be better off grabbing a range of values and then doling them out to your application one by one and then grabbing another range. This could leave gaps in your sequence if the application goes down while it still has a half allocated block.