Issue

• Database transactions, loading jobs, and other operations on Yugabyte errors out with the following error.
• This error occurs when reading from a table receiving concurrent inserts.

ERROR: Operation failed. Try again.: Value write after transaction start: { physical: 1621604071925565 } >= { physical: 1621604065724592 }: kConflict
yugabyte=#

Query error: Restart read required at: { read: { physical: <time> } local_limit: { physical: <time> logical: 1 } global_limit: <min> in_txn_limit: <max> serial_no: 0 }

Environment

• Yugabyte/Yugabytedb 2.0 or greater
  

Resolution

1. Set transaction isolation level to serializable read-only deferrable to read against a consistent snapshot for the read-only transaction as shown below.
2. Retry executing the transaction again or execute writes and read in the same transaction.

yugabyte=# begin transaction isolation level serializable read only deferrable;
SET
yugabyte=# select * from test1;
id | count 
----+-------+
5 | new 
1 | one

./yugabyte-db/src/yb/tserver/tablet_service.cc
.........

52 ReadHybridTime FormRestartReadHybridTime(const HybridTime& restart_time) const {
53   DCHECK_GT(restart_time, read_time.read);
54   VLOG(1) << "Restart read required at: " << restart_time << ", original: " << read_time;
55   auto result = read_time;
56   result.read = std::min(std::max(restart_time, safe_ht_to_read), read_time.global_limit);
57   result.local_limit = std::min(safe_ht_to_read, read_time.global_limit);
58   return result;
59  }

Root Cause

Yugabyte DB by default runs in snapshot isolation level, preventing phantom and non-repeatable reads. In an environment where multiple transactions conflict with each other, it's possible the data has changed since it was the last read and before committing in a read/write transaction. This causes the current transaction to read the stale data or read data from the past. In such cases, the read operation needs to be restarted at a higher hybrid time.

Snapshot isolation here uses optimistic locking, allowing concurrent transactions to proceed but may need to fail a transaction leading to a rollback if two transactions attempt to modify the same data at the same time.

Yugabyte keeps track of multiple versions of values corresponding to the same key and the last part of each key is a timestamp(comes from the Hybrid time algorithm), which enables quick navigation to a particular version. Since the data changed, a record with a higher hybrid timestamp exists, reading of which can lead to inconsistent results, therefore, the data has to be read again.

To reproduce a similar scenario, I have started a transaction from one session in snapshot isolation, updated a similar value in another session, and tried to change the same key. The error indicating there was a write after the transaction started at 1621604071925565 (08:34:31 DST ) which is 10 sec later than 1621604065724592(08:34:25 DST)

./ysqlsh -h <hostname>
ysqlsh (11.2-YB-2.4.1.2-b0)
Type "help" for help

yugabyte=# update test1 set count = <new_value> where id= <some_id>;
ERROR: Operation failed. Try again.: Value write after transaction start: { physical: 1621604071925565 } >= { physical: 1621604065724592 }: kConflict
yugabyte=#