Missing writes during Batch Execution in a transaction

Description

Due to a day-0 issue with transaction retries it is possible that, in specific circumstances, a transaction is partially executed and no error is returned. This is an atomicity violation and may result in data loss.

This can happen in the following circumstance:

• You use some form of write "batch statement" (for example, multiple write statements into the same message) specifically as the first message/statement in the transaction; and a transaction conflict occurs for any but the first internal statement in that batch statement.

Mitigation

The workaround is to add a read statement (for example, SELECT 1) as the first statement in each transaction.

You can also disable the transaction retries according to the installed release as follows:

• Versions prior to v2.18.5.1, v2.18.6.0, v2.20.1.1, v2.20.2.0, and v2.21.0.0.

  Set the following flags:

  ysql_max_read_restart_attempts = 0
  ysql_max_write_restart_attempts = 0
  

• Versions starting from v2.18.5.1, v2.18.6.0, v2.20.1.1, v2.20.2.0, and v2.21.0.0.

  Set the following YSQL configuration parameter:

  yb_max_query_layer_retries = 0
  

Details

In YSQL, if YugabyteDB receives certain errors from the storage layer (for example, transaction conflict and read-restart errors), YugabyteDB tries to internally retry the transaction if possible. To ensure correctness, YugabyteDB checks that it has not sent any response to the client/user before retrying.

Normally, this check implies only retrying in case the error is hit during the first statement in a transaction. This is because after the first statement is executed, YugabyteDB responds to the client and disables retries for this transaction until it is done. Therefore, the database doesn't need to replay the entire transaction, just the current statement.

However, if the first statement uses some form of batching (one message including multiple statements), YugabyteDB may not respond to the client until the full batch is executed. If YugabyteDB hits an error for the Nth statement in the batch, it may only retry that statement.

This results in all statements before the conflicting one being missed in the retried transaction.

There are two currently known cases where this can occur:

• A wire-protocol Batch Statement.
• A message with multiple semicolon-separated individual statements. The Java driver will split them into individual messages and avoid the issue, but other drivers may behave differently.

Examples

connection.setAutoCommit(false);
statement.addBatch("INSERT INTO t2 VALUES (1,2,3)");
statement.addBatch("INSERT INTO t2 VALUES (2,3,4)");
statement.addBatch("INSERT INTO t2 VALUES (3,4,5)");
statement.addBatch("UPDATE t1 SET v2 = src + 1 WHERE k = 1");
statement.executeBatch();
connection.commit();

BEGIN
Batch Statement
      Write statement 1
Write statement 2
…
Write statement N-1
Write statement N -- if there is a conflict here, we only retry current statement

Statement N+1
Statement N+2
…
Statement M
BatchExecute -- we don't respond to the client until here
…
COMMIT