Assigning "unique" numeric ids when auto-increment won't work
-
So I've got a bit of a legacy-interop issue.
Current State:
Foobarentities are identified by an externally-generated numeric id. Effectively an auto-increment database primary key. We made the mistake of not also keeping our own, local id. Effectively, the current id is a reference to an id...in a different database because ourFoobarentities are effectively an extension of that other entity, just with the properties we care about. But guaranteed 1:1. Whenever an <external> entity was created, we got an event and generated our own copy with that same id.Lots of internal state references these numeric
Foobarids. In lots and lots of inconvenient places, including mobile clients on a completely separate release cadence.New State: Now, the corresponding external entity will be identified by uuid instead of a numeric id at all. But during the transition period, we're going to have to support both things. And we can't (don't have the team bandwidth) to update all the clients to do everything with this new uuid.
So it seems we're going to have to continue (for a time, at least) with internal references being numeric and linked to a uuid in a table. Not a problem--just generate a numeric id in our database and fudge the code to translate as needed.
Problem: I need a way to generate those numeric ids that
- will be "unique" (ie guaranteed to not conflict with anything already existing)
- will not suffer collisions if multiple replicas of this service are creating records simultaneously
- is performant, knowing that the database is not on the same machine as the service (interconnect is fast, but not instant).
- can handle the existing records having gaps -- not every existing external entity has a matching record on our side, and some have been deleted.
The id column is currently marked as unique, so I could just add some large number to what we have now and then insert in a loop until it works, then return that insert id. Is that the best way? Or is there some other nifty algorithm/process here?
The database is AWS Aurora, running in MySQL 5.7 compatibility mode.
-
Why can't you just make the numeric field in your database an autoincrement? Databases usually (and it looks like this includes MySQL) let you specify the starting number, so you can just take the highest existing + 1 (or + 1 million, or whatever) as the starting point.
-
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
will not suffer collisions if multiple replicas of this service are creating records simultaneously
Is this a limited number of replicas, or an effectively unlimited number? Because for a limited number, you could just assign each replica their own range of number space they can assign. Like ( <instance number> x 1M ) + <running number>.
And if they're numerous enough to need to auto-co-ordinate, could the replicas request their assign space from a master instance?
-
I have no experience with this myself, but I'm not letting that stop me
I suggest doing what @acrow has suggested and use a Hi/Lo strategy with a master instance.
-
@Unperverted-Vixen said in Assigning "unique" numeric ids when auto-increment won't work:
Why can't you just make the numeric field in your database an autoincrement? Databases usually (and it looks like this includes MySQL) let you specify the starting number, so you can just take the highest existing + 1 (or + 1 million, or whatever) as the starting point.
Seconded.
-
@GOG @Unperverted-Vixen I'm going to try that and hope it works.
-
@Benjamin-Hall Let us know how it went.
-
@GOG said in Assigning "unique" numeric ids when auto-increment won't work:
@Benjamin-Hall Let us know how it went.
Especially if it goes poorly?
-
@izzion Unironically: yes. That's bound to provide useful information.
-
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
will not suffer collisions if multiple replicas of this service are creating records simultaneously
Is this not a solved problem in the host service?
-
@Tsaukpaetra host service? These are wanna be microservices run via. Docker swarm.
-
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
@Tsaukpaetra host service? These are wanna be microservices run via. Docker swarm.
I mean the thing that has the original IDs
-
@Tsaukpaetra said in Assigning "unique" numeric ids when auto-increment won't work:
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
@Tsaukpaetra host service? These are wanna be microservices run via. Docker swarm.
I mean the thing that has the original IDs
The new version of that doesn't have numeric ids at all. But this particular service needs them due to assumptions we made long ago. At least until we can spare the bandwidth to adjust all our stuff including the clients.
-
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
@Tsaukpaetra host service? These are wanna be microservices run via. Docker swarm.
And you don't know a priori how many instances there (will be/are at any given time) all wanting to create their own identifiers. Have I got that right?
-
@Watson said in Assigning "unique" numeric ids when auto-increment won't work:
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
@Tsaukpaetra host service? These are wanna be microservices run via. Docker swarm.
And you don't know a priori how many instances there (will be/are at any given time) all wanting to create their own identifiers. Have I got that right?
Correct. Any one of the replicas might get a request that requires looking up a foobar, creating one if it doesn't exist on our side of the fence yet. And each of the environments has both different traffic and different numbers of replicas. One shared database though, so solving it there is the easiest solution.
-
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
@Watson said in Assigning "unique" numeric ids when auto-increment won't work:
@Benjamin-Hall said in Assigning "unique" numeric ids when auto-increment won't work:
@Tsaukpaetra host service? These are wanna be microservices run via. Docker swarm.
And you don't know a priori how many instances there (will be/are at any given time) all wanting to create their own identifiers. Have I got that right?
Correct. Any one of the replicas might get a request that requires looking up a foobar, creating one if it doesn't exist on our side of the fence yet. And each of the environments has both different traffic and different numbers of replicas. One shared database though, so solving it there is the easiest solution.
Okay. Just use a range assignment table in the shared DB.
Snowflakes'd work. They're not even that hard, writing up a generic implementation in Java took like a day for me, but fuck that noise when the crutches are like right there.
