[Performance] Poor throughput for JDBC / SQL `INSERT INTO ... VALUES` bulk writes (TPC-C load vs PostgreSQL)

## Background

On **10.222.1.128**, we compared **MatrixOne** vs **PostgreSQL 17** for **TPC-C 100-warehouse** data loading using the same **mo-tpcc** (BenchmarkSQL fork) loader.

Load path is application-side looping:

- `PreparedStatement` + `addBatch` / `executeBatch`
- SQL shape: `INSERT INTO ... VALUES (?)` (JDBC may rewrite to multi-value `VALUES (...),(...),...`)
- **Not** `LOAD DATA` / file bulk import

Focus: **MO write performance for `INSERT INTO ... VALUES` (including multi-value batches)**, and lock waits under concurrent batch inserts.

## Environment

| Item | Value |
|------|--------|
| Host | 10.222.1.128 |
| Tool | mo-tpcc `io.mo.LoadData` / `LoadDataWorker` |
| Scale | warehouses=100 |
| PG | Docker PG17, `synchronous_commit=off`, `reWriteBatchedInserts=true`, `loadWorkers=32` |
| MO | Standalone, custom listen port, local disk; MySQL JDBC connector |

MO JDBC (after tuning):

```text
rewriteBatchedStatements=true&useServerPrepStmts=false
```

PG JDBC:

```text
reWriteBatchedInserts=true
```

## Repro steps

1. Create tables via mo-tpcc `tableCreates` (PRIMARY KEY on each table; secondary indexes created **after** load).
2. Run: `./runLoader.sh props_100.{mo,pg}`
3. Observe `SHOW PROCESSLIST` / loader logs for warehouse `done` and elapsed time.

Loader behavior (identical on both sides):

- `loadWorkers` connections, `autoCommit=false`
- Worker 0 loads `bmsql_item` (100k rows)
- Other workers load per warehouse: warehouse → stock (100k) → district/customer/order…
- **One `commit` only after the entire warehouse is inserted** (very large transaction)
- stock: `executeBatch` every 10,000 rows; item: every 1,000 rows

## Results

### 1) PostgreSQL (baseline)

| Config | Result |
|--------|--------|
| `loadWorkers=32` + `reWriteBatchedInserts=true` | **runLoader ≈ 110.6 s**, full 100 warehouses OK |

### 2) MatrixOne — without batch rewrite (default props)

| Config | Result |
|--------|--------|
| `useServerPrepStmts=true`, **no** `rewriteBatchedStatements` | Processlist shows per-row `COM_STMT_EXECUTE` + single-row `INSERT ... VALUES (?,...)` |
| `loadWorkers=32` | After ~10+ minutes, still almost **0 warehouse commits** (stuck on stock inserts) |

Note: with default MySQL JDBC settings, batches are not rewritten to multi-value inserts, so the **protocol amplifies round-trips / execute count**.

### 3) MatrixOne — with `rewriteBatchedStatements=true`

Rewrite confirmed active, e.g.:

```sql
INSERT INTO bmsql_item (...) VALUES (38001, ...),(38002, ...),...
INSERT INTO bmsql_stock (...) VALUES (...),(...)...
```

| Config | Result |
|--------|--------|
| `loadWorkers=32` | ~12–16 warehouses done in ~6–7 min, then many **`Lock wait timeout exceeded`**, load fails / instance unstable |
| `loadWorkers=8` | ~29 warehouses done in ~11 min; still lock waits / connection errors |
| `loadWorkers=1` | Stable progress at **~40 s/warehouse**; full 100 WH extrapolated **~65–70 min** (interrupted at ~32–36 WH / ~21–23 min) |

Comparison (same tool, same SQL shape):

- After rewrite, MO **per-warehouse** time is in the same ballpark as a single PG worker (tens of seconds/WH)
- But MO **cannot sustain 32-way parallel load like PG**; serial wall-clock is about **~35×+** of PG
- Relative to PG, the bottleneck shifts from “row-by-row insert” to: **lock waits under concurrent batch INSERT + cost of large-transaction write path**

## Analysis: why `loadWorkers=32` hits lock wait

### What failed (not a same-PK conflict)

This is **not** classic “two workers insert the same primary key”.

Each warehouse uses a distinct `w_id`, so row PKs are disjoint, e.g.:

- `bmsql_stock (s_w_id, s_i_id)`
- `bmsql_order_line (ol_w_id, ol_d_id, ol_o_id, ol_number)`

