Increment values in Google Cloud Spanner

Question

I'm using Google Cloud Spanner, and I need a way to Increment a Row's value by value = value + 1. This turns out to be a major headache because I don't see a way to check if the row already exists. I'm using the Ruby Client Library (gem version 1.13.1)

Currently I have the following Code that does the job, however only at a speed of around 40-50 Transactions per Second. I don't see a way to batch this because as I understand I can only batch single DML Statements.

db.transaction do |transaction|
  if transaction.execute_query("SELECT value FROM table WHERE key = 'ABC'").rows.first.to_h[:value]
    transaction.execute_update("UPDATE table SET value = value + 1 WHERE key = 'ABC'")
  else
    transaction.execute_update("INSERT INTO table (key, value) VALUES ('ABC', 1)")
  end
end

Unfortunately SQL to solve this with a single statement doesn't seem to be supported at all. (I'm getting Google::Cloud::InvalidArgumentError (3:Syntax error: Unexpected keyword ON)

db.transaction do |tx|
  tx.batch_update do |b|
    b.batch_update(
      "INSERT INTO table (key, value) VALUES ('ABC', 1) ON DUPLICATE KEY UPDATE value = value + 1"
    )
  end
end

It also doesn't seem possible to increment via Mutations. This will just create the row or overwrite the value:

db.transaction do |tx|
  tx.upsert "test", [{ key: 'ABC', value: 1 }]
end

Is there any way to do what I need with Spanner at all? The slow multi-row Solution is really sad compared to batch writing performance at 100k+ rows per second.

Answer 1

There isn't a straightforward way to accomplish what you need.

One option to explore to ensure we got reasonable throughput:

1. Do a read for all the keys you want to update or insert.
2. Construct a BatchDML with bunch of insert/update statements based on result of reads from step#1.
3. Note that updates have to be conditional updates. Example: "UPDATE table SET value = {prev_value} +1 WHERE key = "ABC" AND value = {prev_value}";

4. Execute constructed BatchDML. Note that some of the statements can fail.

   Inserts can fail if the key already exists: I believe this shouldn't be a problem as the key exists (that's the desired result for insert attempt).

   Updates may not occur if the value changes from the time we read. If we repeat this cycle, updates can happen in next batch.

As you can see, this comes with caveats.