The Spanner JDBC driver (Java Database Connectivity) supports sessionmanagement statements, which let you modify the state of your connection,execute transactions, and efficiently execute batches of statements.

The following commands apply to GoogleSQL-dialect databases.

SHOWVARIABLEREADONLYSETREADONLY={true|false}

SETREADONLY=TRUE;-- This transaction is a read-only transaction.BEGINTRANSACTION;-- The following two queries both use the read-only transaction.SELECTFirstName,LastNameFROMSingersORDERBYLastName;SELECTTitleFROMAlbumsORDERBYTitle;-- This shows the read timestamp that was used for the two queries.SHOWVARIABLEREAD_TIMESTAMP;-- This marks the end of the read-only transaction. The next statement starts-- a new read-only transaction.COMMIT;

SHOWVARIABLEAUTOCOMMITSETAUTOCOMMIT={true|false}

-- The default value for AUTOCOMMIT is true.SHOWVARIABLEAUTOCOMMIT;-- This insert statement is automatically committed after it is executed, as-- the connection is in autocommit mode.INSERTINTOT(id,col_a,col_b)VALUES(1,100,1);-- Turning off autocommit means that a new transaction is automatically started-- when the next statement is executed.SETAUTOCOMMIT=FALSE;-- The following statement starts a new transaction.INSERTINTOT(id,col_a,col_b)VALUES(2,200,2);-- This statement uses the same transaction as the previous statement.INSERTINTOT(id,col_a,col_b)VALUES(3,300,3);-- Commit the current transaction with the two INSERT statements.COMMIT;-- Transactions can also be executed in autocommit mode by executing the BEGIN-- statement.SETAUTOCOMMIT=TRUE;-- Execute a transaction while in autocommit mode.BEGIN;INSERTINTOT(id,col_a,col_b)VALUES(4,400,4);INSERTINTOT(id,col_a,col_b)VALUES(5,500,5);COMMIT;

SHOWVARIABLERETRY_ABORTS_INTERNALLYSETRETRY_ABORTS_INTERNALLY={true|false}

You can change the value of this property only after a transaction has started(seeBEGIN TRANSACTION) and beforeany statements are executed within the transaction.

When you setRETRY_ABORTS_INTERNALLY to true, the connection keeps achecksum of all data that the connection returns to the client application. Thisis used to retry the transaction if it is aborted by Spanner.

The default value istrue. We recommend setting this value tofalse if yourapplication already retries aborted transactions.

AUTOCOMMIT_DML_MODE

ASTRING property indicating the autocommit mode forData Manipulation Language (DML) statements.

SHOWVARIABLEAUTOCOMMIT_DML_MODESETAUTOCOMMIT_DML_MODE={'TRANSACTIONAL'|'PARTITIONED_NON_ATOMIC'}

The possible values are:

• InTRANSACTIONAL mode, the driver executes DML statements as separate atomictransactions. The driver creates a new transaction, executes the DMLstatement, and either commits the transaction upon successful execution orrolls back the transaction in the case of an error.
• InPARTITIONED_NON_ATOMIC mode, the driver executes DML statements aspartitioned update statements. A partitionedupdate statement can run as a series of many transactions, each covering asubset of the rows impacted. The partitioned statement providesweakened semantics in exchange for better scalability and performance.

The default isTRANSACTIONAL.

-- Change autocommit DML mode to use Partitioned DML.SETAUTOCOMMIT_DML_MODE='PARTITIONED_NON_ATOMIC';-- Delete all singers that have been marked as inactive.-- This statement is executed using Partitioned DML.DELETEFROMsingersWHEREactive=false;-- Change DML mode back to standard `TRANSACTIONAL`.SETAUTOCOMMIT_DML_MODE='TRANSACTIONAL';

STATEMENT_TIMEOUT

A property of typeSTRING indicating the current timeout value for statements.

SHOWVARIABLESTATEMENT_TIMEOUTSETSTATEMENT_TIMEOUT={'<INT64>{ s | ms | us | ns }'|NULL}

TheINT64 value is a whole number followed by a suffix indicating the timeunit. A value ofNULL indicates that there is no timeout value set. If astatement timeout value has been set, statements that take longer than thespecified timeout value will cause ajava.sql.SQLTimeoutException error andinvalidate the transaction.

