Next Number in Spanner

Question

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?

This question is to broad: describes the problem but not what has been done so far to solve it. It looks like it is just asking for advise without reading any documentation. Btw, [here is the documentation](https://cloud.google.com/spanner/docs/transactions#introduction). There is only 1 kind of transaction in Google Cloud Spanner, which is atomic, and you also can see there [code examples in all the allowed languages](https://cloud.google.com/spanner/docs/transactions#rw_transaction_example) in the same docs.. — Temu, Jul 26 '18 at 15:12
i have edited the question to help with the current plan and design. The documentation in this area is not precise. — Dave, Jul 26 '18 at 15:16

score 1 · Accepted Answer · answered Jul 27 '18 at 22:50

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).

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

What's the best way to implement a next number routine in Spanner?

As you would in any database with the assurance that read-write transactions are isolated,atomic.

Does a read within a transaction lock the row being read until the transaction completes?

Locking read-write. This type of transaction is the only transaction type that supports writing data into Cloud Spanner.

Properties A read-write transaction in Cloud Spanner executes a set of reads and writes atomically at a single logical point in time.

Atomicity, Consistency, Durability In addition to the Isolation property, Cloud Spanner provides Atomicity (if any of the writes in the transaction commit, they all commit), Consistency (the database remains in a consistent state after the transaction) and Durability (committed data stays committed.)

That's quoted directly from their documentation.

Performance >> Locking

Cloud Spanner allows multiple clients to simultaneously interact with the same database. In order to ensure the consistency of multiple concurrent transactions, Cloud Spanner uses a combination of shared locks and exclusive locks to control access to the data. When you perform a read as part of a transaction, Cloud Spanner acquires shared read locks, which allows other reads to still access the data until your transaction is ready to commit. When your transaction is committing and writes are being applied, the transaction attempts to upgrade to a exclusive lock. It blocks new shared read locks on the data, waits for existing shared read locks to clear, then places a exclusive lock for exclusive access to the data.

So... I think you can be totally cool about your concerns if you do it just like the example of read-write at the end of this very same documentation.

Next Number in Spanner

2 Answers2