Read Committed Transactions

On this page

New in v23.2: READ COMMITTED is one of two transaction isolation levels supported on CockroachDB. By default, CockroachDB uses the SERIALIZABLE isolation level, which is the strongest ANSI transaction isolation level.

READ COMMITTED isolation is appropriate in the following scenarios:

• Your application needs to maintain a high workload concurrency with minimal transaction retries, and it can tolerate potential concurrency anomalies. Predictable query performance at high concurrency is more valuable than guaranteed transaction serializability.

• You are migrating an application to CockroachDB 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 serializable ordering, READ COMMITTED isolation permits some concurrency anomalies in exchange for minimizing transaction aborts, retries, and blocking. Compared to SERIALIZABLE transactions, READ COMMITTED transactions return fewer serialization errors that require client-side handling. See 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.

Enable READ COMMITTED isolation

To make READ COMMITTED isolation available to use on a cluster, enable the following cluster setting in the SQL shell:

SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = 'true';

In v23.2, sql.txn.read_committed_isolation.enabled is false by default. As a result, READ COMMITTED transactions are automatically upgraded to SERIALIZABLE unless this setting is enabled. This differs in v24.1 and later, where sql.txn.read_committed_isolation.enabled is true by default.

After you enable the cluster setting, you can set READ COMMITTED as the default isolation level or begin a transaction as READ COMMITTED.

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 SET SESSION CHARACTERISTICS statement, which applies to the current session:

  SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• The default_transaction_isolation session variable:

  At the session level:

  SET default_transaction_isolation = 'read committed';
  

  At the database level:

  ALTER DATABASE db SET default_transaction_isolation = 'read committed';
  

  At the role level:

  ALTER ROLE foo SET default_transaction_isolation = 'read committed';
  

• The default_transaction_isolation session variable as a connection parameter with cockroach sql:

  cockroach sql -–url='postgresql://{username}@{host}:{port}/{database}?options=-c default_transaction_isolation=read%20committed'
  

To view the default isolation level of the session:

SHOW default_transaction_isolation;

    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 BEGIN TRANSACTION ISOLATION LEVEL statement:

  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• The SET TRANSACTION ISOLATION LEVEL statement, at the beginning of the transaction:

  BEGIN;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• The transaction_isolation session variable, at the beginning of the transaction:

  BEGIN;
    SET transaction_isolation = 'read committed';
  

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

SHOW transaction_isolation;

  transaction_isolation
-------------------------
  read committed

READ COMMITTED transaction behavior

READ COMMITTED and SERIALIZABLE transactions both serve globally consistent ("non-stale") reads and commit atomically. 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 serializable ordering.

• 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.

  Note:

  For details on how this is implemented, see Read snapshots.

• 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.

• You can mitigate concurrency anomalies by issuing locking reads in READ COMMITTED transactions. These statements can block concurrent transactions that are issuing writes or other locking reads on the same rows.

READ COMMITTED transactions can abort in the following scenarios:

• Transactions at all isolation levels are subject to lock contention, where a transaction attempts to lock a row that is already locked by a write or locking read. 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 (ABORT_REASON_ABORTED_RECORD_FOUND or ABORT_REASON_PUSHER_ABORTED) returned to the client.

• Constraint violations will abort transactions at all isolation levels.

• In rare cases under READ COMMITTED isolation, a RETRY_WRITE_TOO_OLD or ReadWithinUncertaintyIntervalError error will 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 16 KiB before overflowing and being streamed to the client. You can configure the result buffer size using the sql.defaults.results_buffer.size cluster setting or the results_buffer_size session variable.

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 serializable ordering.

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.

Example: Non-repeatable reads and phantom reads

  CREATE TABLE kv (k INT PRIMARY KEY, v INT);

  INSERT INTO kv VALUES (1, 2);

  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SELECT * FROM kv WHERE v = 2;

    k | v
  ----+----
    1 | 2

  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

  UPDATE kv SET k = 2 WHERE v = 2;
    INSERT INTO kv VALUES (3, 2);
    COMMIT;

  SELECT * FROM kv WHERE v = 2;

    k | v
  ----+----
    2 | 2
    3 | 2

Lost update anomaly

The READ COMMITTED conditions that permit 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.

Example: Lost update anomaly

  CREATE TABLE kv (k INT PRIMARY KEY, v INT);

  INSERT INTO kv VALUES (1, 2);

  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SELECT * FROM kv WHERE k = 1;

    k | v
  ----+----
    1 | 2

  BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

  UPDATE kv SET v = 3 WHERE k = 1;
    COMMIT;

  UPDATE kv SET v = 4 WHERE k = 1;
    COMMIT;

  SELECT * FROM kv WHERE k = 1;

    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 AS OF SYSTEM TIME and a timestamp earlier than the commit in Session 1:

SELECT * FROM kv AS OF SYSTEM TIME '2023-11-09 21:22:10' WHERE k = 1;

  k | v
----+----
  1 | 3

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.

Example: Write skew anomaly