With rewrite enabled, stack traces for the timeouts land here:

```text
java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(...)
at io.mo.LoadDataWorker.loadWarehouse(LoadDataWorker.java:757)
```

`LoadDataWorker.java:757` is the **`bmsql_order_line` `executeBatch()`** (after stock/customer for that warehouse, **before** the warehouse `commit`):

```text
stmtOrder.executeBatch();
stmtOrderLine.executeBatch(); // line 757 — lock wait observed here
stmtNewOrder.executeBatch();
// ... after all districts ...
dbConn.commit(); // one commit per warehouse
```

So workers fail while holding a **long open transaction** and issuing another large multi-value INSERT on a shared table.

### Timeline matches lock contention

On the 32-worker rewrite run:

1. Worker 0 finishes `ITEM`.
2. Some warehouse workers complete (e.g. 8, 23, 24, 28, 30) and even start the next warehouses (35–37).
3. Many other workers still in their **first** warehouse then fail with **`Lock wait timeout exceeded`** (~29 workers).

That pattern is “some txns finish/commit while others still insert,” not “everyone deadlocks on one row.”

### Why concurrency 32 triggers it

Loader model (same on MO and PG):

| Factor | Behavior |
|--------|----------|
| Connections | 32 concurrent sessions, `autoCommit=false` |
| Transaction size | ~200k+ rows per warehouse before **one** `commit` |
| Batch shape | After rewrite: large multi-value `INSERT ... VALUES (...),(...),...` (e.g. 10k stock rows / batch) |
| Tables | Many workers insert into the **same** tables concurrently (`stock`, `customer`, `order_line`, …) with different `w_id` |

On PostgreSQL this parallel append pattern completes (~110 s). On MatrixOne, the same pattern produces widespread lock waits unless concurrency is dropped (e.g. `loadWorkers=1` progresses at ~40 s/WH with no lock errors).

So the issue is not the number “32” itself, but:

**high concurrency × huge uncommitted transactions × concurrent multi-value INSERT into the same tables.**

### Likely mechanisms (inferred; needs lock-wait graph to confirm)

| Hypothesis | Why it fits |
|------------|-------------|
| **Coarse write locking / append-path mutex** | Disjoint PKs still timeout → contention likely above pure row locks (table/object/append chain / similar). |
| **Long lock hold time** | Each of ~32 txns holds uncommitted writes for tens of seconds; commit of finished warehouses overlaps with in-flight inserts → waits until `lock_wait_timeout`. |
| **Commit / flush crossing inserts** | Failures cluster **after** some warehouses `done` (commits started), while others still batch-insert into the same tables. |
| **Secondary: `bmsql_history.hist_id auto_increment`** | Column is `auto_increment` while the loader also sets `hist_id` explicitly; global increment metadata could add serialization. Secondary factor: primary failure site observed is **`order_line`**, not history. |

### Relationship to overall slowness

| Symptom | Role |
|---------|------|
| Lock wait at 32/8 workers | Blocks scaling; forces serial or low parallelism |
| ~40 s/warehouse at 1 worker | Even without locks, large-txn multi-value INSERT path is heavy |
| PG 32 workers ~110.6 s | Same app path sustains parallelism |

**Bottom line:** at `loadWorkers=32`, MO reports lock errors because many oversized load transactions concurrently multi-value-insert into the same tables; waits exceed lock timeout even though PK ranges do not overlap. Lowering concurrency avoids the error but makes wall-clock far worse than PostgreSQL.

## Expected

1. With `rewriteBatchedStatements=true` (multi-value `INSERT ... VALUES (...),(...),...`), bulk write throughput should be closer to same-host PostgreSQL / MySQL JDBC loading.
2. Concurrent connections inserting into **disjoint PK ranges** (e.g. different `w_id` in stock) should not hit long **lock wait timeouts** routinely.
3. Single-connection large transactions (~200k+ rows per warehouse before commit) should have a reasonable latency bound, not become an order-of-magnitude JDBC load bottleneck.

## Actual

1. Default JDBC (server prepared stmts, no rewrite) → effectively row-by-row inserts; impractical.
2. With rewrite, single-warehouse rate is acceptable, but **high-concurrency multi-value INSERT hits lock waits**, forcing lower concurrency; overall much slower than PG.
3. On the same application path, MO’s write path is unfriendly to **large batches + large transactions + many connections**.