Database Transactions errors out with "Restart read required"

Issue

A query fails with a read restart error similar to the following:

ERROR:  Query error: Restart read required at: { read: { physical: <time } local_limit: { physical: <time } global_limit: <min in_txn_limit: <max serial_no: 0 }
SQLSTATE: 40001

Common symptoms:

• Large SELECT statements that scan a table receiving concurrent writes.
• COPY, ETL, or batch read jobs against tables with active inserts/updates.
• The error appears intermittently and often clears on retry.

Environment

• All supported YugabyteDB versions (YSQL).
• The relaxed visibility option in the Resolution applies to 2024.1.1.0 and later.

Cause

YugabyteDB is a distributed MVCC database, so small clock differences (clock skew) can exist between nodes. To guarantee that a read sees everything committed before it started (read-after-commit visibility), each read picks a read time and treats any row committed within the clock-skew window as ambiguous.

When a read operation encounters a row committed inside the clock skew "ambiguity window" (between your read time and `read time + max_clock_skew_usec`), the database can't tell for sure if that row was written before or after your read started.

To keep your data strictly consistent and prevent stale results, YugabyteDB automatically retries the read with a newer timestamp. However, if the database has already started streaming results back to the client, it can't perform this retry quietly in the background. When that happens, it has to pass the error up to your application to handle.

This is expected behavior under snapshot-based isolation (Read Committed and Repeatable Read), not a bug or a data-corruption issue.

Resolution

Apply the option that fits your workload. Implementing application retries is the recommended long-term fix; the others are targeted mitigations.

1. Add application retry logic (recommended)

Retry on SQLSTATE 40001. A retry mechanism is required for any distributed database, as the same code path also handles transaction conflicts and transient failures.

2. Reduce clock skew

Ensure time synchronization (NTP/chrony, or PTP where available) is healthy on all nodes. Lower, well-bounded skew shrinks the ambiguity window and the chance of the error. Only lower max_clock_skew_usec (default 500 ms) if your hardware or cloud provider guarantees it — setting it below real skew risks availability or consistency.

3. Use SERIALIZABLE READ ONLY DEFERRABLE for background reads

Best for large, latency-tolerant reads (reporting, exports). This avoids the error entirely by waiting out the clock-skew window before reading.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT * FROM large_table;
COMMIT;

Note: This adds up to max_clock_skew_usec (default 500 ms) of latency per transaction, and only works for read-only transactions.

4. Increase ysql_output_buffer_size

If the query is not read-only or 500 ms of added latency is unacceptable, raise the TServer flag ysql_output_buffer_size (default 256 KB). As long as a statement's output has not yet been flushed to the client, YSQL can retry read restarts internally for:

• All statements in a Read Committed transaction.
• The first statement in a Repeatable Read transaction.
• Any standalone statement outside a transaction block.

5. Relax read-after-commit visibility (2024.1.1.0+)

Only when latency and memory cannot be compromised and strict read-after-commit visibility is not required. Affects pure reads only.

SET yb_read_after_commit_visibility TO relaxed;
SELECT * FROM large_table;

References

• Read restart error — YugabyteDB Docs
• max_clock_skew_usec — yb-tserver reference
• Related KB: How to Troubleshoot Database Transaction Retryable Errors