The supported time units are:

• s: seconds
• ms: milliseconds
• us: microseconds
• ns: nanoseconds

The default isNULL, which means no timeout value is set.

A statement timeout during a transaction invalidates the transaction, allsubsequent statements in the invalidated transaction (exceptROLLBACK) fail,and the Spanner JDBC driver throws ajava.sql.SQLTimeoutException.

READ_ONLY_STALENESS

A property of typeSTRING indicating the currentread-only staleness setting thatSpanner uses for read-only transactions and queries inAUTOCOMMITmode.

SHOW VARIABLE READ_ONLY_STALENESSSET READ_ONLY_STALENESS =staleness_typestaleness_type:{ 'STRONG'  | 'MIN_READ_TIMESTAMPtimestamp'  | 'READ_TIMESTAMPtimestamp'  | 'MAX_STALENESS <INT64>{ s | ms | us | ns }'  | 'EXACT_STALENESS <INT64>{ s | ms | us | ns }' }

Theread-only staleness value applies to allsubsequent read-only transactions and for all queries inAUTOCOMMIT mode.

The default isSTRONG.

The timestamp bound options are as follows:

• STRONG tells Spanner to perform astrong read.
• MAX_STALENESS defines the time interval Spanner uses to perform abounded staleness read,relative tonow().
• MIN_READ_TIMESTAMP defines an absolute time Spanner uses to performabounded staleness read.
• EXACT_STALENESS defines the time interval Spanner uses to perform anexact staleness read,relative tonow().
• READ_TIMESTAMP defines an absolute time Spanner uses to perform anexact staleness read.

Timestamps must use the following format:

YYYY-[M]M-[D]DT[[H]H:[M]M:[S]S[.DDDDDD]][timezone]

The supported time units for settingMAX_STALENESS andEXACT_STALENESSvalues are:

• s: seconds
• ms: milliseconds
• us: microseconds
• ns: nanoseconds

You can modify the value of this property only while there is no activetransaction.

-- Set the read-only staleness to MAX_STALENESS 10 seconds.SETREAD_ONLY_STALENESS='MAX_STALENESS 10s';-- Execute a query in auto-commit mode. This returns results that are up to-- 10 seconds stale.SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Read-only staleness can also be applied to read-only transactions.-- MAX_STALENESS is only allowed for queries in autocommit mode.-- Change the staleness to EXACT_STALENESS and start a read-only transaction.SETREAD_ONLY_STALENESS='EXACT_STALENESS 10s';BEGIN;SETTRANSACTIONREADONLY;SELECTFirstName,LastNameFROMSingersORDERBYLastName;SELECTTitle,SingerIdFROMAlbumsORDERBYTitle;COMMIT;-- Set the read staleness to an exact timestamp.SETREAD_ONLY_STALENESS='READ_TIMESTAMP 2024-01-26T10:36:00Z';SELECTFirstName,LastNameFROMSingersORDERBYLastName;

A property of typeSTRING indicating theoptimizer version. The version iseither an integer or 'LATEST'.

SHOWVARIABLEOPTIMIZER_VERSIONSETOPTIMIZER_VERSION={'version'|'LATEST'|''}

Sets the version of the optimizer to be used for all the following statements onthe connection. If the optimizer version is set to'' (the empty string), thenSpanner uses the latest version. If no optimizer version is set,Spanner uses the optimizer version that is set at the databaselevel.

The default is''.

-- Set the optimizer version to 5 and execute a query.SETOPTIMIZER_VERSION='5';SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Execute the same query with the latest optimizer version.SETOPTIMIZER_VERSION='LATEST';SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Revert back to using the default optimizer version that has been set for the-- database.SETOPTIMIZER_VERSION='';SELECTFirstName,LastNameFROMSingersORDERBYLastName;

OPTIMIZER_STATISTICS_PACKAGE

A property of typeSTRING indicating the currentoptimizer statistics packagethat is used by this connection.

SHOWVARIABLEOPTIMIZER_STATISTICS_PACKAGESETOPTIMIZER_STATISTICS_PACKAGE={'package'|''}

Sets the optimizer statistics package to use for all following statements onthe connection.<package> must be a valid package name. If no optimizerstatistics package is set, Spanner uses the optimizer statisticspackage that is set at the database level.

The default is''.

