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

*Isolation level* is a database property that defines which data is visible to
concurrent transactions. Spanner supports two of the isolation
levels defined in the ANSI/ISO SQL standard: *serializable* and
*repeatable read*. When you create a transaction, you need to choose the
most appropriate isolation level for the transaction. The chosen isolation level
lets individual transactions prioritize various factors such as latency,
abort rate, and whether the application is susceptible to the effects of data
anomalies. 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 the
strictest concurrency-control guarantees for transactions, which is called
[external consistency](https://docs.cloud.google.com/spanner/docs/true-time-external-consistency).
Spanner behaves as if all transactions were executed
sequentially, even though Spanner actually runs them across
multiple servers (and possibly in multiple datacenters) for higher performance
and availability than single server databases. In addition, if one transaction
completes before another transaction starts to commit, Spanner
guarantees that clients always see the results of transactions in sequential
order. Intuitively, Spanner is similar to a single-machine
database.

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

## Repeatable read isolation

In Spanner, repeatable read isolation is implemented using a
technique commonly known as snapshot isolation. Repeatable read isolation in
Spanner ensures that all read operations within a transaction see
a consistent, or strong, snapshot of the database as it existed at the start of
the transaction. It also guarantees that concurrent writes on the same data only
succeed if there are no conflicts. This approach is beneficial in high
read-write conflict 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 restrictive serializable isolation
level.

With its default
[optimistic concurrency](https://docs.cloud.google.com/spanner/docs/concurrency-control#optimistic_concurrency_control),
reads execute without acquiring locks and without blocking concurrent
writes, which results in fewer aborted transactions that might need to be
retried due to potential serialization conflicts. With
[pessimistic concurrency](https://docs.cloud.google.com/spanner/docs/concurrency-control#pessimistic_concurrency_control),
read operations use snapshots, but exclusive locks apply to data read from
`FOR UPDATE` queries or `lock_scanned_ranges=exclusive` hints, and data written
with DML queries.

For workloads migrating from other databases, we recommend configuring your
application to use repeatable read isolation in Spanner.
Repeatable read transaction semantics, specifically the locking for reads,
matches the default isolation levels in most other databases (for example, MySQL
and PostgreSQL). This helps reduce the need to redesign your
application to work with Spanner's default serializable
isolation level.

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

In the previous example, you can
[use the `SELECT FOR UPDATE` clause in repeatable read isolation](https://docs.cloud.google.com/spanner/docs/use-select-for-update-repeatable-read).
The `SELECT ... FOR UPDATE` clause verifies if the data it read at the chosen
snapshot remains unchanged at commit time. Similarly,
[DML statements](https://docs.cloud.google.com/spanner/docs/dml-tasks) and
[mutations](https://docs.cloud.google.com/spanner/docs/modify-mutation-api), that read data internally to
ensure the integrity of the writes, also verify that the data remains unchanged
at commit time. Additionally, with pessimistic concurrency, the data read
by the `SELECT ... FOR UPDATE`, and data written by DML statements acquire
exclusive locks to prevent any future transactions from committing conflicting
modifications before the current transaction commits.

For workloads migrating from other databases that use `FOR UPDATE` queries, we
recommend configuring your application to use repeatable read isolation with
pessimistic concurrency in Spanner. The application continues
to acquire locks for the data read by the `SELECT ... FOR UPDATE`, which is
the default behavior in other databases.

For more information, see [Use repeatable read isolation](https://docs.cloud.google.com/spanner/docs/use-repeatable-read-isolation).

### Example use case

The following example demonstrates the benefit of using repeatable read
isolation to eliminate locking overhead. Both `Transaction 1` and
`Transaction 2` run in repeatable read isolation.

`Transaction 1` establishes a snapshot timestamp when the `SELECT` statement
runs.

### GoogleSQL

    -- Transaction 1
    BEGIN;

    -- Snapshot established at T1
    SELECT AlbumId, MarketingBudget
    FROM Albums
    WHERE SingerId = 1;

    /*---+---*
    | AlbumId    | MarketingBudget  |
    +---+---+
    | 1          | 50000            |
    | 2          | 100000           |
    | 3          | 70000            |
    | 4          | 80000            |
    *---+---*/

### PostgreSQL

    -- Transaction 1
    BEGIN;

    -- Snapshot established at T1
    SELECT albumid, marketingbudget
    FROM albums
    WHERE singerid = 1;

    /*---+---*
    | albumid    | marketingbudget  |
    +---+---+
    | 1          | 50000            |
    | 2          | 100000           |
    | 3          | 70000            |
    | 4          | 80000            |
    *---+---*/

Then, `Transaction 2` establishes a snapshot timestamp after `Transaction 1`
begins but before it commits. Since `Transaction 1` hasn't updated the data, the
`SELECT` query in `Transaction 2` reads the same data as `Transaction 1`.

### GoogleSQL

    -- Transaction 2
    BEGIN;

    -- Snapshot established at T2 > T1
    SELECT AlbumId, MarketingBudget
    FROM Albums
    WHERE SingerId = 1;

    INSERT INTO Albums (SingerId, AlbumId, MarketingBudget) VALUES (1, 5, 50000);

    COMMIT;

### PostgreSQL

    -- Transaction 2
    BEGIN;

    -- Snapshot established at T2 > T1
    SELECT albumid, marketingbudget
    FROM albums
    WHERE singerid = 1;

    INSERT INTO albums (singerid, albumid, marketingbudget) VALUES (1, 5, 50000);

    COMMIT;

`Transaction 1` continues after `Transaction 2` has committed.

### GoogleSQL

    -- Transaction 1 continues
    SELECT SUM(MarketingBudget) as UsedBudget
    FROM Albums
    WHERE SingerId = 1;

    /*---*
    | UsedBudget |
    +---+
    | 300000     |
    *---*/

### PostgreSQL

    -- Transaction 1 continues
    SELECT SUM(marketingbudget) AS usedbudget
    FROM albums
    WHERE singerid = 1;

    /*---*
    | usedbudget |
    +---+
    | 300000     |
    *---*/

The `UsedBudget` value that Spanner returns is the sum of the
budget read by `Transaction 1`. This sum reflects only the data present at the
`T1` snapshot. It doesn't include the budget that `Transaction 2` added,
because `Transaction 2` committed after `Transaction 1` established snapshot
`T1`. Using repeatable read means that `Transaction 1` didn't have to abort even
though `Transaction 2` modified the data read by `Transaction 1`. However,
the result Spanner returns might or might not be the intended
outcome.

### Read-write conflicts and correctness

In the previous example, if the data queried by the `SELECT` statements in
`Transaction 1` was used to make subsequent marketing budget decisions, there
might be correctness issues.

For example, assume there is a total budget of `400,000`. Based on the result
from the `SELECT` statement in `Transaction 1`, we might think there is
`100,000` left in the budget and decide to allocate it all to `AlbumId = 4`.

### GoogleSQL

    -- Transaction 1 continues..
    UPDATE Albums
    SET MarketingBudget = MarketingBudget + 100000
    WHERE SingerId = 1 AND AlbumId = 4;

    COMMIT;

### PostgreSQL

    -- Transaction 1 continues..
    UPDATE albums
    SET marketingbudget = marketingbudget + 100000
    WHERE singerid = 1 AND albumid = 4;

    COMMIT;

`Transaction 1` commits successfully, even though `Transaction 2` already
allocated `50,000` of the remaining `100,000` budget to a new album
`AlbumId = 5`.

You can use the `SELECT...FOR UPDATE` syntax to validate that certain reads of a
transaction are unchanged during the lifetime of the transaction in order to
guarantee the correctness of the transaction. In the following example using
`SELECT...FOR UPDATE`, `Transaction 1` aborts at commit time.

### GoogleSQL

    -- Transaction 1 continues..
    SELECT SUM(MarketingBudget) AS TotalBudget
    FROM Albums
    WHERE SingerId = 1
    FOR UPDATE;

    /*---*
    | TotalBudget |
    +---+
    | 300000     |
    *---*/

    COMMIT;

### PostgreSQL

    -- Transaction 1 continues..
    SELECT SUM(marketingbudget) AS totalbudget
    FROM albums
    WHERE singerid = 1
    FOR UPDATE;

    /*---*
     | totalbudget |
     +---+
     | 300000      |
     *---*/

    COMMIT;

For more information, see [Use SELECT FOR UPDATE in repeatable read isolation](https://docs.cloud.google.com/spanner/docs/use-select-for-update-repeatable-read).

You can also use pessimistic concurrency, which acquires exclusive locks on data
read by the `SELECT...FOR UPDATE` statement. For example, `Transaction 1`
aborts at commit time because `Transaction 2` committed its modifications
before `Transaction 1` acquired locks, which results in a conflict. However, if
the transaction ordering causes `Transaction 2` to attempt to update the
marketing budget after `Transaction 1` acquires locks, then `Transaction 2`
waits for `Transaction 1` to commit and release the locks before it can
proceed. The pessimistic concurrency option serializes access to data.

For more information, see
[Concurrency control](https://docs.cloud.google.com/spanner/docs/concurrency-control).

### Write-write conflicts and correctness

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

In the following example, `Transaction 1` establishes a snapshot timestamp at
the first `SELECT` statement.

### GoogleSQL

    -- Transaction 1
    BEGIN;

    -- Snapshot established at T1
    SELECT AlbumId, MarketingBudget
    FROM Albums
    WHERE SingerId = 1;

### PostgreSQL

    -- Transaction 1
    BEGIN;

    -- Snapshot established at T1
    SELECT albumid, marketingbudget
    FROM albums
    WHERE singerid = 1;

The following `Transaction 2` reads the same data as `Transaction 1` and inserts
a new item. `Transaction 2` successfully commits without waiting or aborting.

### GoogleSQL

    -- Transaction 2
    BEGIN;

    -- Snapshot established at T2 (> T1)
    SELECT AlbumId, MarketingBudget
    FROM Albums
    WHERE SingerId = 1;

    INSERT INTO Albums (SingerId, AlbumId, MarketingBudget) VALUES (1, 5, 50000);

    COMMIT;

### PostgreSQL

    -- Transaction 2
    BEGIN;

    -- Snapshot established at T2 (> T1)
    SELECT albumid, marketingbudget
    FROM albums
    WHERE singerid = 1;

    INSERT INTO albums (singerid, albumid, marketingbudget) VALUES (1, 5, 50000);

    COMMIT;

`Transaction 1` continues after `Transaction 2` has committed.

### GoogleSQL

    -- Transaction 1 continues
    INSERT INTO Albums (SingerId, AlbumId, MarketingBudget) VALUES (1, 5, 30000);
    -- Transaction aborts
    COMMIT;

### PostgreSQL

    -- Transaction 1 continues
    INSERT INTO albums (singerid, albumid, marketingbudget) VALUES (1, 5, 30000);
    -- Transaction aborts
    COMMIT;

`Transaction 1` aborts since `Transaction 2` already committed an insertion
to the `AlbumId = 5` row.

## What's next

- Learn how to [Use repeatable read isolation level](https://docs.cloud.google.com/spanner/docs/use-repeatable-read-isolation).

- Learn about [Concurrency control](https://docs.cloud.google.com/spanner/docs/concurrency-control).

- Learn how to [Use SELECT FOR UPDATE in repeatable read isolation](https://docs.cloud.google.com/spanner/docs/use-select-for-update-repeatable-read).

- Learn how to [Use SELECT FOR UPDATE in serializable isolation](https://docs.cloud.google.com/spanner/docs/use-select-for-update-serializable).

- Learn more about Spanner serializability and external
  consistency, see [TrueTime and external consistency](https://docs.cloud.google.com/spanner/docs/true-time-external-consistency).