Console
    Contact Us Start free

    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" 
 ) 
 func 
  
 writeWithTransactionUsingIsolationLevel 
 ( 
 w 
  
 io 
 . 
 Writer 
 , 
  
 db 
  
 string 
 ) 
  
 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 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "failed to create client: %w" 
 , 
  
 err 
 ) 
  
 } 
  
 defer 
  
 client 
 . 
 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 
 ( 
 ctx 
  
 context 
 . 
 Context 
 , 
  
 txn 
  
 * 
 spanner 
 . 
 ReadWriteTransaction 
 ) 
  
 error 
  
 { 
  
 // Read the current album title 
  
 key 
  
 := 
  
 spanner 
 . 
  Key 
 
 { 
 1 
 , 
  
 1 
 } 
  
 row 
 , 
  
 err 
  
 := 
  
 txn 
 . 
 ReadRow 
 ( 
 ctx 
 , 
  
 "Albums" 
 , 
  
 key 
 , 
  
 [] 
 string 
 { 
 "AlbumTitle" 
 }) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "failed to read album: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 var 
  
 title 
  
 string 
  
 if 
  
 err 
  
 := 
  
 row 
 . 
  Column 
 
 ( 
 0 
 , 
  
& title 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 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 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "failed to update album: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 fmt 
 . 
 Fprintf 
 ( 
 w 
 , 
  
 "Updated %d record(s).\n" 
 , 
  
 count 
 ) 
  
 return 
  
 nil 
  
 }, 
  
 txnOpts 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "transaction failed: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 return 
  
 nil 
 }

    Java

      static 
  
 void 
  
 isolationLevelSetting 
 ( 
 DatabaseId 
  
 db 
 ) 
  
 { 
  
 // The isolation level specified at the client-level will be applied to all 
  
 // RW transactions. 
  
 DefaultReadWriteTransactionOptions 
  
 transactionOptions 
  
 = 
  
 DefaultReadWriteTransactionOptions 
 . 
 newBuilder 
 () 
  
 . 
 setIsolationLevel 
 ( 
 IsolationLevel 
 . 
 SERIALIZABLE 
 ) 
  
 . 
 build 
 (); 
  
 SpannerOptions 
  
 options 
  
 = 
  
 SpannerOptions 
 . 
 newBuilder 
 () 
  
 . 
 setDefaultTransactionOptions 
 ( 
 transactionOptions 
 ) 
  
 . 
 build 
 (); 
  
 Spanner 
  
 spanner 
  
 = 
  
 options 
 . 
 getService 
 (); 
  
 DatabaseClient 
  
 dbClient 
  
 = 
  
 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. 
  
 String 
  
 selectSql 
  
 = 
  
 "SELECT AlbumTitle from Albums WHERE SingerId = 1 and AlbumId = 1" 
 ; 
  
 ResultSet 
  
 resultSet 
  
 = 
  
 transaction 
 . 
 executeQuery 
 ( 
 Statement 
 . 
 of 
 ( 
 selectSql 
 )); 
  
 String 
  
 title 
  
 = 
  
 null 
 ; 
  
 while 
  
 ( 
 resultSet 
 . 
 next 
 ()) 
  
 { 
  
 title 
  
 = 
  
 resultSet 
 . 
 getString 
 ( 
 "AlbumTitle" 
 ); 
  
 } 
  
 System 
 . 
 out 
 . 
 printf 
 ( 
 "Current album title: %s\n" 
 , 
  
 title 
 ); 
  
 // Update the title. 
  
 String 
  
 updateSql 
  
 = 
  
 "UPDATE Albums " 
  
 + 
  
 "SET AlbumTitle = 'New Album Title' " 
  
 + 
  
 "WHERE SingerId = 1 and AlbumId = 1" 
 ; 
  
 long 
  
 rowCount 
  
 = 
  
 transaction 
 . 
 executeUpdate 
 ( 
 Statement 
 . 
 of 
 ( 
 updateSql 
 )); 
  
 System 
 . 
 out 
 . 
 printf 
 ( 
 "%d record updated.\n" 
 , 
  
 rowCount 
 ); 
  
 return 
  
 null 
 ; 
  
 }); 
 }

    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. 
 const 
  
 isolationOptionsForClient 
  
 = 
  
 { 
  
 defaultTransactionOptions 
 : 
  
 { 
  
 isolationLevel 
 : 
  
 protos 
 . 
 google 
 . 
 spanner 
 . 
 v1 
 . 
  TransactionOptions 
 
 . 
 IsolationLevel 
 . 
 SERIALIZABLE 
 , 
  
 }, 
 }; 
 // Instantiates a client with defaultTransactionOptions 
 const 
  
 spanner 
  
 = 
  
 new 
  
  Spanner 
 
 ({ 
  
 projectId 
 : 
  
 projectId 
 , 
  
 defaultTransactionOptions 
 : 
  
 isolationOptionsForClient 
 , 
 }); 
 function 
  
 runTransactionWithIsolationLevel 
 () 
  
 { 
  
 // Gets a reference to a Cloud Spanner instance and database 
  
 const 
  
 instance 
  
 = 
  
 spanner 
 . 
 instance 
 ( 
 instanceId 
 ); 
  
 const 
  
 database 
  
 = 
  
 instance 
 . 
 database 
 ( 
 databaseId 
 ); 
  
 // The isolation level specified at the request level takes precedence over the isolation level configured at the client level. 
  
 const 
  
 isolationOptionsForTransaction 
  
 = 
  
 { 
  
 isolationLevel 
 : 
  
 protos 
 . 
 google 
 . 
 spanner 
 . 
 v1 
 . 
  TransactionOptions 
 
 . 
 IsolationLevel 
  
 . 
 REPEATABLE_READ 
 , 
  
 }; 
  
 database 
 . 
  runTransaction 
 
 ( 
  
 isolationOptionsForTransaction 
 , 
  
 async 
  
 ( 
 err 
 , 
  
 transaction 
 ) 
  
 = 
>  
 { 
  
 if 
  
 ( 
 err 
 ) 
  
 { 
  
 console 
 . 
 error 
 ( 
 err 
 ); 
  
 return 
 ; 
  
 } 
  
 try 
  
 { 
  
 const 
  
 query 
  
 = 
  
 'SELECT AlbumTitle FROM Albums WHERE SingerId = 1 AND AlbumId = 1' 
 ; 
  
 const 
  
 results 
  
 = 
  
 await 
  
 transaction 
 . 
 run 
 ( 
 query 
 ); 
  
 // Gets first album's title 
  
 const 
  
 rows 
  
 = 
  
 results 
 [ 
 0 
 ]. 
 map 
 ( 
 row 
  
 = 
>  
 row 
 . 
 toJSON 
 ()); 
  
 const 
  
 albumTitle 
  
 = 
  
 rows 
 [ 
 0 
 ]. 
 AlbumTitle 
 ; 
  
 console 
 . 
 log 
 ( 
 `previous album title 
 ${ 
 albumTitle 
 } 
 ` 
 ); 
  
 const 
  
 update 
  
 = 
  
 "UPDATE Albums SET AlbumTitle = 'New Album Title' WHERE SingerId = 1 AND AlbumId = 1" 
 ; 
  
 const 
  
 [ 
 rowCount 
 ] 
  
 = 
  
 await 
  
 transaction 
 . 
  runUpdate 
 
 ( 
 update 
 ); 
  
 console 
 . 
 log 
 ( 
  
 `Successfully updated 
 ${ 
 rowCount 
 } 
 record in Albums table.` 
 , 
  
 ); 
  
 await 
  
 transaction 
 . 
 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. 
  
 await 
  
 database 
 . 
 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 
 ) 
 def 
  
 update_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
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: