Use SELECT FOR UPDATE in repeatable read isolation

This page describes how to use the FOR UPDATE clause in repeatable read isolation .

The locking mechanism of the FOR UPDATE clause is different for repeatable read and serializable isolation. Unlike in serializable isolation, the FOR UPDATE clause doesn't acquire locks in repeatable read isolation. For more information about locks in FOR UPDATE , see Use SELECT FOR UPDATE in serializable isolation .

To learn how to use the FOR UPDATE clause, see the GoogleSQL and PostgreSQL FOR UPDATE reference guides.

Why use the `FOR UPDATE` clause

When a transaction runs with repeatable read isolation, the data queried by the SELECT statement is always returned at the established snapshot timestamp for the transaction. If the transaction then makes updates based on the data that is queried, there might be correctness issues if a concurrent transaction also updates the queried data. For more information, see Read-write conflicts and correctness .

To ensure that data queried by the SELECT statement is still valid when the transaction commits, you can use a FOR UPDATE clause with repeatable read isolation. Using FOR UPDATE guarantees transaction correctness despite read-write conflicts where data might have been modified by another transaction between the time it was read and modified.

Query syntax

This section provides guidance on query syntax when using the FOR UPDATE clause.

The most common usage is in a top-level SELECT statement. For example:

  SELECT 
  
 SingerId 
 , 
  
 SingerInfo 
 FROM 
  
 Singers 
  
 WHERE 
  
 SingerID 
  
 = 
  
 5 
 FOR 
  
 UPDATE 
 ;

The FOR UPDATE clause ensures that the data queried by the SELECT statement and SingerID = 5 is still valid when the transaction commits, preventing correctness issues that might arise if a concurrent transaction updates the queried data.

Use in WITH statements

The FOR UPDATE clause doesn't verify the ranges scanned within the WITH statement when you specify FOR UPDATE in the outer-level query of the WITH statement.

In the following query, no scanned ranges are validated because the FOR UPDATE isn't propagated to the common table expressions (CTE) query.

  WITH 
  
 s 
  
 AS 
  
 ( 
 SELECT 
  
 SingerId 
 , 
  
 SingerInfo 
  
 FROM 
  
 Singers 
  
 WHERE 
  
 SingerID 
 > 
 5 
 ) 
 SELECT 
  
 * 
  
 FROM 
  
 s 
 FOR 
  
 UPDATE 
 ;

If the FOR UPDATE clause is specified in the CTE query, then the scanned range of the CTE query is validated.

In the following example, the SingerId and SingerInfo cells for the rows where SingerId > 5 are validated.

  WITH 
  
 s 
  
 AS 
  
 ( 
 SELECT 
  
 SingerId 
 , 
  
 SingerInfo 
  
 FROM 
  
 Singers 
  
 WHERE 
  
 SingerId 
 > 
 5 
  
 FOR 
  
 UPDATE 
 ) 
 SELECT 
  
 * 
  
 FROM 
  
 s 
 ;

Use in subqueries

You can use the FOR UPDATE clause in an outer-level query that has one or more subqueries. Ranges scanned by the top-level query and within subqueries are validated, except in expression subqueries .

The following query validates the SingerId and SingerInfo cells for rows where SingerId > 5.

  ( 
 SELECT 
  
 SingerId 
 , 
  
 SingerInfo 
  
 FROM 
  
 Singers 
  
 WHERE 
  
 SingerId 
 > 
 5 
 ) 
  
 AS 
  
 t 
 FOR 
  
 UPDATE 
 ;

The following query doesn't validate any cells in the Albums table because it's within an expression subquery. The SingerId and SingerInfo cells for the rows returned by the expression subquery are validated.

  SELECT 
  
 SingerId 
 , 
  
 SingerInfo 
 FROM 
  
 Singers 
 WHERE 
  
 SingerId 
  
 = 
  
 ( 
 SELECT 
  
 SingerId 
  
 FROM 
  
 Albums 
  
 WHERE 
  
 MarketingBudget 
 > 
 100000 
 ) 
 FOR 
  
 UPDATE 
 ;

Use to query views

You can use the FOR UPDATE clause to query a view as shown in the following example:

  CREATE 
  
 VIEW 
  
 SingerBio 
  
 AS 
  
 SELECT 
  
 SingerId 
 , 
  
 FullName 
 , 
  
 SingerInfo 
  
 FROM 
  
 Singers 
 ; 
 SELECT 
  
 * 
  
 FROM 
  
 SingerBio 
  
 WHERE 
  
 SingerId 
  
 = 
  
 5 
  
 FOR 
  
 UPDATE 
 ;

You can't use the FOR UPDATE clause when defining a view .

Unsupported use cases

The following FOR UPDATE use cases are unsupported:

As a mutual exclusion mechanism for running code outside of Spanner:Don't use locking in Spanner to ensure exclusive access to a resource outside of Spanner. Transactions might be aborted by Spanner, for example, if a transaction is retried, whether explicitly by application code or implicitly by client code, such as the Spanner JDBC driver , it's only guaranteed that the locks are held during the attempt that was committed.
In combination with the LOCK_SCANNED_RANGES hint :You can't use both the FOR UPDATE clause and the LOCK_SCANNED_RANGES hint in the same query, or Spanner returns an error. For more information, see Comparison to the LOCK_SCANNED_RANGES hint .
In full-text search queries:You can't use the FOR UPDATE clause in queries using full-text search indexes.
In read-only transactions:The FOR UPDATE clause is only valid in queries executing within read-write transactions.
Within DDL statements:You can't use the FOR UPDATE clause in queries within DDL statements, which are stored for later execution. For example, you can't use the FOR UPDATE clause when defining a view .

What's Next

Learn how to use the FOR UPDATE clause in GoogleSQL and PostgreSQL .
Learn how to Use SELECT FOR UPDATE in serializable isolation .
Learn about the LOCK_SCANNED_RANGES hint .
Learn about Locking in Spanner.