The open-source JDBC (Java Database Connectivity) driver for Cloud Spanner supports session management statements, which let you modify the state of your connection, execute transactions, and efficiently execute batches of statements.
The following commands apply to PostgreSQL-dialect databases.
Connection statements
The following statements make changes to or display properties of the current connection.
SHOW [VARIABLE] SPANNER.READONLY
Returns a result set with one row and one column of type BOOL
indicating
whether or not the connection is currently in read-only mode.
SET SPANNER.READONLY
Syntax
SET SPANNER.READONLY {TO|=} { true | false }
Sets whether the connection is in read-only mode.
You can execute this statement only while there is no active transaction.
SHOW [VARIABLE] AUTOCOMMIT
Returns a result set with one row and one column of type BOOL
indicating
whether the connection is in AUTOCOMMIT
mode.
SET AUTOCOMMIT
Syntax
SET AUTOCOMMIT {TO|=} { true | false }
Sets the AUTOCOMMIT
mode of the connection. The default is true
.
You can execute this statement only when there is no active transaction.
When AUTOCOMMIT
is false, a new transaction is initiated automatically after
you execute COMMIT
or ROLLBACK
. The first statement that you execute
starts the transaction.
SHOW [VARIABLE] SPANNER.RETRY_ABORTS_INTERNALLY
Returns a result set with one row and one column of type BOOL
indicating
whether the connection automatically retries aborted transactions.
SET SPANNER.RETRY_ABORTS_INTERNALLY
Syntax
SET SPANNER.RETRY_ABORTS_INTERNALLY {TO|=} { true | false }
Sets whether the connection automatically retries aborted transactions.
You can execute this command only after a transaction is started
(see BEGIN [TRANSACTION | WORK]
) and before
any statements are executed within the transaction.
When you enable SPANNER.RETRY_ABORTS_INTERNALLY
, the connection keeps a cryptographic
checksum of all data that the connection returns to the client application and
all of the update counts that the connection reports during the transaction. If
Cloud Spanner aborts the transaction, the connection tries to execute the same
transaction and checks if the returned data is exactly equal to data returned in
the original transaction. If the data matches, Cloud Spanner continues the
transaction. If the data does not match, the transaction fails by throwing
AbortedDueToConcurrentModification
.
This setting is enabled by default. We recommend turning off this setting if your application already retries aborted transactions.
SHOW [VARIABLE] SPANNER.AUTOCOMMIT_DML_MODE
Returns a result set with one row and one column of type STRING
indicating the
autocommit mode for Data Manipulation Language (DML)
statements.
This variable has an effect only when you enable AUTOCOMMIT
mode on the
connection.
SET SPANNER.AUTOCOMMIT_DML_MODE
Syntax
SET SPANNER.AUTOCOMMIT_DML_MODE {TO|=} { 'TRANSACTIONAL' | 'PARTITIONED_NON_ATOMIC' }
Sets the autocommit mode for DML statements:
- In
TRANSACTIONAL
mode, the driver executes DML statements as separate atomic transactions. The driver creates a new transaction, executes the DML statement, and either commits the transaction upon successful execution or rolls back the transaction in the case of an error. - In
PARTITIONED_NON_ATOMIC
mode, the driver executes DML statements as partitioned update statements. A partitioned update statement can run as a series of many transactions, each covering a subset of the rows impacted. The partitioned statement provides weakened semantics in exchange for better scalability and performance.
You can execute this statement only if you have enabled AUTOCOMMIT
mode.
SHOW [VARIABLE] STATEMENT_TIMEOUT
Returns a result set with one row and one column of type STRING
indicating the
current timeout value for statements. The value is a whole number followed by a
suffix indicating the time unit. A value of DEFAULT
indicates that there is no
timeout value set. If a statement timeout value has been set, statements that
take longer than the specified timeout value will cause a
java.sql.SQLTimeoutException
and invalidate the transaction.
SET STATEMENT_TIMEOUT
Syntax
SET STATEMENT_TIMEOUT {TO|=} { '<int8>{ s | ms | us | ns }' | <int8> | DEFAULT }
Sets the statement timeout value for all subsequent statements on the connection.
The supported time units are:
s
: secondsms
: millisecondsus
: microsecondsns
: nanoseconds
The DEFAULT
is 0 seconds, which means no timeout. An int8
number without
units indicates ms
. For example, the following commands set the statement
timeout to 2 seconds.
SET STATEMENT_TIMEOUT TO 2000;
SET STATEMENT_TIMEOUT TO '2s';
A statement timeout during a transaction invalidates the transaction, all
subsequent statements in the invalidated transaction (except ROLLBACK
) fail,
and the JDBC driver throws a java.sql.SQLTimeoutException
.
SHOW [VARIABLE] SPANNER.READ_ONLY_STALENESS
Returns a result set with one row and one column of type STRING
indicating the
current read-only staleness setting that
Cloud Spanner uses for read-only transactions and queries in AUTOCOMMIT
mode. The default setting is STRONG
.
SET SPANNER.READ_ONLY_STALENESS
Syntax
SET SPANNER.READ_ONLY_STALENESS {TO|=} staleness_type staleness_type: { 'STRONG' | 'MIN_READ_TIMESTAMP timestamp' | 'READ_TIMESTAMP timestamp' | 'MAX_STALENESS <int8>{ s | ms | us | ns }' | 'EXACT_STALENESS <int8>{ s | ms | us | ns }' }
Sets the read-only staleness setting for all
subsequent read-only transactions while not in
AUTOCOMMIT
mode, and for all queries while in AUTOCOMMIT
mode.
The timestamp bound options are as follows:
STRONG
tells Cloud Spanner to perform a strong read.MAX_STALENESS
defines the time interval Cloud Spanner uses to perform a bounded staleness read, relative tonow()
.MIN_READ_TIMESTAMP
defines an absolute time Cloud Spanner uses to perform a bounded staleness read.EXACT_STALENESS
defines the time interval Cloud Spanner uses to perform an exact staleness read, relative tonow()
.READ_TIMESTAMP
defines an absolute time Cloud Spanner uses to perform an exact staleness read.
Timestamps must use the following format:
YYYY-[M]M-[D]D [[H]H:[M]M:[S]S[.DDDDDD]][timezone]
The supported time units for setting MAX_STALENESS
and EXACT_STALENESS
values are:
s
: secondsms
: millisecondsus
: microsecondsns
: nanoseconds
You can execute this statement only while there is no active transaction.
SHOW [VARIABLE] SPANNER.OPTIMIZER_VERSION
Returns a result set with one row and one column of type STRING
indicating the
optimizer version. The returned version is either numeric or 'LATEST
'.
SET SPANNER.OPTIMIZER_VERSION
Syntax
SET SPANNER.OPTIMIZER_VERSION {TO|=} '<version>'|'LATEST'|''
Sets the version of the optimizer to be used for all the following statements on
the connection. Setting the optimizer version to ''
(the empty string)
indicates to
use the latest version. If no optimizer version is set, Cloud Spanner uses
the optimizer version that is set at the database level.
SHOW [VARIABLE] SPANNER.OPTIMIZER_STATISTICS_PACKAGE
Returns a result set with one row and one column of type STRING
indicating the
current statistics package.
SET SPANNER.OPTIMIZER_STATISTICS_PACKAGE
Syntax
SET SPANNER.OPTIMIZER_STATISTICS_PACKAGE {TO|=} '<package>'|''
Sets the optimizer statistics package to use for all following statements on
the connection. <package> must be a valid package
name. Setting the package to ''
(the empty string) indicates to use the latest
package. If no optimizer statistics package is set, Cloud Spanner uses the
optimizer statistics package that is set at the database level.
SHOW [VARIABLE] SPANNER.RETURN_COMMIT_STATS
Returns a result set with one row and one column of type BOOL
indicating
whether statistics should be returned for transactions on this
connection. You can see returned statistics by using the
SHOW [VARIABLE] COMMIT_RESPONSE
command.
SET SPANNER.RETURN_COMMIT_STATS
Syntax
SET SPANNER.RETURN_COMMIT_STATS {TO|=} { true | false }
Indicates whether statistics should be returned for transactions on this
connection. You can see returned statistics by using the
SHOW [VARIABLE] COMMIT_RESPONSE
command.
SHOW [VARIABLE] SPANNER.RPC_PRIORITY
Returns a result set with one row and one column of type STRING
indicating the
relative priority for Cloud Spanner requests. The priority acts as
a hint to the Cloud Spanner scheduler and does not guarantee
order of execution.
SET SPANNER.RPC_PRIORITY
Syntax
SET SPANNER.RPC_PRIORITY {TO|=} {'HIGH'|'MEDIUM'|'LOW'|'NULL'}
Sets the relative priority for Cloud Spanner requests executed by this connection. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee order of execution.
Transaction statements
The following statements manage and commit Cloud Spanner transactions.
SHOW [ VARIABLE ] TRANSACTION ISOLATION LEVEL
Returns a result set with one row and one column of type STRING
. Returned
value is always serializable
.
SHOW [VARIABLE] SPANNER.READ_TIMESTAMP
Returns a result set with one row and one column of type TIMESTAMP
containing
the read timestamp of the most recent read-only transaction. This statement
returns a timestamp only when either a read-only transaction is still active and
has executed at least one query, or immediately after a read-only transaction is
committed and before a new transaction starts. Otherwise, the result is NULL
.
SHOW [VARIABLE] SPANNER.COMMIT_TIMESTAMP
Returns a result set with one row and one column of type TIMESTAMP
containing
the commit timestamp of the last read-write transaction that Cloud Spanner
committed. This statement returns a timestamp only when you execute it after you
commit a read-write transaction and before you execute any subsequent SELECT
,
DML
, or schema change statements. Otherwise, the result is NULL
.
SHOW [VARIABLE] SPANNER.COMMIT_RESPONSE
Returns a result set with one row and two columns:
COMMIT_TIMESTAMP
(type=TIMESTAMP
) Indicates when the most recent transaction was committed.MUTATION_COUNT
(type=int8
) Indicates how many mutations were applied in the committed transaction.
This information is available only if SET RETURN_COMMIT_STATS
was
set to true
prior to the transaction commit.
{ START | BEGIN } [ TRANSACTION | WORK ]
Syntax
{ START | BEGIN } [ TRANSACTION | WORK ] [{ READ ONLY | READ WRITE }]
Starts a new transaction. The keywords TRANSACTION
and WORK
are optional,
equivalent, and have no effect.
- Use
COMMIT
orROLLBACK
to terminate a transaction. - If you have enabled
AUTOCOMMIT
mode, this statement temporarily takes the connection out ofAUTOCOMMIT
mode. The connection returns toAUTOCOMMIT
mode when the transaction ends. - If
READ ONLY
orREAD WRITE
is not specified, the transaction mode is determined by the default transaction mode of the session. This default is set by using theSET SESSION CHARACTERISTICS AS TRANSACTION
command.
You can execute this statement only while there is no active transaction.
COMMIT [TRANSACTION | WORK]
Commits the current transaction. The keywords TRANSACTION
and WORK
are
optional and equivalent, and have no effect.
- Committing a read-write transaction makes all updates of this transaction visible to other transactions and releases all of the transaction's locks on Cloud Spanner.
- Committing a read-only transaction ends the current read-only transaction. Any
subsequent statement starts a new transaction. There is no semantic difference
between
COMMIT
andROLLBACK
for a read-only transaction.
You can execute this statement only while there is an active transaction.
ROLLBACK [TRANSACTION | WORK]
Performs a ROLLBACK
of the current transaction. The keywords TRANSACTION
and
WORK
are optional and equivalent, and have no effect.
- Performing a
ROLLBACK
of a read-write transaction clears any buffered mutations, rolls back the transaction on Cloud Spanner, and releases any locks the transaction held. - Performing a
ROLLBACK
of a read-only transaction ends the current read-only transaction. Any subsequent statements start a new transaction. There is no semantic difference betweenCOMMIT
andROLLBACK
for a read-only transaction on a connection.
You can execute this statement only while there is an active transaction.
SET TRANSACTION
Syntax
SET TRANSACTION { READ ONLY | READ WRITE }
Sets the transaction mode for the current transaction.
You can execute this statement only when AUTOCOMMIT
is false
, or
if you have started a transaction by executing
BEGIN [TRANSACTION | WORK]
and have not yet executed any statements in the transaction.
This statement sets the transaction mode for the current transaction only. When
the transaction commits or rolls back, the next transaction uses the default
mode for the connection. (See SET SESSION CHARACTERISTICS
.)
SET SESSION CHARACTERISTICS
Syntax
SET SESSION CHARACTERISTICS AS TRANSACTION { READ ONLY | READ WRITE }
Sets the default transaction mode for transactions in the session to
READ ONLY
or READ WRITE
. This statement is permitted only when there is no
active transaction.
The SET TRANSACTION
command can override this setting.
SHOW [ VARIABLE ] SPANNER.STATEMENT_TAG
Returns a result set with one row and one column of type STRING
that contains
the request tag for the next statement. The request tag must have been
previously set by the SET SPANNER.STATEMENT_TAG
statement.
For more information, see Troubleshooting with request tags and transaction tags.
SET SPANNER.STATEMENT_TAG
Syntax
SET SPANNER.STATEMENT_TAG {TO|=} '<tag>'
Sets the request tag for the next statement to be executed. Only one
tag can be set per statement. The tag doesn't span multiple statements; it
must be set on a per statement basis. A request tag can be removed by setting it
to the empty string (''
).
For more information, see Troubleshooting with request tags and transaction tags.
SHOW [ VARIABLE ] SPANNER.TRANSACTION_TAG
Returns a result set with one row and one column of type STRING
that contains
the transaction tag for the next statement. The transaction tag must have been
previously set by the SET SPANNER.TRANSACTION_TAG
statement.
For more information, see Troubleshooting with request tags and transaction tags.
SET SPANNER.TRANSACTION_TAG
Syntax
SET SPANNER.TRANSACTION_TAG {TO|=} '<tag>'
Sets the transaction tag for the next transaction to be executed. Only one
tag can be set per transaction. The tag doesn't span multiple transactions; it
must be set on a per transaction basis. A transaction tag can be removed by
setting it to the empty string (''
).
For more information, see Troubleshooting with request tags and transaction tags.
Batch statements
The following statements manage batches of DDL statements and send those batches to Cloud Spanner.
START BATCH DDL
Starts a batch of DDL statements on the connection. All subsequent statements
during the batch must be DDL statements. The DDL statements are buffered locally
and sent to Cloud Spanner as one batch when you execute RUN BATCH
.
Executing multiple DDL statements as one batch is typically faster than running
the statements separately.
You can execute this statement only while there is no active transaction.
RUN BATCH
Sends all buffered DDL statements in the current DDL batch to the database, waits for Cloud Spanner to execute these statements, and ends the current DDL batch.
If Cloud Spanner cannot execute at least one DDL statement, RUN BATCH
returns an error for the first DDL statement that Cloud Spanner cannot
execute. Otherwise, RUN BATCH
returns successfully.
ABORT BATCH
Clears all buffered DDL statements in the current DDL batch and ends the batch.
You can execute this statement only when a DDL batch is active. You can use
ABORT BATCH
regardless of whether or not the batch has buffered DDL
statements.
START BATCH DML
The following statements batch the two DML statements together and send these in one call to the server. A DML batch can be executed as part of a transaction or in autocommit mode.
START BATCH DML
INSERT INTO MYTABLE (ID, NAME) VALUES (1, 'ONE')
INSERT INTO MYTABLE (ID, NAME) VALUES (2, 'TWO')
RUN BATCH
What's next
Learn how to use the open-source JDBC driver.