I am working on a weird ID system where I generate all the IDs in advance and need to atomically
pop() one from the database, so no ID is read twice at the same time (and so used more than once). This is simple and straightforward when you have 1 request per minute, but hardly at 1 per second or faster than that.
I am aware of the concept of atomicity but don't know how it's typically implemented in a database. My question is, first of all, what it's roughly doing to implement it on a single machine instance. But more importantly, how it is implemented at scale.
If I want to have the ability to
pop millions or billions of IDs per second from this database, I can't conceive of anything except one physical machine with maximum file system, cpus, and memory size that today's technology allows, and it allowing traditional database atomic operations on these records. Then all HTTP requests to the machine would be taken to a single IP address of a single machine. But even with the best technology I imagine it would be a bottleneck. Could a single stock machine reach billions of writes like this per second? From my readings, the answer is no.
So I'm wondering, how do you do atomic operations on a distributed network of databases/machines? If I have 10 thousand machines, I can imagine 1 billion writes would be a piece of cake. But how do you do these atomic interactions in such a system, given there is likely more than one copy of the database, and so likely data would get out of sync, and IDs would get used more than once.
I could see this being a problem with more than just my system, but with any atomic operation.
In searching for this, you find stuff like distributed counters, but that is different. That makes sense. You write to your queue or whatever, and then sum the end result over time. It doesn't address the atomicity problem here.
It seems at some place you need a single point of entry. But I don't know how that could possibly work at scale.
On a practical level, I am looking for how to do this on Google Cloud, but that's beside the point.