-- Show the available optimizer statistics packages in this database.SELECT*FROMINFORMATION_SCHEMA.SPANNER_STATISTICS;-- Set the optimizer statistics package and execute a query.SETOPTIMIZER_STATISTICS_PACKAGE='auto_20240124_06_47_29UTC';SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Execute the same query with the default optimizer statistics package.SETOPTIMIZER_STATISTICS_PACKAGE='';SELECTFirstName,LastNameFROMSingersORDERBYLastName;

RETURN_COMMIT_STATS

A property of typeBOOL indicating whether statistics should be returned fortransactions on this connection. You can see returned statistics by executingtheSHOW VARIABLE COMMIT_RESPONSE command.

SHOWVARIABLERETURN_COMMIT_STATSSETRETURN_COMMIT_STATS={true|false}

The default isfalse.

-- Enable the returning of commit stats.SETRETURN_COMMIT_STATS=true;-- Execute a transaction.BEGIN;INSERTINTOT(id,col_a,col_b)VALUES(1,100,1),(2,200,2),(3,300,3);COMMIT;-- View the commit response with the transaction statistics for the last-- transaction that was committed.SHOWVARIABLECOMMIT_RESPONSE;

RPC_PRIORITY

A property of typeSTRING indicating the relative priority forSpanner requests. The priority acts as a hint to theSpanner scheduler and doesn't guarantee order of execution.

SHOWVARIABLERPC_PRIORITYSETRPC_PRIORITY={'HIGH'|'MEDIUM'|'LOW'|'NULL'}

'NULL' means that no hint should be included in the request.

The default is'NULL'.

You can also use a statement hint to specify the RPC priority:

@{RPC_PRIORITY=PRIORITY_LOW}SELECT*FROMAlbums

For more information, seePriority.

Tags

The following statements managerequest and transaction tags.

STATEMENT_TAG

A property of typeSTRING that contains the request tag for the nextstatement.

SHOWVARIABLESTATEMENT_TAGSETSTATEMENT_TAG='tag-name'

Sets the request tag for the next statement to be executed. Only onetag can be set per statement. The tag doesn't span multiple statements; itmust be set on a per statement basis. A request tag can be removed by setting itto the empty string ('').

The default is''.

You can set both transaction tags and statement tags for the same statement.

You can also use a statement hint to add a statement tag:

@{STATEMENT_TAG='my-tag'}SELECT*FROMAlbums

For more information, seeTroubleshoot with request tags and transaction tags.

-- Set the statement tag that should be included with the next statement.SETSTATEMENT_TAG='tag1';SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- The statement tag property is cleared after each statement execution.SHOWVARIABLESTATEMENT_TAG;-- Set another tag for the next statement.SETSTATEMENT_TAG='tag2';SELECTTitleFROMAlbumsORDERBYTitle;-- Set a statement tag with a query hint.@{STATEMENT_TAG='tag3'}SELECTTrackNumber,TitleFROMTracksWHEREAlbumId=1ANDSingerId=1ORDERBYTrackNumber;

TRANSACTION_TAG

A property of typeSTRING that contains the transaction tag for the nexttransaction.

SHOWVARIABLETRANSACTION_TAGSETTRANSACTION_TAG='tag-name'

Sets the transaction tag for the current transaction to be executed. Only onetag can be set per transaction. The tag doesn't span multiple transactions; itmust be set on a per transaction basis. A transaction tag can be removed bysetting it to the empty string (''). The transaction tag must be set beforeany statements have been executed in the transaction.

The default is''.

You can set both transaction tags and statement tags for the same statement.

For more information, seeTroubleshoot with request tags and transaction tags.

BEGIN;-- Set the transaction tag for the current transaction.SETTRANSACTION_TAG='transaction-tag-1';-- Set the statement tag that should be included with the next statement.-- The statement will include both the statement tag and the transaction tag.SETSTATEMENT_TAG='select-statement';SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- The statement tag property is cleared after each statement execution.SHOWVARIABLESTATEMENT_TAG;-- Set another tag for the next statement.SETSTATEMENT_TAG='insert-statement';INSERTINTOT(id,col_a,col_b)VALUES(1,100,1);COMMIT;-- The transaction tag property is cleared when the transaction finishes.SHOWVARIABLETRANSACTION_TAG;

Transaction statements

