Use SELECT FOR UPDATE in serializable isolation

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

The locking mechanism of the FOR UPDATE clause is different for repeatable read and serializable isolation. Using serializable isolation, when you use the SELECT query to scan a table, adding a FOR UPDATE clause enables exclusive locks at the intersection of the row-and-column granularity level, otherwise known as cell-level. The lock remains in place for the lifetime of the read-write transaction. During this time, the FOR UPDATE clause prevents other transactions from modifying the locked cells until the current transaction completes.

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

Why use the `FOR UPDATE` clause

In databases with less strict isolation levels, the FOR UPDATE clause might be necessary to ensure that a concurrent transaction doesn't update data between reading the data and committing the transaction. Since Spanner enforces serializability by default, it's guaranteed that the transaction only commits successfully if the data accessed within the transaction isn't stale at commit time. Therefore, the FOR UPDATE clause isn't necessary to ensure transaction correctness in Spanner.

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

A transaction that uses the FOR UPDATE clause acquires the exclusive lock proactively and proceeds to execute, while other transactions wait their turn for the lock. Although Spanner might still limit throughput because the conflicting transactions can only be performed one at a time, but because Spanner is only making progress on one transaction, it saves time that would otherwise be spent aborting and retrying transactions.

Therefore, if reducing the number of aborted transactions in a simultaneous write request scenario is important, then you can use the FOR UPDATE clause to reduce the overall number of aborts and increase workload execution efficiency.

Comparison to the `LOCK_SCANNED_RANGES` hint

The FOR UPDATE clause serves a similar function as the LOCK_SCANNED_RANGES=exclusive hint.

There are two key differences:

If you use the LOCK_SCANNED_RANGES hint, the transaction acquires exclusive locks on the scanned ranges for the entire statement. You can't acquire exclusive locks on a subquery. Using the lock hint might result in acquiring more 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}
SELECT s.SingerId, s.FullName FROM Singers AS s
JOIN (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
AS a ON a.SingerId = s.SingerId;
```
On the other hand, you can use the FOR UPDATE clause in a subquery as shown in the following example:
```
SELECT s.SingerId, s.FullName FROM Singers AS s
JOIN (SELECT SingerId FROM Albums WHERE MarketingBudget > 100000)
FOR UPDATE AS a ON a.SingerId = s.SingerId;
```
You can use the LOCK_SCANNED_RANGES hint in DML statements whereas you can only use the FOR UPDATE clause in SELECT statements.

Lock semantics

To reduce simultaneous write requests and the cost of transactions being aborted as a result of deadlock, Spanner locks data at the cell-level if possible. The cell-level is the most granular level of data within a table - a data point at the intersection of a row and a column. When using the FOR UPDATE clause, Spanner locks specific cells that are scanned by the SELECT query.

In the following example, the MarketingBudget cell in the SingerId = 1 and AlbumId = 1 row is exclusively locked in the Albums table, preventing concurrent transactions from modifying that cell until this transaction is committed or rolled back. However, concurrent transactions can still update the AlbumTitle cell in that row.

SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1
FOR UPDATE;

Concurrent transactions might block on reading locked data

When one transaction has acquired exclusive locks on a scanned range, concurrent transactions might block reading that data. Spanner enforces serializability so data can only be read if it is guaranteed to be unchanged by another transaction within the lifetime of the transaction. Concurrent transactions that attempt to read already locked data might have to wait until the transaction holding the locks is committed, rolled back, or times out.

In the following example, Transaction 1 locks the MarketingBudget cells for 1 <= AlbumId < 5.

-- Transaction 1
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 1 and AlbumId < 5
FOR UPDATE;

Transaction 2, which is attempting to read the MarketingBudget for AlbumId = 1, is blocked until Transaction 1 either commits or is rolled back.

-- Transaction 2
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId = 1;

-- Blocked by Transaction 1

Similarly, a transaction attempting to lock a scanned range with FOR UPDATE is blocked by a concurrent transaction that locks an overlapping scanned range.

Transaction 3 in the following example is also blocked since Transaction 1 has locked the MarketingBudget cells for 3 <= AlbumId < 5, which is the overlapping scanned range with Transaction 3.

-- Transaction 3
SELECT MarketingBudget
FROM Albums
WHERE SingerId = 1 and AlbumId >= 3 and AlbumId < 10
FOR UPDATE;

-- Blocked by Transaction 1

Read an index

A concurrent read might not be blocked if the query that locked the scanned range locks the rows in the base table, but the concurrent transaction reads from an index.

The following Transaction 1 locks the SingerId and SingerInfo cells for SingerId = 1.

-- Transaction 1
SELECT SingerId, SingerInfo
FROM Singers
WHERE SingerId = 1
FOR