This page describes how to use theFOR UPDATE clause in serializableisolation.

The locking mechanism of theFOR UPDATE clause is different forrepeatable read andserializable isolation. Usingserializable isolation, when you use theSELECT query to scan a table, addingaFOR UPDATE clause enables exclusive locks at the intersection of therow-and-column granularity level, otherwise known as cell-level. The lockremains in place for the lifetime of the read-write transaction. During thistime, theFOR UPDATE clause prevents other transactions from modifying thelocked cells until the current transaction completes.

To learn how to use theFOR UPDATE clause, see theGoogleSQLandPostgreSQLFOR UPDATE reference guides.

Why use theFOR UPDATE clause

In databases with less strict isolation levels, theFOR UPDATE clause might benecessary to ensure that a concurrent transaction doesn't update data betweenreading the data and committing the transaction. Since Spannerenforces serializability by default, it's guaranteed that the transaction onlycommits successfully if the data accessed within the transaction isn't stale atcommit time. Therefore, theFOR UPDATE clause isn't necessary to ensuretransaction correctness in Spanner.

However, in use cases with high write contention, such as when multipletransactions are concurrently reading and writing to the same data, thesimultaneous transactions might cause an increase in aborts. This is becausewhen multiple, simultaneous transactions acquire shared locks, and then try toupgrade to exclusive locks, the transactions cause a deadlock. The deadlockpermanently blocks the transactions because each is waiting for the other torelease the resource that it needs. In order to make progress,Spanner aborts all but one of the transactions to resolve thedeadlock. For more information, seeLocking.

A transaction that uses theFOR UPDATE clause acquires the exclusive lock proactively and proceeds to execute, while other transactions wait their turnfor the lock. Although Spanner might still limit throughputbecause the conflicting transactions can only be performed one at a time, butbecause Spanner is only making progress on one transaction, itsaves time that would otherwise be spent aborting and retrying transactions.

Therefore, if reducing the number of aborted transactions in a simultaneouswrite request scenario is important, then you can use theFOR UPDATE clause toreduce the overall number of aborts and increase workload execution efficiency.

Comparison to theLOCK_SCANNED_RANGES hint

TheFOR UPDATE clause serves a similar function as theLOCK_SCANNED_RANGES=exclusivehint.

There are two key differences:

• If you use theLOCK_SCANNED_RANGES hint, the transaction acquires exclusivelocks on the scanned ranges for the entire statement. You can't acquireexclusive locks on a subquery. Using the lock hint might result in acquiringmore locks than necessary and contributing to lock contention in the workload.The following example shows how to use a lock hint:

  @{lock_scanned_ranges=exclusive}SELECTs.SingerId,s.FullNameFROMSingersASsJOIN(SELECTSingerIdFROMAlbumsWHEREMarketingBudget >100000)ASaONa.SingerId=s.SingerId;

  On the other hand, you can use theFOR UPDATE clause in a subquery as shownin the following example:

  SELECTs.SingerId,s.FullNameFROMSingersASsJOIN(SELECTSingerIdFROMAlbumsWHEREMarketingBudget >100000)FORUPDATEASaONa.SingerId=s.SingerId;

• You can use theLOCK_SCANNED_RANGES hint in DML statements whereas you canonly use theFOR UPDATE clause inSELECT statements.

Lock semantics

To reduce simultaneous write requests and the cost of transactions being abortedas a result of deadlock, Spanner locks data at the cell-level ifpossible. The cell-level is the most granular level of data within a table - adata point at the intersection of a row and a column. When using theFOR UPDATE clause, Spanner locks specific cells that arescanned by theSELECT query.

In the following example, theMarketingBudget cell in theSingerId = 1 andAlbumId = 1 row is exclusively locked in theAlbums table, preventingconcurrent transactions from modifying that cell until this transaction iscommitted or rolled back. However, concurrent transactions can still update theAlbumTitle cell in that row.

SELECTMarketingBudgetFROMAlbumsWHERESingerId=1andAlbumId=1FORUPDATE;

Concurrent transactions might block on reading locked data

When one transaction has acquired exclusive locks on a scanned range, concurrenttransactions might block reading that data. Spanner enforcesserializabilityso data can only be read if it is guaranteed to be unchanged by anothertransaction within the lifetime of the transaction. Concurrent transactions thatattempt to read already locked data might have to wait until the transactionholding the locks is committed, rolled back, or times out.

In the following example,Transaction 1 locks theMarketingBudget cells for1 <= AlbumId < 5.

-- Transaction 1SELECTMarketingBudgetFROMAlbumsWHERESingerId=1andAlbumId>=1andAlbumId <5FORUPDATE;

Transaction 2, which is attempting to read theMarketingBudget forAlbumId = 1, is blocked untilTransaction 1 either commits or is rolledback.

-- Transaction 2SELECTMarketingBudgetFROMAlbumsWHERESingerId=1andAlbumId=1;-- Blocked by Transaction 1

Similarly, a transaction attempting to lock a scanned range withFOR UPDATE isblocked by a concurrent transaction that locks an overlapping scanned range.

Transaction 3 in the following example is also blocked sinceTransaction 1has locked theMarketingBudget cells for3 <= AlbumId < 5, which is theoverlapping scanned range withTransaction 3.

-- Transaction 3SELECTMarketingBudgetFROMAlbumsWHERESingerId=1andAlbumId>=3andAlbumId <10FORUPDATE;-- Blocked by Transaction 1

Read an index

A concurrent read might not be blocked if the query that locked the scannedrange locks the rows in the base table, but the concurrent transaction readsfrom an index.

The followingTransaction 1 locks theSingerId andSingerInfo cells forSingerId = 1.

