This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This page introduces different isolation levels and explains how they work inSpanner.

Isolation level is a database property that defines which data is visible toconcurrent transactions. Spanner supports two of the isolationlevels defined in the ANSI/ISO SQL standard:serializable andrepeatable read. When you create a transaction, you need to choose themost appropriate isolation level for the transaction. The chosen isolation levellets individual transactions prioritize various factors such as latency,abort rate, and whether the application is susceptible to the effects of dataanomalies. The best choice depends on the specific demands of the workload.

Serializable isolation

Serializable isolation is the default isolation level in Spanner.Under serializable isolation, Spanner provides you with thestrictest concurrency-control guarantees for transactions, which is calledexternal consistency.Spanner behaves as if all transactions were executedsequentially, even though Spanner actually runs them acrossmultiple servers (and possibly in multiple datacenters) for higher performanceand availability than single server databases. In addition, if one transactioncompletes before another transaction starts to commit, Spannerguarantees that clients always see the results of transactions in sequentialorder. Intuitively, Spanner is similar to a single-machinedatabase.

The trade-off is that Spanner might abort transactions if aworkload has high read-write contention, where many transactions read data thatanother transaction is updating, due to the fundamental nature of serializabletransactions. However, this is a good default for an operationaldatabase. It helps you to avoid tricky timing issues that usually only arisewith high concurrency. These issues are difficult to reproduce and troubleshoot.Therefore, serializable isolation provides the strongest protection againstdata anomalies. If a transaction needs to be retried, there might be an increasein latency due to transaction retries.

Repeatable read isolation

In Spanner, repeatable read isolation is implemented using atechnique commonly known as snapshot isolation. Repeatable read isolation inSpanner ensures that all read operations within a transaction seea consistent, or strong, snapshot of the database as it existed at the start ofthe transaction. It also guarantees that concurrent writes on the same data onlysucceed if there are no conflicts. This approach is beneficial in highread-write conflict scenarios where numerous transactions read data that othertransactions might be modifying. By using a fixed snapshot, repeatable readavoids the performance impacts of the more restrictive serializable isolationlevel. Reads can execute without acquiring locks and without blocking concurrentwrites, which results in fewer aborted transactions that might need to beretried due to potential serialization conflicts. In use cases where yourclients already run everything in a read-write transaction, and it is difficultto redesign and use read-only transactions, you can use repeatable readisolation to improve the latency of your workloads.

Unlike serializable isolation, repeatable read might lead to data anomalies ifyour application relies on specific data relationships or constraints thataren't enforced by the database schema, especially when the order of operationsmatter. In such cases, a transaction might read data, make decisions based onthat data, and then write changes that violate those application-specificconstraints, even if the database schema constraints are still met. This happensbecause repeatable read isolation allows concurrent transactions to proceedwithout strict serialization. One potential anomaly is known as awrite skew,which arises from a particular kind of concurrent update, where each update isindependently accepted, but their combined effect violates application dataintegrity. For example, imagine there's a hospital system where at least onedoctor needs to be on-call at all times, and doctors can request to be takenoff-call for a shift. Under repeatable read isolation, if both Dr. Richards andDr. Smith are scheduled to be on-call for the same shift and concurrently try torequest to be taken off-call, each request succeeds in parallel. This is becauseboth transactions read that there is at least one other doctor who is scheduledto be on-call at the start of the transaction, causing data anomaly if thetransactions succeed. On the other hand, using serializable isolation preventsthese transactions from violating the constraint because serializabletransactions will detect potential data anomalies and abort the transaction.Thereby ensuring application consistency by accepting higher abort rates.

In the previous example, you canuse theSELECT FOR UPDATE clause in repeatable read isolation.TheSELECT…FOR UPDATE clause verifies if the data it read at the chosensnapshot remains unchanged at commit time. Similarly,DML statementsandmutations, that read data internally toensure the integrity of the writes, also verify that the data remains unchangedat commit time.

For more information, seeUse repeatable read isolation.

Example use case

The following example demonstrates the benefit of using repeatable readisolation to eliminate locking overhead. BothTransaction 1 andTransaction 2 run in repeatable read isolation.

Transaction 1 establishes a snapshot timestamp when theSELECT statementruns.

-- Transaction 1BEGIN;-- Snapshot established at T1SELECTAlbumId,MarketingBudgetFROMAlbumsWHERESingerId=1;/*-----------+------------------*| AlbumId    | MarketingBudget  |+------------+------------------+| 1          | 50000            || 2          | 100000           || 3          | 70000            || 4          | 80000            |*------------+------------------*/

-- Transaction 1BEGIN;-- Snapshot established at T1SELECTalbumid,marketingbudgetFROMalbumsWHEREsingerid=1;/*-----------+------------------*| albumid    | marketingbudget  |+------------+------------------+| 1          | 50000            || 2          | 100000           || 3          | 70000            || 4          | 80000            |*------------+------------------*/

Then,Transaction 2 establishes a snapshot timestamp afterTransaction 1begins but before it commits. SinceTransaction 1 hasn't updated the data, theSELECT query inTransaction 2 reads the same data asTransaction 1.

