roachtest: support tpc-c under read committed

[nvanbenschoten]

As part of the Read Committed implementation, nightly testing of TPC-C will be adapted to run at the Read Committed isolation level. When doing so, transaction retry loops will be removed from the workload to validate that retry errors are rare or non-existent under weak isolation.

TPC-C provides a useful sandbox to test weaker isolation levels because it contains three moderately complex read-write transactions, two read-only transactions, a diverse schema with referential integrity constraints, and twelve post-workload consistency checks.

Jira issue: CRDB-26569

Epic CRDB-26548

[nvanbenschoten]

When doing this, we will also need to ensure that the transaction procedures can tolerate weak isolation. Comparing to benchbase, the one place where it looks like we'll need to add explicit row-level locking is:

diff --git a/pkg/workload/tpcc/new_order.go b/pkg/workload/tpcc/new_order.go
index 5152a2841a6..1ed02a5199b 100644
--- a/pkg/workload/tpcc/new_order.go
+++ b/pkg/workload/tpcc/new_order.go
@@ -302,7 +302,8 @@ func (n *newOrder) run(ctx context.Context, wID int) (interface{}, error) {
                                        SELECT s_quantity, s_ytd, s_order_cnt, s_remote_cnt, s_data, s_dist_%02[1]d
                                        FROM stock
                                        WHERE (s_i_id, s_w_id) IN (%[2]s)
-                                       ORDER BY s_i_id`,
+                                       ORDER BY s_i_id
+                                       FOR UPDATE`,
                                        d.dID, strings.Join(stockIDs, ", "),
                                ),
                        )

It would be interesting to see whether the consistency check at the end of the workload would catch this.

[nvanbenschoten]

They do, nice! workload check tpcc --expensive-checks fails on checks 3.3.2.10 and 3.3.2.12 with Read Committed but not Serializable. Adding just that FOR UPDATE doesn't fix the issue though, so there's something else going wrong.

The checks also don't fail under Repeatable Read.