-- Transaction 1SELECTSingerId,SingerInfoFROMSingersWHERESingerId=1FORUPDATE;

The read-onlyTransaction 2 isn't blocked by the locks acquired inTransaction 1, because it queries an index table.

-- Transaction 2SELECTSingerIdFROMSingers;

Concurrent transactions doesn't block DML operations on already locked data

When one transaction has acquired locks on a range of cells with an exclusivelock hint, concurrent transactions attempting to perform a write withoutreading the data first on the locked cells can proceed. The transaction blockson the commit until the transaction holding the locks commits or rolls back.

The followingTransaction 1 locks theMarketingBudget cells for1 <= AlbumId < 5.

-- Transaction 1SELECTMarketingBudgetFROMAlbumsWHERESingerId=1andAlbumId>=1andAlbumId <5FORUPDATE;

IfTransaction 2 attempts to update theAlbums table, it is blocked fromdoing so untilTransaction 1 commits or rolls back.

-- Transaction 2UPDATEAlbumsSETMarketingBudget=200000WHERESingerId=1andAlbumId=1;>QueryOK,1rowsaffectedCOMMIT;-- Blocked by Transaction 1

Existing rows and gaps are locked when a scanned range is locked

When one transaction has acquired exclusive locks on a scanned range, concurrenttransactions can't insert data in the gaps within that range.

The followingTransaction 1 locks theMarketingBudget cells for1 <= AlbumId < 10.

-- Transaction 1SELECTMarketingBudgetFROMAlbumsWHERESingerId=1andAlbumId>=1andAlbumId <10FORUPDATE;

IfTransaction 2 attempts to insert a row forAlbumId = 9 that doesn't existyet, it is blocked from doing so untilTransaction 1 commits or rolls back.

-- Transaction 2INSERTINTOAlbums(SingerId,AlbumId,AlbumTitle,MarketingBudget)VALUES(1,9,"Hello hello!",10000);>QueryOK,1rowsaffectedCOMMIT;-- Blocked by Transaction 1

Lock acquisition caveats

The lock semantics described provide general guidance but aren't a guaranteeon exactly how locks might be acquired when Spanner runs atransaction that uses theFOR UPDATE clause. Spanner's queryoptimization mechanisms might also affect which locks are acquired. The clauseprevents other transactions from modifying the locked cells until the currenttransaction completes.

Query syntax

This section provides guidance on query syntax when using theFOR UPDATEclause.

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

SELECTSingerId,SingerInfoFROMSingersWHERESingerID=5FORUPDATE;

This sample demonstrates how to use theFOR UPDATE clause in aSELECTstatement to exclusively lock theSingerId andSingerInfo cells ofWHERE SingerID = 5.

Use in WITH statements

TheFOR UPDATE clause doesn't acquire locks for theWITH statement whenyou specifyFOR UPDATE in the outer-level query of theWITH statement.

In the following query, no locks are acquired by theSingers table, becausethe intent to lock isn't propagated to the common table expressions (CTE) query.

WITHsAS(SELECTSingerId,SingerInfoFROMSingersWHERESingerID >5)SELECT*FROMsFORUPDATE;

If theFOR UPDATE clause is specified in the CTE query, the scanned range ofthe CTE query acquires the locks.

In the following example, theSingerId andSingerInfo cells for the rowswhereSingerId > 5 are locked.

WITHsAS(SELECTSingerId,SingerInfoFROMSingersWHERESingerId >5FORUPDATE)SELECT*FROMs;

Use in subqueries

You can use theFOR UPDATE clause in an outer-level query that has one or moresubqueries. Locks are acquired by the top-level query and within subqueries,except inexpression subqueries.

The following query locks theSingerId andSingerInfo cells for rows whereSingerId > 5.

(SELECTSingerId,SingerInfoFROMSingersWHERESingerId >5)AStFORUPDATE;

The following query doesn't lock any cells in theAlbums table because it iswithin an expression subquery. TheSingerId andSingerInfo cells for therows returned by the expression subquery are locked.

SELECTSingerId,SingerInfoFROMSingersWHERESingerId=(SELECTSingerIdFROMAlbumsWHEREMarketingBudget >100000)FORUPDATE;

Use to query views

You can use theFOR UPDATE clause to query a view as shown in the followingexample:

CREATEVIEWSingerBioASSELECTSingerId,FullName,SingerInfoFROMSingers;SELECT*FROMSingerBioWHERESingerId=5FORUPDATE;

You can't use theFOR UPDATE clausewhen defining a view.

Unsupported use cases

The followingFOR 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 accessto a resource outside of Spanner. Transactions might be abortedby Spanner, for example, if a transaction is retried, whetherexplicitly by application code or implicitly by client code, such as theSpanner JDBC driver, it's onlyguaranteed that the locks are held during the attempt that was committed.
• In combination with theLOCK_SCANNED_RANGES hint: You can't use both theFOR UPDATE clause and theLOCK_SCANNED_RANGES hint in the same query, orSpanner returns an error.
• In full-text search queries: You can't use theFOR UPDATE clause inqueries usingfull-text search indexes.
• In read-only transactions: TheFOR UPDATE clause is only valid inqueries running within read-write transactions.
• Within DDL statements: You can't use theFOR UPDATE clause in querieswithin DDL statements, which are stored for later execution. For example, youcan't use theFOR UPDATE clausewhen defining a view.If locking is required, theFOR UPDATE clause can be specified when queryingthe view.

What's Next

• Learn how to use theFOR UPDATE clause inGoogleSQLandPostgreSQL.
• Learn how toUse SELECT FOR UPDATE in repeatable read isolation.
• Learn about theLOCK_SCANNED_RANGES hint.
• Learn aboutLocking inSpanner.
• Learn about Spannerserializability.