-- Transaction 2BEGIN;-- Snapshot established at T2 > T1SELECTAlbumId,MarketingBudgetFROMAlbumsWHERESingerId=1;INSERTINTOAlbums(SingerId,AlbumId,MarketingBudget)VALUES(1,5,50000);COMMIT;

-- Transaction 2BEGIN;-- Snapshot established at T2 > T1SELECTalbumid,marketingbudgetFROMalbumsWHEREsingerid=1;INSERTINTOalbums(singerid,albumid,marketingbudget)VALUES(1,5,50000);COMMIT;

Transaction 1 continues afterTransaction 2 has committed.

-- Transaction 1 continuesSELECTSUM(MarketingBudget)asUsedBudgetFROMAlbumsWHERESingerId=1;/*-----------*| UsedBudget |+------------+| 300000     |*------------*/

-- Transaction 1 continuesSELECTSUM(marketingbudget)ASusedbudgetFROMalbumsWHEREsingerid=1;/*-----------*| usedbudget |+------------+| 300000     |*------------*/

TheUsedBudget value that Spanner returns is the sum of thebudget read byTransaction 1. This sum reflects only the data present at theT1 snapshot. It doesn't include the budget thatTransaction 2 added,becauseTransaction 2 committed afterTransaction 1 established snapshotT1. Using repeatable read means thatTransaction 1 didn't have to abort eventhoughTransaction 2 modified the data read byTransaction 1. However,the result Spanner returns might or might not be the intendedoutcome.

Read-write conflicts and correctness

In the previous example, if the data queried by theSELECT statements inTransaction 1 was used to make subsequent marketing budget decisions, theremight be correctness issues.

For example, assume there is a total budget of400,000. Based on the resultfrom theSELECT statement inTransaction 1, we might think there is100,000 left in the budget and decide to allocate it all toAlbumId = 4.

-- Transaction 1 continues..UPDATEAlbumsSETMarketingBudget=MarketingBudget+100000WHERESingerId=1ANDAlbumId=4;COMMIT;

-- Transaction 1 continues..UPDATEalbumsSETmarketingbudget=marketingbudget+100000WHEREsingerid=1ANDalbumid=4;COMMIT;

Transaction 1 commits successfully, even thoughTransaction 2 alreadyallocated50,000 of the remaining100,000 budget to a new albumAlbumId = 5.

You can use theSELECT...FOR UPDATE syntax to validate that certain reads of atransaction are unchanged during the lifetime of the transaction in order toguarantee the correctness of the transaction. In the following example usingSELECT...FOR UPDATE,Transaction 1 aborts at commit time.

-- Transaction 1 continues..SELECTSUM(MarketingBudget)ASTotalBudgetFROMAlbumsWHERESingerId=1FORUPDATE;/*-----------*| TotalBudget |+------------+| 300000     |*------------*/COMMIT;

-- Transaction 1 continues..SELECTSUM(marketingbudget)AStotalbudgetFROMalbumsWHEREsingerid=1FORUPDATE;/*-------------* | totalbudget | +-------------+ | 300000      | *-------------*/COMMIT;

For more information, seeUse SELECT FOR UPDATE in repeatable read isolation.

Write-write conflicts and correctness

By using repeatable read isolation level, concurrent writes on the same dataonly succeed if there are no conflicts.

In the following example,Transaction 1 establishes a snapshot timestamp atthe firstSELECT statement.

-- Transaction 1BEGIN;-- Snapshot established at T1SELECTAlbumId,MarketingBudgetFROMAlbumsWHERESingerId=1;

-- Transaction 1BEGIN;-- Snapshot established at T1SELECTalbumid,marketingbudgetFROMalbumsWHEREsingerid=1;

The followingTransaction 2 reads the same data asTransaction 1 and insertsa new item.Transaction 2 successfully commits without waiting or aborting.

-- Transaction 2BEGIN;-- Snapshot established at T2 (> T1)SELECTAlbumId,MarketingBudgetFROMAlbumsWHERESingerId=1;INSERTINTOAlbums(SingerId,AlbumId,MarketingBudget)VALUES(1,5,50000);COMMIT;

-- Transaction 2BEGIN;-- Snapshot established at T2 (> T1)SELECTalbumid,marketingbudgetFROMalbumsWHEREsingerid=1;INSERTINTOalbums(singerid,albumid,marketingbudget)VALUES(1,5,50000);COMMIT;

Transaction 1 continues afterTransaction 2 has committed.

-- Transaction 1 continuesINSERTINTOAlbums(SingerId,AlbumId,MarketingBudget)VALUES(1,5,30000);-- Transaction abortsCOMMIT;

-- Transaction 1 continuesINSERTINTOalbums(singerid,albumid,marketingbudget)VALUES(1,5,30000);-- Transaction abortsCOMMIT;

Transaction 1 aborts sinceTransaction 2 already committed an insertionto theAlbumId = 5 row.

What's next

• Learn how toUse repeatable read isolation level.

• Learn how toUse SELECT FOR UPDATE in repeatable read isolation.

• Learn how toUse SELECT FOR UPDATE in serializable isolation.

• Learn more about Spanner serializability and externalconsistency, seeTrueTime and external consistency.