Use repeatable read isolation

This page describes how to use repeatable read isolation in Spanner.

Repeatable read is an isolation level that ensures that all read operations within a transaction see a consistent snapshot of the database as it existed at the start of the transaction. In Spanner, this isolation level is implemented using a technique that is also commonly called snapshot isolation. This approach is beneficial in high read-write concurrency scenarios where numerous transactions read data that other transactions might be modifying. By using a fixed snapshot, repeatable read avoids the performance impacts of the more rigorous serializable isolation level. Reads can execute without acquiring locks and without blocking concurrent writes, which results in potentially fewer aborted transactions that might need to be retried due to serialization conflicts. For more information, see Isolation level overview.

Set the isolation level

You can set the isolation level on read-write transactions at the transaction-level using the following methods:

Client libraries

Go


import(
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
pb"cloud.google.com/go/spanner/apiv1/spannerpb"
)
funcwriteWithTransactionUsingIsolationLevel(wio.Writer,dbstring)error{
ctx:=context.Background()
// The isolation level specified at the client-level will be applied
// to all RW transactions.
cfg:=spanner.ClientConfig {
TransactionOptions:spanner.TransactionOptions{
IsolationLevel:pb.TransactionOptions_SERIALIZABLE,
},
}
client,err:=spanner.NewClientWithConfig (ctx,db,cfg)
iferr!=nil{
returnfmt.Errorf("failed to create client: %w",err)
}
deferclient.Close()
// The isolation level specified at the transaction-level takes
// precedence over the isolation level configured at the client-level.
// REPEATABLE_READ is used here to demonstrate overriding the client-level setting.
txnOpts:=spanner.TransactionOptions{
IsolationLevel:pb.TransactionOptions_REPEATABLE_READ,
}
_,err=client.ReadWriteTransactionWithOptions (ctx,func(ctxcontext.Context,txn*spanner.ReadWriteTransaction)error{
// Read the current album title
key:=spanner.Key {1,1}
row,err:=txn.ReadRow(ctx,"Albums",key,[]string{"AlbumTitle"})
iferr!=nil{
returnfmt.Errorf("failed to read album: %v",err)
}
vartitlestring
iferr:=row.Column (0,&title);err!=nil{
returnfmt.Errorf("failed to get album title: %v",err)
}
fmt.Fprintf(w,"Current album title: %s\n",title)
// Update the album title
stmt:=spanner.Statement {
SQL:`UPDATE Albums
				SET AlbumTitle = @AlbumTitle
				WHERE SingerId = @SingerId AND AlbumId = @AlbumId`,
Params:map[string]interface{}{
"SingerId":1,
"AlbumId":1,
"AlbumTitle":"New Album Title",
},
}
count,err:=txn.Update(ctx,stmt)
iferr!=nil{
returnfmt.Errorf("failed to update album: %v",err)
}
fmt.Fprintf(w,"Updated %d record(s).\n",count)
returnnil
},txnOpts)
iferr!=nil{
returnfmt.Errorf("transaction failed: %v",err)
}
returnnil
}

Java

staticvoidisolationLevelSetting(DatabaseIddb){
// The isolation level specified at the client-level will be applied to all
// RW transactions.
DefaultReadWriteTransactionOptionstransactionOptions=
DefaultReadWriteTransactionOptions.newBuilder()
.setIsolationLevel(IsolationLevel.SERIALIZABLE)
.build();
SpannerOptionsoptions=
SpannerOptions.newBuilder()
.setDefaultTransactionOptions(transactionOptions)
.build();
Spannerspanner=options.getService();
DatabaseClientdbClient=spanner.getDatabaseClient(db);
dbClient
// The isolation level specified at the transaction-level takes precedence
// over the isolation level configured at the client-level.
.readWriteTransaction(Options.isolationLevel(IsolationLevel.REPEATABLE_READ))
.run(transaction->{
// Read an AlbumTitle.
StringselectSql=
"SELECT AlbumTitle from Albums WHERE SingerId = 1 and AlbumId = 1";
ResultSetresultSet=transaction.executeQuery(Statement.of(selectSql));
Stringtitle=null;
while(resultSet.next()){
title=resultSet.getString("AlbumTitle");
}
System.out.printf("Current album title: %s\n",title);
// Update the title.
StringupdateSql=
"UPDATE Albums "
+"SET AlbumTitle = 'New Album Title' "
+"WHERE SingerId = 1 and AlbumId = 1";
longrowCount=transaction.executeUpdate(Statement.of(updateSql));
System.out.printf("%d record updated.\n",rowCount);
returnnull;
});
}

