Environment

• Yugabyte DB - YSQL
• Yugabyte DB - YCQL

Issue

This KB documents Yugabyte database retryable transaction errors. Our most current list of all retryable  YSQL/YCQL transaction errors are captured below. 

Examples

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"};

ERROR:  Aborted: ERROR:  duplicate key value violates unique constraint "include_key"

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 from v 2.8  or higher.
     - 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.
7. For violates unique constraint you want to validate this based on a manual query. 
     EX: 
   

   db_name=# select item_id from item_mapping group by item_id having count(item_id) > 1;
   
    item_id
   --------------
   (0 rows)

Typically, thepgsqlerror code40001indicates that the process hit a serialization failure. 

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)  - 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.
• If the DDL operation is failing due to a timeout, run the operation with the options below, and note the time taken, and _temporarily_ consider adjusting the 
  

  \timing
  \set ECHO all

Additional Information

Please refer to YSQL Error Codes for more information on SQL specific errors. You can also review our list of Safe and Unsafe DDL operations to guarantee that the operation you are attempting to execute is recommended.