The following statements manage and commit Spanner transactions.

READ_TIMESTAMP

SHOWVARIABLEREAD_TIMESTAMP

Returns a result set with one row and one column of typeTIMESTAMP containingthe read timestamp of the most recent read-only transaction. This statementreturns a timestamp only when either a read-only transaction is still active andhas executed at least one query, or immediately after a read-only transaction iscommitted and before a new transaction starts. Otherwise, the result isNULL.

-- Execute a query in autocommit mode using the default read-only staleness-- (strong).SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Show the read timestamp that was used for the previous query.SHOWVARIABLEREAD_TIMESTAMP;-- Set a non-deterministic read-only staleness and execute the same query.SETREAD_ONLY_STALENESS='MAX_STALENESS 20s';SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Show the read timestamp that was used for the previous query. The timestamp-- is determined by Spanner, and is guaranteed to be no less than-- 20 seconds stale.SHOWVARIABLEREAD_TIMESTAMP;-- The read timestamp of a read-only transaction can also be retrieved.SETREAD_ONLY_STALENESS='STRONG';BEGIN;SETTRANSACTIONREADONLY;SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Show the read timestamp of the current read-only transaction. All queries in-- this transaction will use this read timestamp.SHOWVARIABLEREAD_TIMESTAMP;SELECTTitleFROMAlbumsORDERBYTitle;-- The read timestamp is the same as for the previous query, as all queries in-- the same transaction use the same read timestamp.SHOWVARIABLEREAD_TIMESTAMP;COMMIT;

COMMIT_TIMESTAMP

SHOWVARIABLECOMMIT_TIMESTAMP

Returns a result set with one row and one column of typeTIMESTAMP containingthe commit timestamp of the last read-write transaction thatSpanner committed. This statement returns a timestamp only whenyou execute it after you commit a read-write transaction and before you executeany subsequentSELECT,DML, or schema change statements. Otherwise, theresult isNULL.

-- Execute a DML statement.INSERTINTOT(id,col_a,col_b)VALUES(1,100,1),(2,200,2),(3,300,3);-- Show the timestamp that the statement was committed.SHOWVARIABLECOMMIT_TIMESTAMP;

COMMIT_RESPONSE

SHOWVARIABLECOMMIT_RESPONSE

Returns a result set with one row and two columns:

• COMMIT_TIMESTAMP (type=TIMESTAMP) indicates when the most recenttransaction was committed.
• MUTATION_COUNT (type=INT64) indicates how many mutations were applied inthe committed transaction. This value is always empty when executed on theemulator.

The mutation count is available only ifSET RETURN_COMMIT_STATS wasset totrue prior to the transaction commit.

-- Enable returning commit stats in addition to the commit timestamp.SETRETURN_COMMIT_STATS=true;-- Execute a DML statement.INSERTINTOT(id,col_a,col_b)VALUES(1,100,1),(2,200,2),(3,300,3);-- Show the timestamp that the statement was committed.SHOWVARIABLECOMMIT_RESPONSE;

BEGIN [TRANSACTION]

BEGIN[TRANSACTION]

Starts a new transaction. The keywordTRANSACTION is optional.

• UseCOMMIT orROLLBACK to terminate a transaction.
• If you have enabledAUTOCOMMIT mode, this statementtemporarily takes the connection out ofAUTOCOMMIT mode. The connectionreturns toAUTOCOMMIT mode when the transaction ends.
• The transaction mode is determined by the currentREADONLY setting for thisconnection. This value is set by using theSET READONLY = {TRUE | FALSE}command.
• The transaction mode can be changed by executingSET TRANSACTION READ ONLYorSET TRANSACTION READ WRITE directly after executingBEGIN [TRANSACTION].

You can execute this statement only while there is no active transaction.

-- This starts a transaction using the current defaults of this connection.-- The value of READONLY determines whether the transaction is a-- read-write or a read-only transaction.BEGIN;INSERTINTOT(id,col_a,col_b)VALUES(1,100,1);COMMIT;-- Set READONLY to TRUE to use read-only transactions by default.SETREADONLY=TRUE;-- This starts a read-only transaction.BEGIN;SELECTFirstName,LastNameFROMSingersORDERBYLastName;COMMIT;-- Execute 'SET TRANSACTION READ WRITE' or 'SET TRANSACTION READ ONLY' directly-- after the BEGIN statement to override the current default of the connection.SETREADONLY=FALSE;BEGIN;SETTRANSACTIONREADONLY;SELECTFirstName,LastNameFROMSingersORDERBYLastName;COMMIT;

