What's the best way to implement a next number routine in Spanner?
Background: We need to generate sequential numbers for assignment. Multiple concurrent users can be requesting the next number. We want to ensure that no user receives the same number.
Current design is to have a table that contains the last number used. This column will be read, incremented by an amount and then written out. Does a read within a transaction lock the row being read until the transaction completes?
Be very careful when dealing with sequences in Cloud Spanner, as this can be an anti-pattern that can lead to hotspots (i.e. performance problems). Are you really sure you absolutely need sequential numbers? If you just need to assign people unique identifiers, there are ways to do this without requiring them to be sequential. For example, see this answer here on designing primary keys.
In your application description in your question, you say that multiple concurrent users could be requesting the next number. Is this value (next_number) globally unique across your entire application? If so, then this value is going to be a hotspot and could limit the scalability of your database (and application), as it means the performance of your database will be limited by how fast a single machine can process transactions on this single row. Could you somehow have a different next_number for different users/entities in your database? For example, could each user have a next_number value? Or perhaps you could "shard" your application so that there are hundreds or thousands of values for next_number? E.g.
CREATE TABLE MyTable(
ShardNum INT64 NOT NULL,
NextNumber INT64 NOT NULL
.. etc..
) PRIMARY KEY (ShardNum, NextNumber)
Note that I'm merely discussing the performance of your application. As far as correctness goes (i.e. ensuring next_value is unique), Cloud Spanner ReadWrite Transactions ensure that two readers will not read the same value of next_number (or, more precisely, two readers will not both be allowed to commit). So as long as you use ReadWrite transactions for your read-modify-commit flow, you should be fine (from a correctness perspective).