Read this article here:
We are having these errors after performing SELECT queries, in production.
This is causing lot of delays in responding to UI clients
Our setup has, YSQL talking to postgres(yugabyte 2.4.4)
If yugabyte is a transactional database, why we receive such errors from yugabyte?
Read restart errors occur in YSQL but not in Postgres, not because of YSQL’s limitation, but due to the distributed nature of YSQL. There is a complex reasoning to it, but I will skip that and only point out that the cause lies in the fact that clocks between nodes in a distributed database can’t be in sync and this skew causes ambiguities which are bubbled up to the client as a read restart error.
Having said this, YSQL takes a best effort to internally retry the statement/ txn in case of a read restart error. If a read restart error is seen in the first statement of a txn, YSQL will restart that txn and retry the statement. The client will not see an error in this case. The retry logic tries a limited number of times (configurable via the ysql_max_read_restart_attempts gflag).
In your workload, is the SELECT within an implicit or explicit txn block? If so, is it the first statement in the txn always?
Further elaborating on “If a read restart error is seen in the first statement of a txn, YSQL will restart that txn and retry the statement.” -
Restarting a statement requires using a new latest snapshot as of the retry. So, a restart is possible only if no data that belongs to the response of the first statement has been sent to the user. This is because if any data was sent, it would have been based off some snapshot of the database which doesn’t allow retrying the statement on a new snapshot again. The amount of data to be buffered in the query layer before sending it to the user can be controlled by this gflag “ysql_output_buffer_size” which has units of bytes. For example, if ysql_output_buffer_size=256 * 1024, then as long as a read restart occurs in the first 256KB of data, it would be retried internally since none of the 256KB would have yet been sent to the user (all of it would have been buffered).
So, if you are facing a read restart error that can’t be retried by the database since it is not the first query in the transaction or if some data has already been sent as part of the statement, try using the SERIALIZABLE READ ONLY DEFERRABLE transaction mode which waits out the clock skew before starting the read operation, and hence, leaving no scope of uncertainty for a read restart error to occur.
Note that there is an exception to the above details: in READ COMMITTED isolation (that can be run after setting yb_enable_read_committed_isolation=true), read restart errors are retried separately for each statement. This is because all statements in a read committed transaction use a new snapshot and hence if some later statement in the transaction faces a read restart error, it is free to restart again on a new snapshot since that doesn’t require changing the response of past statements which ran on different snapshots. Again, this is allowed per statement as long as no data has already been sent to the user for that statement.
Adding a link to the new “Read Restart errors” section in our architecture docs page - Read Committed isolation level | YugabyteDB Docs
This explains in detail why this error is a consequence of the inherent nature of a distributed database and how YugabyteDB addressed it.