> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Read Committed Transactions

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

`READ COMMITTED` is one of two [transaction isolation levels](https://wikipedia.org/wiki/Isolation_\(database_systems\)) supported on CockroachDB. By default, CockroachDB uses the <InternalLink path="demo-serializable">`SERIALIZABLE`</InternalLink> isolation level, which is the strongest [ANSI transaction isolation level](https://wikipedia.org/wiki/Isolation_\(database_systems\)#Isolation_levels).

`READ COMMITTED` isolation is appropriate in the following scenarios:

* Your application needs to maintain a high workload concurrency with minimal <InternalLink path="developer-basics#transaction-retries">transaction retries</InternalLink>, and it can tolerate potential [concurrency anomalies](#concurrency-anomalies). Predictable query performance at high concurrency is more valuable than guaranteed transaction <InternalLink path="developer-basics#serializability-and-transaction-contention">serializability</InternalLink>.
* You are <InternalLink version="molt" path="migration-overview">migrating an application to CockroachDB</InternalLink> that was built at a `READ COMMITTED` isolation level on the source database, and it is not feasible to modify your application to use `SERIALIZABLE` isolation.

Whereas `SERIALIZABLE` isolation guarantees data correctness by placing transactions into a <InternalLink path="demo-serializable">serializable ordering</InternalLink>, `READ COMMITTED` isolation permits some [concurrency anomalies](#concurrency-anomalies) in exchange for minimizing transaction aborts, <InternalLink path="developer-basics#transaction-retries">retries</InternalLink>, and blocking. Compared to `SERIALIZABLE` transactions, `READ COMMITTED` transactions do **not** return <InternalLink path="transaction-retry-error-reference">serialization errors</InternalLink> that require client-side handling. See [`READ COMMITTED` transaction behavior](#read-committed-transaction-behavior).

If your workload is already running well under `SERIALIZABLE` isolation, Cockroach Labs does not recommend changing to `READ COMMITTED` isolation unless there is a specific need.

<Note>
  `READ COMMITTED` on CockroachDB provides stronger isolation than `READ COMMITTED` on PostgreSQL. On CockroachDB, `READ COMMITTED` prevents anomalies within single statements.
</Note>

## Enable `READ COMMITTED` isolation

By default, the `sql.txn.read_committed_isolation.enabled` <InternalLink path="cluster-settings">cluster setting</InternalLink> is `true`, enabling `READ COMMITTED` transactions. If the cluster setting is `false`, `READ COMMITTED` transactions will run as `SERIALIZABLE`.

To check whether any transactions are being upgraded to `SERIALIZABLE`, see the <InternalLink path="ui-sql-dashboard#upgrades-of-sql-transaction-isolation-level">**Upgrades of SQL Transaction
Isolation Level**</InternalLink> graph in the DB Console.

### Set the default isolation level to `READ COMMITTED`

To set all future transactions to run at `READ COMMITTED` isolation, use one of the following options:

* The <InternalLink path="set-vars#special-syntax-cases">`SET SESSION CHARACTERISTICS`</InternalLink> statement, which applies to the current session:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
  ```

* The <InternalLink path="session-variables">`default_transaction_isolation`</InternalLink> session variable:

  At the session level:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SET default_transaction_isolation = 'read committed';
  ```

  At the <InternalLink path="alter-database#set-session-variable">database level</InternalLink>:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER DATABASE db SET default_transaction_isolation = 'read committed';
  ```

  At the <InternalLink path="alter-role#set-default-session-variable-values-for-a-role">role level</InternalLink>:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER ROLE foo SET default_transaction_isolation = 'read committed';
  ```

* The `default_transaction_isolation` session variable as a <InternalLink path="connection-parameters#supported-options-parameters">connection parameter</InternalLink> with <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink>:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  cockroach sql --url='postgresql://{username}@{host}:{port}/{database}?options=-c default_transaction_isolation=read\ committed'
  ```

To view the default isolation level of the session:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW default_transaction_isolation;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    default_transaction_isolation
-----------------------------------------------------
    read committed
```

### Set the current transaction to `READ COMMITTED`

To begin a transaction as a `READ COMMITTED` transaction, use one of the following options:

* The <InternalLink path="begin-transaction#parameters">`BEGIN TRANSACTION ISOLATION LEVEL`</InternalLink> statement:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
  ```

* The <InternalLink path="set-transaction#parameters">`SET TRANSACTION ISOLATION LEVEL`</InternalLink> statement, at the beginning of the transaction:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BEGIN;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  ```

* The <InternalLink path="session-variables">`transaction_isolation`</InternalLink> session variable, at the beginning of the transaction:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BEGIN;
    SET transaction_isolation = 'read committed';
  ```

To view the isolation level of a transaction, run `SHOW` within the open transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW transaction_isolation;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  transaction_isolation
-------------------------
  read committed
```

Starting a transaction as `READ COMMITTED` does not affect the [default isolation
level](#set-the-default-isolation-level-to-read-committed), which can be different.

## `READ COMMITTED` transaction behavior

`READ COMMITTED` and `SERIALIZABLE` transactions both serve globally consistent ("non-stale") reads and <InternalLink path="developer-basics#how-transactions-work-in-cockroachdb">commit atomically</InternalLink>. `READ COMMITTED` transactions have the following differences:

* Writes in concurrent `READ COMMITTED` transactions can interleave without aborting transactions, and a write can never block a non-locking read of the same row. This is because `READ COMMITTED` transactions are not required to be placed into a <InternalLink path="demo-serializable">serializable ordering</InternalLink>.

* Whereas statements in `SERIALIZABLE` transactions see data that committed before the transaction began, statements in `READ COMMITTED` transactions see data that committed before each **statement** began. If rows are being updated by concurrent writes, reads in a `READ COMMITTED` transaction can [return different results](#non-repeatable-reads-and-phantom-reads).

  For details on how this is implemented, see <InternalLink path="architecture/transaction-layer#read-snapshots">Read
  snapshots</InternalLink>.

* Due to the preceding behaviors, `READ COMMITTED` transactions permit some types of concurrency anomalies that are prevented in `SERIALIZABLE` transactions. For details and examples, see [Concurrency anomalies](#concurrency-anomalies).

* You can mitigate concurrency anomalies by issuing [locking reads](#locking-reads) in `READ COMMITTED` transactions. These statements can block concurrent transactions that are issuing writes or other locking reads on the same rows.

* When using `READ COMMITTED` isolation, you do **not** need to implement <InternalLink path="transaction-retry-error-reference#client-side-retry-handling">client-side retries</InternalLink> to handle <InternalLink path="transaction-retry-error-reference">serialization errors</InternalLink> under <InternalLink path="performance-best-practices-overview#transaction-contention">transaction contention</InternalLink>. `READ COMMITTED` transactions never return <InternalLink path="transaction-retry-error-reference#retry_serializable">`RETRY_SERIALIZABLE`</InternalLink> errors, and will only return `40001` errors in limited cases, as described in the following points.

<a id="read-committed-abort" />

`READ COMMITTED` transactions can abort in certain scenarios:

* Transactions at all isolation levels are subject to <InternalLink path="performance-best-practices-overview#transaction-contention">*lock contention*</InternalLink>, where a transaction attempts to lock a row that is already locked by a <InternalLink path="architecture/transaction-layer#write-intents">write</InternalLink> or [locking read](#locking-reads). In such cases, the later transaction is blocked until the earlier transaction commits or rolls back, thus releasing its lock on the row. Lock contention that produces a *deadlock* between two transactions will result in a transaction abort and a `40001` error (<InternalLink path="transaction-retry-error-reference#abort_reason_aborted_record_found">`ABORT_REASON_ABORTED_RECORD_FOUND`</InternalLink> or <InternalLink path="transaction-retry-error-reference#abort_reason_pusher_aborted">`ABORT_REASON_PUSHER_ABORTED`</InternalLink>) returned to the client.
* <InternalLink path="constraints">Constraint</InternalLink> violations will abort transactions at all isolation levels.
* In rare cases under `READ COMMITTED` isolation, a <InternalLink path="transaction-retry-error-reference#retry_write_too_old">`RETRY_WRITE_TOO_OLD`</InternalLink> or <InternalLink path="transaction-retry-error-reference#readwithinuncertaintyintervalerror">`ReadWithinUncertaintyIntervalError`</InternalLink> error can be returned to the client if a statement has already begun streaming a partial result set back to the client and cannot retry transparently. By default, the result set is buffered up to the value of the <InternalLink path="cluster-settings">`sql.defaults.results_buffer.size`</InternalLink> cluster setting before overflowing and being streamed to the client.

### Concurrency anomalies

Statements in concurrent `READ COMMITTED` transactions can interleave with each other. This can create concurrency anomalies that are not permitted under `SERIALIZABLE` isolation, which places concurrent transactions into a <InternalLink path="demo-serializable">serializable ordering</InternalLink>.

<Tip>
  The behaviors described in this section assume the use of non-locking reads. You can prevent concurrency anomalies
  through the selective use of [locking reads](#locking-reads), which can also increase latency due to <InternalLink path="performance-best-practices-overview#transaction-contention">lock
  contention</InternalLink>.
</Tip>

#### Non-repeatable reads and phantom reads

`READ COMMITTED` transactions can serve different reads over the course of a transaction.

*Non-repeatable reads* return different row values because a concurrent transaction updated the values in between reads:

1. Transaction `A` reads row `R` at timestamp `1`.
2. Transaction `B` writes to row `R` and commits at timestamp `2`.
3. Transaction `A` reads row `R` and gets a different result at timestamp `3`.

*Phantom reads* return different rows because a concurrent transaction changed the set of rows that satisfy the row search:

1. Transaction `A` reads the set of rows `S` at timestamp `1`.
2. Transaction `B` inserts, deletes, or updates rows in `S` and commits at timestamp `2`.
3. Transaction `A` reads the set of rows `S` and gets a different result at timestamp `3`.

Whereas statements in `SERIALIZABLE` transactions see data that committed before the transaction began, statements in `READ COMMITTED` transactions see data that committed before each **statement** began.

For details on how this is implemented, see <InternalLink path="architecture/transaction-layer#read-snapshots">Read
snapshots</InternalLink>.

##### Example: Non-repeatable reads and phantom reads

##### Session 1

##### Session 2

In a terminal window (Session 1), create a table and insert some values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  CREATE TABLE kv (k INT PRIMARY KEY, v INT);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  INSERT INTO kv VALUES (1, 2);
```

Begin a `READ COMMITTED` transaction and read a table row:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SELECT * FROM kv WHERE v = 2;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    k | v
  ----+----
    1 | 2
```

In a new terminal window (Session 2), begin another `READ COMMITTED` transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Update the table row, insert a new row, and commit the transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  UPDATE kv SET k = 2 WHERE v = 2;
    INSERT INTO kv VALUES (3, 2);
    COMMIT;
```

In Session 1, issue the read again:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SELECT * FROM kv WHERE v = 2;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    k | v
  ----+----
    2 | 2
    3 | 2
```

#### Lost update anomaly

The `READ COMMITTED` conditions that permit [non-repeatable reads and phantom reads](#non-repeatable-reads-and-phantom-reads) also permit *lost update anomalies*, where an update from a transaction appears to be "lost" because it is overwritten by a concurrent transaction:

1. Transaction `A` reads row `R` at timestamp `1`.
2. Transaction `B` writes to row `R` and commits at timestamp `2`.
3. Transaction `A` writes to row `R` and commits at timestamp `3`.

The value of `R` has changed while transaction `A` is open. However, `A` can still write to `R` and commit, effectively overwriting the update from transaction `B`.

<Note>
  Under `SERIALIZABLE` isolation, transaction `A` would have aborted with a
  <InternalLink path="transaction-retry-error-reference#retry_write_too_old">`RETRY_WRITE_TOO_OLD`</InternalLink> error, prompting the client
  to retry the transaction.
</Note>

##### Example: Lost update anomaly

##### Session 1

##### Session 2

In a terminal window (Session 1), create a table and insert some values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  CREATE TABLE kv (k INT PRIMARY KEY, v INT);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  INSERT INTO kv VALUES (1, 2);
```

Begin a `READ COMMITTED` transaction and read a table row:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SELECT * FROM kv WHERE k = 1;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    k | v
  ----+----
    1 | 2
```

In a new terminal window (Session 2), begin another `READ COMMITTED` transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Update the table row and commit the transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  UPDATE kv SET v = 3 WHERE k = 1;
    COMMIT;
```

In Session 1, update the table row again and commit the transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  UPDATE kv SET v = 4 WHERE k = 1;
    COMMIT;
```

Read the table row and see that it reflects the update from Session 1:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SELECT * FROM kv WHERE k = 1;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    k | v
  ----+----
    1 | 4
```

The update in Session 2 appears to be "lost" because its result is overwritten by a concurrent transaction. It is **not** lost at the database level, and can be found using <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> and a timestamp earlier than the commit in Session 1:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM kv AS OF SYSTEM TIME '2023-11-09 21:22:10' WHERE k = 1;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  k | v
----+----
  1 | 3
```

<Note>
  While concurrent `READ COMMITTED` transactions can have their committed writes overwritten, **uncommitted** writes in
  `READ COMMITTED` transactions cannot be overwritten.
</Note>

#### Write skew anomaly

The following sequence of operations on a table is possible under `READ COMMITTED` isolation:

1. Transaction `A` reads row `R` at timestamp `1`.
2. Transaction `B` reads row `S` at timestamp `2`.
3. Transaction `A` writes to row `S` and commits at timestamp `3`.
4. Transaction `B` writes to row `R` and commits at timestamp `4`.

Transaction `A` updates the value of `S` based on the `R` value it reads at timestamp `1`. Transaction `B` updates the value of `R` based on the `S` value it reads at timestamp `2`. The value of `S` has changed while transaction `B` is open, but `B` can still write and commit instead of aborting, since `READ COMMITTED` transactions do not require serializability. This is the basis of potential *write skew anomalies* where two concurrent transactions each read values that the other subsequently updates.

For details on why this is allowed, see <InternalLink path="architecture/transaction-layer">Read
refreshing</InternalLink>.

##### Example: Write skew anomaly

For an example of how a write skew anomaly can occur, see [Demonstrate interleaved statements in `READ COMMITTED` transactions](#demonstrate-interleaved-statements-in-read-committed-transactions).

## Locking reads

To reduce the occurrence of [concurrency anomalies](#concurrency-anomalies) in `READ COMMITTED` isolation, you can strengthen the isolation of individual reads by using <InternalLink path="select-for-update">`SELECT... FOR UPDATE`</InternalLink> or <InternalLink path="select-for-update">`SELECT... FOR SHARE`</InternalLink> to issue *locking reads* on specific rows. Locking reads behave similarly to <InternalLink path="architecture/transaction-layer#write-intents">writes</InternalLink>: they lock qualifying rows to prevent concurrent writes from modifying them until the transaction commits. Conversely, if a locking read finds that a row is exclusively locked by a concurrent transaction, it waits for the other transaction to commit or rollback before proceeding. A locking read in a transaction will always have the latest version of a row when the transaction commits.

The clause used with the `SELECT` statement determines the *lock strength* of a locking read:

* `SELECT FOR UPDATE` obtains an *exclusive lock* on each qualifying row, blocking concurrent writes and locking reads on the row. Only one transaction can hold an exclusive lock on a row at a time, and only the transaction holding the exclusive lock can write to the row. For an example, see [Reserve rows for updates using exclusive locks](#reserve-rows-for-updates-using-exclusive-locks).
* `SELECT FOR SHARE` obtains a *shared lock* on each qualifying row, blocking concurrent writes and **exclusive** locking reads on the row. Multiple transactions can hold a shared lock on a row at the same time. When multiple transactions hold a shared lock on a row, none can write to the row. A shared lock grants transactions mutual read-only access to a row, and ensures that they read the latest version of the row. For an example, see [Reserve values using shared locks](#reserve-row-values-using-shared-locks).

When a `SELECT FOR UPDATE` or `SELECT FOR SHARE` read is issued on a row, only the latest version of the row is returned to the client. Under `READ COMMITTED` isolation, neither statement will block concurrent, non-locking reads.

### When to use locking reads

Use locking reads in your application if certain `READ COMMITTED` transactions must guarantee that the data they access will not be changed by intermediate writes.

Non-locking reads can allow intermediate writes to update rows before `READ COMMITTED` transactions commit, potentially creating [concurrency anomalies](#concurrency-anomalies). Locking reads prevent such anomalies, but increase the amount of lock contention that <InternalLink path="performance-recipes#waiting-transaction">may require intervention</InternalLink> if latency becomes too high. Note that locking reads do **not** prevent [phantom reads](#non-repeatable-reads-and-phantom-reads) that are caused by the insertion of new rows, since only existing rows can be locked.

<Note>
  Locking reads are not effective for emulating `SERIALIZABLE` transactions, which can avoid locking reads because they
  always <InternalLink path="architecture/transaction-layer#read-refreshing">retry or abort if reads are not current</InternalLink>. As a
  result, `READ COMMITTED` transactions that use locking reads will perform differently than `SERIALIZABLE` transactions
  at various levels of concurrency.
  To use locking reads:
</Note>

* If you need to read and later update a row within a transaction, use `SELECT... FOR UPDATE` to acquire an exclusive lock on the row. This guarantees data integrity between the transaction's read and write operations.
* If you need to read the latest version of a row, and later update a **different** row within a transaction, use `SELECT... FOR SHARE` to acquire a shared lock on the row. This blocks all concurrent writes on the row without unnecessarily blocking concurrent reads or other `SELECT... FOR SHARE` queries.

<Tip>
  This allows an application to build cross-row consistency constraints by ensuring that rows that are read in a `READ
        COMMITTED` transaction will not change before the writes in the same transaction have been committed.
</Tip>

## Examples

In this scenario:

* A hospital has an application for doctors to manage their on-call shifts.
* The hospital has a rule that at least one doctor must be on call at any one time.
* Two doctors are on call for a particular shift, and both of them try to request leave for the shift in two concurrent transactions.
* Under the `READ COMMITTED` isolation level, the [write skew anomaly](#write-skew-anomaly) can potentially result in both doctors successfully booking leave and the hospital having no doctors on call for that particular shift.

The following examples demonstrate how to:

* Observe that `READ COMMITTED` transactions can [serve different reads](#demonstrate-interleaved-statements-in-read-committed-transactions).
* Use exclusive locks to [strengthen isolation for `READ COMMITTED` transactions](#reserve-rows-for-updates-using-exclusive-locks).
* Use shared locks to [reserve values in `READ COMMITTED` transactions](#reserve-row-values-using-shared-locks).

### Before you begin

1. Open the SQL shell using <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink>.

2. Enable `READ COMMITTED` transactions:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = 'true';
   ```

3. Create the `doctors` table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE TABLE doctors (
     id INT PRIMARY KEY,
     name TEXT
   );
   ```

4. Create the `schedules` table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE TABLE schedules (
     day DATE,
     doctor_id INT REFERENCES doctors (id),
     on_call BOOL,
     PRIMARY KEY (day, doctor_id)
   );
   ```

5. Add two doctors to the `doctors` table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   INSERT INTO doctors VALUES
     (1, 'Abe'),
     (2, 'Betty');
   ```

6. Insert one week's worth of data into the `schedules` table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   INSERT INTO schedules VALUES
     ('2023-12-01', 1, true),
     ('2023-12-01', 2, true),
     ('2023-12-02', 1, true),
     ('2023-12-02', 2, true),
     ('2023-12-03', 1, true),
     ('2023-12-03', 2, true),
     ('2023-12-04', 1, true),
     ('2023-12-04', 2, true),
     ('2023-12-05', 1, true),
     ('2023-12-05', 2, true),
     ('2023-12-06', 1, true),
     ('2023-12-06', 2, true),
     ('2023-12-07', 1, true),
     ('2023-12-07', 2, true);
   ```

### Demonstrate interleaved statements in `READ COMMITTED` transactions

Before proceeding, reset the [example scenario](#before-you-begin):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE schedules SET on_call = true WHERE on_call = false;
```

Confirm that at least one doctor is on call each day of the week:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT day, count(*) AS on_call FROM schedules
  WHERE on_call = true
  GROUP BY day
  ORDER BY day;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | on_call
-------------+----------
  2023-12-01 |       2
  2023-12-02 |       2
  2023-12-03 |       2
  2023-12-04 |       2
  2023-12-05 |       2
  2023-12-06 |       2
  2023-12-07 |       2
```

##### Session 1

##### Session 2

Doctor 1, Abe, starts to request leave for `2023-12-05` using the hospital's schedule management application.

Start a transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Check to make sure that another doctor is on call for `2023-12-05`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t
```

Around the same time, Doctor 2, Betty, starts to request leave for the same day using the hospital's schedule management application.

In a new terminal (Session 2), open the SQL shell on your <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> cluster. Start a transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Check to make sure that another doctor is on call for `2023-12-05`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t
```

In Session 1, the previous read confirmed that another doctor is available on `2023-12-05`. Update the schedule to put Abe on leave:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE schedules SET on_call = false
  WHERE day = '2023-12-05'
  AND doctor_id = 1;
```

Read the rows for `2023-12-05`. Session 1 sees that only Abe is on leave once its transaction commits:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    t
```

In Session 2, the previous read confirmed that another doctor is available on `2023-12-05`. Update the schedule to put Betty on leave:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE schedules SET on_call = false
  WHERE day = '2023-12-05'
  AND doctor_id = 2;
```

Read the rows for `2023-12-05`. Session 2 sees that only Betty is on leave once its transaction commits:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    f
```

In Session 1, commit the transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
COMMIT;
```

<InternalLink path="architecture/transaction-layer">By design under `READ COMMITTED` isolation</InternalLink>, CockroachDB allows the transaction to commit even though its previous read (the `SELECT` query) has changed due to the concurrent transaction in Session 2.

In Session 2, read the rows for `2023-12-05` again:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    f
```

The result has changed because Session 1 committed earlier and updated the `on_call` value for doctor 1, thus changing the read result for the transaction in Session 2.

If the transaction in Session 2 commits and updates the `on_call` value for Betty, this will create a [write skew anomaly](#write-skew-anomaly). The result would be that neither Abe nor Betty is scheduled to be on call on `2023-12-05`.

Instead, the transaction should rollback so that the write skew anomaly does not commit:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ROLLBACK;
```

### Reserve rows for updates using exclusive locks

Before proceeding, reset the [example scenario](#before-you-begin):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE schedules SET on_call = true WHERE on_call = false;
```

Confirm that at least one doctor is on call each day of the week:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT day, count(*) AS on_call FROM schedules
  WHERE on_call = true
  GROUP BY day
  ORDER BY day;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | on_call
-------------+----------
  2023-12-01 |       2
  2023-12-02 |       2
  2023-12-03 |       2
  2023-12-04 |       2
  2023-12-05 |       2
  2023-12-06 |       2
  2023-12-07 |       2
```

##### Session 1

##### Session 2

Doctor 1, Abe, starts to request leave for `2023-12-05` using the hospital's schedule management application.

Start a transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Check to make sure that another doctor is on call for `2023-12-05`. Use <InternalLink path="select-for-update">`FOR UPDATE`</InternalLink> to lock the rows so that only the current transaction can update them:

<Tip>
  Include an `ORDER BY` clause to force locking to occur in a specific order. This prevents potential deadlock with
  another locking read on the same rows, which can cause the transaction to abort.
</Tip>

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05'
  ORDER BY doctor_id
  FOR UPDATE;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t
```

Around the same time, Doctor 2, Betty, starts to request leave for the same day using the hospital's schedule management application.

In a new terminal (Session 2), open the SQL shell on your <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> cluster. Start a transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Check to make sure that another doctor is on call for `2023-12-05`. Use `FOR UPDATE` to lock the rows so that only the current transaction can update them:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05'
  ORDER BY doctor_id
  FOR UPDATE;
```

However, because Session 1 has already acquired an [exclusive lock](#locking-reads) on these rows, the current transaction is blocked until Session 1 releases its lock.

In Session 1, the previous read confirmed that another doctor is available on `2023-12-05`. Update the schedule to put Abe on leave:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE schedules SET on_call = false
  WHERE day = '2023-12-05'
  AND doctor_id = 1;
```

Commit the transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
COMMIT;
```

Once the transaction in Session 1 commits, it releases its exclusive lock. Session 2 can read the rows for `2023-12-05`, which show that Abe has already been put on leave for that day:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    t
```

Rollback the transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ROLLBACK;
```

### Reserve row values using shared locks

Before proceeding, reset the [example scenario](#before-you-begin):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE schedules SET on_call = true WHERE on_call = false;
```

Confirm that at least one doctor is on call each day of the week:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT day, count(*) AS on_call FROM schedules
  WHERE on_call = true
  GROUP BY day
  ORDER BY day;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | on_call
-------------+----------
  2023-12-01 |       2
  2023-12-02 |       2
  2023-12-03 |       2
  2023-12-04 |       2
  2023-12-05 |       2
  2023-12-06 |       2
  2023-12-07 |       2
```

##### Session 1

##### Session 2

Doctor 1, Abe, starts to request leave for `2023-12-05` using the hospital's schedule management application.

Start a transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Check to make sure that another doctor is on call for `2023-12-05`. Use <InternalLink path="select-for-update">`FOR SHARE`</InternalLink> to lock the rows so that they cannot be updated by another transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05'
  FOR SHARE;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t
```

Around the same time, Doctor 2, Betty, starts to request leave for the same day using the hospital's schedule management application.

In a new terminal (Session 2), open the SQL shell on your <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> cluster. Start a transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

Check to make sure that another doctor is on call for `2023-12-05`. Use `FOR SHARE` to lock the rows so that they cannot be updated by another transaction:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05'
  FOR SHARE;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t
```

Shared locks are [typically used](#when-to-use-locking-reads) when a transaction needs to read the latest version of a row, but does not need to update the row. With the rows locked by both Sessions 1 and 2, a third Session 3 is blocked from updating the rows:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE schedules SET on_call = false
  WHERE day = '2023-12-05'
  AND doctor_id = 1;
```

Once both Sessions 1 and 2 commit or rollback their transactions, Session 3 can complete the update to place Abe on leave:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE 1
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
COMMIT;
```

Read the rows for `2023-12-05` and confirm that Betty is still on call:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM schedules
  WHERE day = '2023-12-05';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    t
```

## Known limitations

* Schema changes (e.g., <InternalLink path="create-table">`CREATE TABLE`</InternalLink>, <InternalLink path="create-schema">`CREATE SCHEMA`</InternalLink>, <InternalLink path="create-index">`CREATE INDEX`</InternalLink> ) cannot be performed within explicit `READ COMMITTED` transactions when the <InternalLink path="set-vars">`autocommit_before_ddl` session setting</InternalLink> is set to `off`, and will cause transactions to abort. As a workaround, <InternalLink path="read-committed#set-the-current-transaction-to-read-committed">set the transaction's isolation level</InternalLink> to `SERIALIZABLE`.
* Multi-column-family checks during updates are not supported under `READ COMMITTED` isolation.
* Because locks acquired by <InternalLink path="foreign-key">foreign key</InternalLink> checks, <InternalLink path="select-for-update">`SELECT FOR UPDATE`</InternalLink>, and <InternalLink path="select-for-update">`SELECT FOR SHARE`</InternalLink> are fully replicated under `READ COMMITTED` isolation, some queries experience a delay for Raft replication.
* <InternalLink path="foreign-key">Foreign key</InternalLink> checks are not performed in parallel under `READ COMMITTED` isolation.
* <InternalLink path="select-for-update">`SELECT FOR UPDATE` and `SELECT FOR SHARE`</InternalLink> statements are less optimized under `READ COMMITTED` isolation than under `SERIALIZABLE` isolation. Under `READ COMMITTED` isolation, `SELECT FOR UPDATE` and `SELECT FOR SHARE` usually perform an extra lookup join for every locked table when compared to the same queries under `SERIALIZABLE`. In addition, some optimization steps (such as de-correlation of correlated <InternalLink path="subqueries">subqueries</InternalLink> ) are not currently performed on these queries.
* Regardless of isolation level, <InternalLink path="select-for-update">`SELECT FOR UPDATE` and `SELECT FOR SHARE`</InternalLink> statements in CockroachDB do not prevent insertion of new rows matching the search condition (i.e., <InternalLink path="read-committed#non-repeatable-reads-and-phantom-reads">phantom reads</InternalLink> ). This matches PostgreSQL behavior at all isolation levels.

## See also

* <InternalLink path="architecture/transaction-layer">Transaction Layer</InternalLink>
* <InternalLink path="select-for-update">`SELECT... FOR UPDATE`</InternalLink>
* <InternalLink path="demo-serializable">Serializable Transactions</InternalLink>
* [What Write Skew Looks Like](https://www.cockroachlabs.com/blog/what-write-skew-looks-like)
* <InternalLink version="molt" path="migration-overview">Migration Overview</InternalLink>