COMMIT [TRANSACTION]

COMMIT[TRANSACTION]

Commits the current transaction. The keywordTRANSACTION is optional.

• Committing a read-write transaction makes all updates of this transactionvisible to other transactions and releases all of the transaction's locks onSpanner.
• Committing a read-only transaction ends the current read-only transaction. Anysubsequent statement starts a new transaction. There is no semantic differencebetweenCOMMIT andROLLBACK for a read-only transaction.

You can execute this statement only while there is an active transaction.

-- Execute a regular read-write transaction.BEGIN;INSERTINTOT(id,col_a,col_b)VALUES(1,100,1);COMMIT;-- Execute a read-only transaction. Read-only transactions also need to be-- either committed or rolled back in the Spanner JDBC driver in order-- to mark the end of the transaction.BEGIN;SETTRANSACTIONREADONLY;SELECTFirstName,LastNameFROMSingersORDERBYLastName;COMMIT;

ROLLBACK [TRANSACTION]

ROLLBACK[TRANSACTION]

Performs aROLLBACK of the current transaction. The keywordsTRANSACTION isoptional.

• Performing aROLLBACK of a read-write transaction clears any bufferedmutations, rolls back the transaction on Spanner, and releasesany locks the transaction held.
• Performing aROLLBACK of a read-only transaction ends the current read-onlytransaction. Any subsequent statements start a new transaction. There is nosemantic difference betweenCOMMIT andROLLBACK for a read-onlytransaction on a connection.

You can execute this statement only while there is an active transaction.

-- Use ROLLBACK to undo the effects of a transaction.BEGIN;INSERTINTOT(id,col_a,col_b)VALUES(1,100,1);-- This ensures that the insert statement is not persisted in the database.ROLLBACK;-- Read-only transactions also need to be either committed or rolled back in the-- Spanner JDBC driver in order to mark the end of the transaction.-- There is no semantic difference between rolling back or committing a-- read-only transaction.BEGIN;SETTRANSACTIONREADONLY;SELECTFirstName,LastNameFROMSingersORDERBYLastName;ROLLBACK;

SET TRANSACTION

SETTRANSACTION{READONLY|READWRITE}

Sets the transaction mode for the current transaction.

You can execute this statement only whenAUTOCOMMIT isfalse, or if you havestarted a transaction by executingBEGIN [TRANSACTION] and have not yetexecuted any statements in the transaction.

This statement sets the transaction mode for the current transaction only. Whenthe transaction commits or rolls back, the next transaction uses the defaultmode for the connection (seeSET READONLY).

-- Start a transaction and set the transaction mode to read-only.BEGIN;SETTRANSACTIONREADONLY;SELECTFirstName,LastNameFROMSingersORDERBYLastName;-- Commit the read-only transaction to mark the end of the transaction.COMMIT;-- Start a transaction and set the transaction mode to read-write.BEGIN;SETTRANSACTIONREADWRITE;INSERTINTOT(id,col_a,col_b)VALUES(1,100,1);COMMIT;

STARTBATCHDDL

-- Start a DDL batch. All following statements must be DDL statements.STARTBATCHDDL;-- This statement is buffered locally until RUN BATCH is executed.CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(MAX),LastNameSTRING(MAX))PRIMARYKEY(SingerId);-- This statement is buffered locally until RUN BATCH is executed.CREATETABLEAlbums(AlbumIdINT64NOTNULL,TitleSTRING(MAX),SingerIdINT64,CONSTRAINTfk_albums_singersFOREIGNKEY(SingerId)REFERENCESSingers(SingerId))PRIMARYKEY(AlbumId);-- This runs the DDL statements as one batch.RUNBATCH;

RUNBATCH

-- Start a DDL batch. All following statements must be DDL statements.STARTBATCHDDL;-- The following statements are buffered locally.CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(MAX),LastNameSTRING(MAX))PRIMARYKEY(SingerId);CREATETABLEAlbums(AlbumIdINT64NOTNULL,TitleSTRING(MAX),SingerIdINT64,CONSTRAINTfk_albums_singersFOREIGNKEY(SingerId)REFERENCESSingers(SingerId))PRIMARYKEY(AlbumId);-- This aborts the DDL batch and removes the DDL statements from the buffer.ABORTBATCH;

