google-cloud-spannerlooker

Specify ML workloads to connect to Cloud Spanner Read Only replicas

Is there a way in the JDBC connection string to specify ML workloads to connect to Spanner's Read-Only replicas? Spanner's infrastructure efficiently handles such Read-only transactions?

Solution

  • You cannot use the JDBC driver to connect directly to a read-only replica, but you can use read-only transactions and specify the read staleness of the transaction (or a single query). This will effectively allow Cloud Spanner to execute the query on the closest read-only replica. Use the SET READ_ONLY_STALENESS command for this.

    Example for a single query:

    -- Set the max staleness to 15 seconds.
-- Cloud Spanner will choose the most efficient timestamp to
-- execute the query. This only works in auto commit mode.
SET READ_ONLY_STALENESS='MAX_STALENESS 15s';

SELECT * FROM my_table;

    Example for a read-only transaction with multiple queries:

    BEGIN;

-- Mark the transaction as read-only.
SET TRANSACTION READ ONLY;

-- MAX_STALENESS can only be used in auto commit.
-- Use EXACT_STALENESS or READ_TIMESTAMP for transactions.
SET READ_ONLY_STALENESS='EXACT_STALENESS 15s';

SELECT * FROM my_table;
SELECT * FROM other_table;

-- Read-only transactions are not really committed, but this
-- marks the end of the transaction for the JDBC driver.
COMMIT;