Node.js

// Imports the Google Cloud Spanner client library
const{Spanner,protos}=require('@google-cloud/spanner');
// The isolation level specified at the client-level will be applied
// to all RW transactions.
constisolationOptionsForClient={
defaultTransactionOptions:{
isolationLevel:
protos.google.spanner.v1.TransactionOptions .IsolationLevel.SERIALIZABLE,
},
};
// Instantiates a client with defaultTransactionOptions
constspanner=newSpanner ({
projectId:projectId,
defaultTransactionOptions:isolationOptionsForClient,
});
functionrunTransactionWithIsolationLevel(){
// Gets a reference to a Cloud Spanner instance and database
constinstance=spanner.instance(instanceId);
constdatabase=instance.database(databaseId);
// The isolation level specified at the request level takes precedence over the isolation level configured at the client level.
constisolationOptionsForTransaction={
isolationLevel:
protos.google.spanner.v1.TransactionOptions .IsolationLevel
.REPEATABLE_READ,
};
database.runTransaction (
isolationOptionsForTransaction,
async(err,transaction)=>{
if(err){
console.error(err);
return;
}
try{
constquery=
'SELECT AlbumTitle FROM Albums WHERE SingerId = 1 AND AlbumId = 1';
constresults=awaittransaction.run(query);
// Gets first album's title
constrows=results[0].map(row=>row.toJSON());
constalbumTitle=rows[0].AlbumTitle;
console.log(`previous album title ${albumTitle}`);
constupdate=
"UPDATE Albums SET AlbumTitle = 'New Album Title' WHERE SingerId = 1 AND AlbumId = 1";
const[rowCount]=awaittransaction.runUpdate (update);
console.log(
`Successfully updated ${rowCount} record in Albums table.`,
);
awaittransaction.commit();
console.log(
'Successfully executed read-write transaction with isolationLevel option.',
);
}catch(err){
console.error('ERROR:',err);
}finally{
transaction.end();
// Close the database when finished.
awaitdatabase.close();
}
},
);
}
runTransactionWithIsolationLevel();

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
# The isolation level specified at the client-level will be applied to all RW transactions.
isolation_options_for_client = TransactionOptions.IsolationLevel.SERIALIZABLE
spanner_client = spanner.Client(
 default_transaction_options=DefaultTransactionOptions(
 isolation_level=isolation_options_for_client
 )
)
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
# The isolation level specified at the request level takes precedence over the isolation level configured at the client level.
isolation_options_for_transaction = (
 TransactionOptions.IsolationLevel.REPEATABLE_READ
)
defupdate_albums_with_isolation(transaction):
 # Read an AlbumTitle.
 results = transaction.execute_sql(
 "SELECT AlbumTitle from Albums WHERE SingerId = 1 and AlbumId = 1"
 )
 for result in results:
 print("Current Album Title: {}".format(*result))
 # Update the AlbumTitle.
 row_ct = transaction.execute_update(
 "UPDATE Albums SET AlbumTitle = 'A New Title' WHERE SingerId = 1 and AlbumId = 1"
 )
 print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(
 update_albums_with_isolation, isolation_level=isolation_options_for_transaction
)

REST

You can use the TransactionOptions.isolation_level REST API to set the isolation level on read-write transactions at the transaction-level. The valid options are TransactionOptions.SERIALIZABLE and TransactionOptions.REPEATABLE_READ. By default, Spanner sets the isolation level to serializable isolation.

RPC

Unsupported use cases

You can only use repeatable read isolation in read-write transactions. Spanner returns an error if you set this isolation level on a read-only transaction. All read-only transactions operate at a fixed snapshot and don't require locks.
You can't use repeatable read isolation to modify tables that has full-text search indexes defined on its columns.

What's next

Learn more about isolation levels.
Learn how to use SELECT FOR UPDATE in repeatable read isolation.
Learn more about Spanner serializability and external consistency, see TrueTime and external consistency.