STARTBATCHDML;INSERTINTOMYTABLE(ID,NAME)VALUES(1,'ONE');INSERTINTOMYTABLE(ID,NAME)VALUES(2,'TWO');RUNBATCH;

-- Start a DML batch. All following statements must be a DML statement.STARTBATCHDML;-- The following statements are buffered locally.INSERTINTOMYTABLE(ID,NAME)VALUES(1,'ONE');INSERTINTOMYTABLE(ID,NAME)VALUES(2,'TWO');-- This sends the statements to Spanner.RUNBATCH;-- DML batches can also be part of a read/write transaction.BEGIN;-- Insert a row using a single statement.INSERTINTOMYTABLE(ID,NAME)VALUES(3,'THREE');-- Insert two rows using a batch.STARTBATCHDML;INSERTINTOMYTABLE(ID,NAME)VALUES(4,'FOUR');INSERTINTOMYTABLE(ID,NAME)VALUES(5,'FIVE');RUNBATCH;-- Rollback the current transaction. This rolls back both the single DML-- statement and the DML batch.ROLLBACK;

ThepartitionQueryAPI divides a query into smaller pieces, or partitions, and uses multiplemachines to fetch the partitions in parallel. Each partition is identified by apartition token. The PartitionQuery API has higher latency than the standardquery API, because it is only intended for bulk operations such as exporting orscanning the whole database.

Data Boostlets you execute analytics queries and data exports with near-zeroimpact to existing workloads on the provisioned Spanner instance.Data Boost only supportspartitioned queries.

You can enable Data Boost with theSET DATA_BOOST_ENABLED statement.

The Spanner JDBC driver supports three alternatives for executingpartitioned queries:

• SET AUTO_PARTITION_MODE = true
• RUN PARTITIONED QUERY sql
• PARTITION sql followed by multipleRUN PARTITION 'partition-token'

Each of these methods are described in the following sections.

DATA_BOOST_ENABLED

A property of typeBOOL indicating whether this connection should useData Boost for partitioned queries. The defaultisfalse.

SHOWVARIABLEDATA_BOOST_ENABLEDSETDATA_BOOST_ENABLED={true|false}

-- Enable Data Boost on this connection.SETDATA_BOOST_ENABLED=true;-- Execute a partitioned query. Data Boost is only used for partitioned queries.RUNPARTITIONEDQUERYSELECTFirstName,LastNameFROMSingers;

For a full example, seeDataBoostExample.

AUTO_PARTITION_MODE

A property of typeBOOL indicating whether the connection automatically usespartitioned queries for all queries that are executed.

SHOWVARIABLEAUTO_PARTITION_MODESETAUTO_PARTITION_MODE={true|false}

• Set this variable totrue if you want the connection to usepartitioned query for all queries that are executed.
• Also setDATA_BOOST_ENABLED totrue if you want theconnection to useData Boost for allqueries.

The default isfalse.

SETAUTO_PARTITION_MODE=trueSETDATA_BOOST_ENABLED=trueSELECTFirstName,LastNameFROMSingersSELECTSingerId,TitleFROMAlbums

For a full example, seeAutoPartitionModeExample.

RUN PARTITIONED QUERY

RUNPARTITIONEDQUERY<sql>

Executes a query as a partitioned query on Spanner. Ensure thatDATA_BOOST_ENABLED is set totrue to execute the query withData Boost:

SETDATA_BOOST_ENABLED=trueRUNPARTITIONEDQUERYSELECTFirstName,LastNameFROMSingers

The Spanner JDBC driver internally partitions the query and executespartitions in parallel. The results are merged into one result set and returnedto the application. The number of worker threads executing partitions can be setwith the variableMAX_PARTITIONED_PARALLELISM.

For a full example, seeRunPartitionedQueryExample.

PARTITION <SQL>

PARTITION<sql>

Creates a list of partitions to execute a query against Spannerand returns these a list of partition tokens. Each partition token can beexecuted on a separate connection on the same or another client using theRUN PARTITION 'partition-token' command.

