Environment

• Yugabyte DB - YSQL
• Yugabyte DB - YCQL

Issue

This KB documents Yugabyte database retryable transaction errors. All the YSQL/YCQL transaction errors such as SQLSTATE 40001 Schema version mismatch Catalog version mismatch or YSQL errors such as  Transaction expired, Transaction was recently aborted, Conflicts with the committed transaction,  Table schema mismatch, Conflicts with higher priority transaction, Restart read required, Value write after transaction startcan be safely retried. A few examples of such errors are shown below.

ERROR: Operation failed. Try again.: [Operation failed. Try again. Conflicts with higher priority transaction: (transaction error 3)] (SQLSTATE 40001)

ERROR: Catalog Version Mismatch: A DDL occurred while processing this query. Try Again

Transaction expired or aborted by a conflict

Operation failed. Try again: conflicts with higher priority transaction

pq_libpq-test.cc
...................................................
2008...bool RetryableError(const Status& status) {
2089....const auto msg = status.message().ToBuffer();
2090....const std::string expected_errors[] = {"Try Again", 
2091.......................................... "Catalog Version Mismatch",
2092 .........................................."Restart read required at",
2093 .........................................."schema version mismatch for table"};

Resolution

Following are some of the options that can be tried if the database aborts an operation with 40001 errors:

1. Retry the transaction within the application using retry logic.
2. Use a select for update statement to prevent rows from being modified by other transactions.
3. Define SAVEPOINT inside a transaction. This feature is available in the latest version 2.9.1. See GitHub issue Savepoints support in Yugabyte.
4. Design application schema and workload to avoid contention.
5. Pessimistic locking - Yugabyte supports optimistic concurrency control with Pessimistic locking under development. See Github issue for Pessimistic locking support.
6. For Catalog Version mismatch errors, run DDL statements sequentially or retry the operation.

Overview

• Catalog Version mismatch(YSQL/YCQL) - This error occurs if concurrent DDL statements run on the database on the same or different objects. A DML query in YSQL may touch multiple servers, and each server has a Catalog Version which is used to track schema changes. When a DDL statement runs in the middle of the DML query, the Catalog Version is changed and the query has a mismatch, causing it to fail. Refer to the article Catalog version mismatch for more info.
• Schema version mismatch(YCQL/YSQL)- The operation can be safely retried from the client side. Refer to the article Catalog version mismatch for more info.
• Try again(YCQL/YSQL) - The operation can be safely retried from the client side.
• General transaction errors leading to 40001(YSQL/YCQL)  - When concurrent transactions conflict trying to modify the same set of rows, one of the transactions can abort with 40001 an error. This is due to contention.
• Restart read required at(YSQL)- This error occurs if there are multiple concurrent transactions conflicting with each other. The data read during the transaction might no longer be valid, hence the transaction has to be retried. Refer to an article Read Restart Required for more info.

Additional Information

Please refer to YSQL Error Codes for more information on SQL specific errors.