For an example of how a write skew anomaly can occur, see Demonstrate interleaved statements in READ COMMITTED transactions.

Locking reads

To reduce the occurrence of concurrency anomalies in READ COMMITTED isolation, you can strengthen the isolation of individual reads by using SELECT ... FOR UPDATE or SELECT ... FOR SHARE to issue locking reads on specific rows. Locking reads behave similarly to writes: 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.

• 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.

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. Locking reads prevent such anomalies, but increase the amount of lock contention that may require intervention if latency becomes too high. Note that locking reads do not prevent phantom reads that are caused by the insertion of new rows, since only existing rows can be locked.

To use locking reads:

• 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, but not update the row, use SELECT ... FOR SHARE to block all concurrent writes on the row without unnecessarily blocking concurrent reads.

  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.

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 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.
• Use exclusive locks to strengthen isolation for READ COMMITTED transactions.
• Use shared locks to reserve values in READ COMMITTED transactions.

Before you begin

1. Open the SQL shell using cockroach demo.

2. Enable READ COMMITTED transactions:

   SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = 'true';
   

3. Create the doctors table:

   CREATE TABLE doctors (
     id INT PRIMARY KEY,
     name TEXT
   );
   

4. Create the schedules table:

   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:

   INSERT INTO doctors VALUES
     (1, 'Abe'),
     (2, 'Betty');
   

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

   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:

UPDATE schedules SET on_call = true WHERE on_call = false;

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

SELECT day, count(*) AS on_call FROM schedules
  WHERE on_call = true
  GROUP BY day
  ORDER BY day;  

     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

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM schedules
  WHERE day = '2023-12-05';

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM schedules
  WHERE day = '2023-12-05';

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t

UPDATE schedules SET on_call = false
  WHERE day = '2023-12-05'
  AND doctor_id = 1;

SELECT * FROM schedules
  WHERE day = '2023-12-05';

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    t

UPDATE schedules SET on_call = false
  WHERE day = '2023-12-05'
  AND doctor_id = 2;

SELECT * FROM schedules
  WHERE day = '2023-12-05';

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    f

COMMIT;

SELECT * FROM schedules
  WHERE day = '2023-12-05';

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    f

ROLLBACK;

Reserve rows for updates using exclusive locks

Before proceeding, reset the example scenario:

UPDATE schedules SET on_call = true WHERE on_call = false;

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

SELECT day, count(*) AS on_call FROM schedules
  WHERE on_call = true
  GROUP BY day
  ORDER BY day;  

     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

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM schedules
  WHERE day = '2023-12-05'
  ORDER BY doctor_id
  FOR UPDATE;

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM schedules
  WHERE day = '2023-12-05'
  ORDER BY doctor_id
  FOR UPDATE;

UPDATE schedules SET on_call = false
  WHERE day = '2023-12-05'
  AND doctor_id = 1;

COMMIT;

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    t

ROLLBACK;

Reserve row values using shared locks

Before proceeding, reset the example scenario:

UPDATE schedules SET on_call = true WHERE on_call = false;

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

SELECT day, count(*) AS on_call FROM schedules
  WHERE on_call = true
  GROUP BY day
  ORDER BY day;  

     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

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM schedules
  WHERE day = '2023-12-05'
  FOR SHARE;

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM schedules
  WHERE day = '2023-12-05'
  FOR SHARE;

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    t
  2023-12-05 |         2 |    t

Shared locks are typically used 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:

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:

UPDATE 1

COMMIT;

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

SELECT * FROM schedules
  WHERE day = '2023-12-05';

     day     | doctor_id | on_call
-------------+-----------+----------
  2023-12-05 |         1 |    f
  2023-12-05 |         2 |    t

Known limitations

The following are not yet supported with READ COMMITTED:

• Schema changes (e.g., CREATE TABLE, CREATE SCHEMA, CREATE INDEX) cannot be performed within explicit READ COMMITTED transactions, and will cause transactions to abort. As a workaround, set the transaction's isolation level to SERIALIZABLE.
• READ COMMITTED transactions performing INSERT, UPDATE, or UPSERT cannot access REGIONAL BY ROW tables in which UNIQUE and PRIMARY KEY constraints exist, the region is not included in the constraint, and the region cannot be computed from the constraint columns.
• Shared locks cannot yet be promoted to exclusive locks.
• SKIP LOCKED requests do not check for replicated locks, which can be acquired by READ COMMITTED transactions.

The following affect the performance of READ COMMITTED transactions:

• Because locks acquired by foreign key checks, SELECT FOR UPDATE, and SELECT FOR SHARE are fully replicated under READ COMMITTED isolation, some queries experience a delay for Raft replication.
• Foreign key checks are not performed in parallel under READ COMMITTED isolation.
• SELECT FOR UPDATE and SELECT FOR SHARE 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 subqueries) are not currently performed on these queries.

See also

• Transaction Layer
• SELECT ... FOR UPDATE
• Serializable Transactions
• What Write Skew Looks Like
• Read Committed RFC
• Migration Overview

Was this helpful?