-- Partition a query. This returns a list of partition tokens that can be-- executed either on this connection or on any other connection to the same-- database.PARTITIONSELECTFirstName,LastNameFROMSingers;-- Run the partitions that were returned from the previous statement.RUNPARTITION'partition-token-1';RUNPARTITION'partition-token-2';

For a full example, seePartitionQueryExample.

RUN PARTITION 'partition-token'

RUNPARTITION'partition-token'

Executes a query partition that has previously been returned by thePARTITIONcommand. The command can be executed on any connection that is connected tothe same database as the database that created the partition tokens.

MAX_PARTITIONED_PARALLELISM

A property of typeINT64 indicating the number of worker threads theSpanner JDBC driver uses to execute partitions. This value is used for:

• AUTO_PARTITION_MODE = true
• RUN PARTITIONED QUERY sql

SHOWVARIABLEMAX_PARTITIONED_PARALLELISMSETMAX_PARTITIONED_PARALLELISM=<INT64>

Sets the maximum number of worker threads that the Spanner JDBC drivercan use to execute partitions. Setting this value to0 instructs theSpanner JDBC driver to use the number of CPU cores on the clientmachine as the maximum.

The default is0.

Directed read statements

A property of typeSTRING that sets the directed read option for the followingstatements.

SHOWVARIABLEDIRECTED_READSETDIRECTED_READ='{"includeReplicas":{"replicaSelections":[{"location":"<location-name>"}]}}'

For more information, seeDirected reads.

Savepoint commands

The following statements enable and disable emulatedsavepointsin transactions. You can create a savepoint by calling thejava.sql.Connection#setSavepoint()method.

The Spanner JDBC driver emulates savepoints to support frameworks thatrely on these for nested transactions. Savepoints are emulated by keeping trackof a running checksum for the results that have been returned by statements inthe transaction. When rolling back to a savepoint, theSpanner JDBC driver rolls back the transaction, and then retries thetransaction up to the point where the savepoint was set. The checksum of theretry is compared to the checksum of the initial transaction to verify that thesame results were returned.

SAVEPOINT_SUPPORT

SHOWVARIABLESAVEPOINT_SUPPORTSETSAVEPOINT_SUPPORT={'DISABLED'|'FAIL_AFTER_ROLLBACK'|'ENABLED'}

A property of typeSTRING indicating the currentSAVEPOINT_SUPPORTconfiguration. Possible values are:

• DISABLED: All savepoint commands are disabled and will fail.
• FAIL_AFTER_ROLLBACK: Savepoint commands are enabled. Rolling back to asavepoint rolls back the entire transaction. The transaction fails if you tryto use the transaction after rolling back to a savepoint.
• ENABLED: All savepoint commands are enabled. Rolling back to a savepointwill rollback the transaction and retry is performed to the savepoint. Thisoperation fails with anAbortedDueToConcurrentModificationException error ifthe underlying data that has been used by the transaction up to the savepointhas changed.

The default value isFAIL_AFTER_ROLLBACK.

You can change the value of this variable only while there is no activetransaction.

try(Connectionconnection=DriverManager.getConnection(String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s","my-project","my-instance","my-database"))){// Savepoints can only be used when AutoCommit=false.connection.setAutoCommit(false);// Disables setting a savepoint.connection.createStatement().execute("SET SAVEPOINT_SUPPORT='DISABLED'");// The following statement fails because savepoints have been disabled.connection.setSavepoint("my_savepoint1");// Enables setting a savepoint and releasing a savepoint.// Rolling back to a savepoint is disabled.connection.createStatement().execute("SET SAVEPOINT_SUPPORT='FAIL_AFTER_ROLLBACK'");SavepointmySavepoint2=connection.setSavepoint("my_savepoint2");connection.createStatement().execute("insert into my_table (id, value) values (1, 'One')");connection.releaseSavepoint(mySavepoint2);connection.commit();// Enables setting, releasing and rolling back to a savepoint.connection.createStatement().execute("SET SAVEPOINT_SUPPORT='ENABLED'");SavepointmySavepoint3=connection.setSavepoint("my_savepoint3");connection.createStatement().execute("insert into my_table (id, value) values (2, 'Two')");connection.rollback(mySavepoint3);}

What's next

Learn how toconnect JDBC to a GoogleSQL-dialect database.