Some trivial transactions take dozens of seconds to complete on Spanner microinstance

Question

Here are some bits of context.

Nodejs server, connecting to Cloud Spanner from development machine. Most of the time the queries take like 200-400ms including data transfer from servers location to my dev machine.

But sometimes these trivial transaction takes 12-16 seconds which surely not acceptable for use case - sessions storage for backend server.

In local dev context sessions service runs on same machine as main backend, at staging at prod they run in same Kubernetes cluster. This is not about amount of data, it is very small amount of data now in our staging Spanner database overall, like few MB across all tables and just like 10 rows in the table under question.

Spanner instance stats:

• Processing units: 100
• CPU utilization: 4.3% for the staging database and 10% overall for instance.

Table is like so (few other small fields omitted):

CREATE TABLE sessions 
(
    id STRING(255) NOT NULL,
    created TIMESTAMP,
    updated TIMESTAMP,
    status STRING(16),
    is_local BOOL,
    user_id STRING(255),
    anonymous BOOL,
    expires_at TIMESTAMP,
    last_activity_at TIMESTAMP,
    json_data STRING(MAX),
) PRIMARY KEY(id);

Transaction under question makes single question like this:

UPDATE ${schema.reportsTable} 
SET ${statusCol.columnName} = @status_recycled 
WHERE ${idCol.columnName} = @id_value 
  AND ${statusCol.columnName} = @status_active

with parameters like this:

{
   "id_value": "some_session_id",
   "status_active": "active",
   "status_recycled": "recycled"
}

Yes, that status field of STRING(16) with readable names instead of boolean field is not ideal, I know, but this concept is inherited from an older code. What concerns me is that while we do not have yet too much of data there, just 10 rows or such, experience this sort of delays is surely unexpected at this scale.

Okay, I understand I am like on other side of the globe from the Spanner servers, but this usually gives delays between 200-1200 ms, not 12-16 seconds.

Delay happens quite rarely and randomly but seems to happen on queries like this.

The delay comes at commit, not at e. g. sending SQL command itself or obtaining a transaction.

I tried different query first, like

DELETE FROM Sessions WHERE id = @id_value

and it was the same - random rare long delay of 12-16 such trivial query.

Thanks a lot for your help and time.

PS: Update: actually this 12-16 seconds delay can happen at any random transaction in described context, and all of these transactions are standard CRUD single-row operations.

Update 2:

The code that sends transaction is own wrapper over the standard @google-cloud/spanner client library for nodejs.

The library gives just an easy to use wrapping around the Spanner instance, database, and transaction.

The Spanner instance and database objects are long-living singletons, I mean they do not recreated for every transaction from scratch.

The main purpose of that wrapper is to give logic like:

let result = await useDataContext(async(ctx) => {
   let sql = await ctx.getSQLRunner();
   return await sql.runSQLUpdate({
      sql: `Some SQL Trivial Statement`,
      parameters: {
         param1: 1,
         param2: true,
         param3: "some string"
      }
   });
});

purpose of that is to give some warrantees that if some changes were made over data, transaction.commit surely will be called, and if no changes were made, transaction.end will be called, and if an error boom in the called code, like invalid SQL generated or some variable will be undefined or null, transaction rollback will be initiated.