# Write Workload Candidate Priority

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "write-workload-candidate-priority-2026-07-27",
  "problems": [
    {
      "problemId": "write-attribution-promotes-unlinked-low-ranked-dml",
      "summary": "When generic Commit or Prewrite dominates TopSQL, diagnosis can promote a much lower-ranked co-window DML without transaction linkage and waste the only Slow Query lookup on its exact Digest.",
      "status": "candidate",
      "issueKeys": ["APID-29194"],
      "sourceRefs": [
        "https://tidb.atlassian.net/browse/APID-29194#problem:diagnostic_regression:unlinked-low-ranked-dml-promoted"
      ],
      "signalTextIncludesAny": [
        "low ranked dml treated as originating without transaction linkage",
        "dominant commit wrapper not unpacked through prev_stmt",
        "exact digest slow query returns zero after co-window candidate selection",
        "second window repeats instance selection after first window transaction identification"
      ],
      "observedThrough": "2026-07-27",
      "effectiveAt": null,
      "verifiedAt": null,
      "attemptedAction": "When generic Commit or Prewrite materially dominates TopSQL rate and CPU without direct transaction linkage, keep lower-ranked DML as candidates, use the one table-text Slow Query ordered by write_keys to inspect Commit prev_stmt and transaction fields without a low-confidence Digest, reuse that same narrow query in a second-window gap, and require literal Wetech TopSQL window syntax.",
      "evidenceUrl": null
    }
  ]
}
-->

- Optimization ID: `write-workload-candidate-priority-2026-07-27`
- Owner: auto-oncall-services
- Source reference: `APID-29194`
- Implementation status: local candidate; not deployed
- Effective status: `candidate`

## Bounded problem

TopSQL can show generic transaction wrappers and several unrelated or
partially related SQL statements in the same interval. Co-occurrence alone
does not establish which DML belongs to the dominant transactions. If the
agent treats any business-table DML as originating, one low-confidence exact
Digest can consume the only Slow Query refinement and hide the useful COMMIT
rows and their `prev_stmt`.

This record owns candidate ranking and transaction unwrapping only. It does
not own the broader pre-TopSQL mechanism budget or claim the entire ticket is
resolved.

## Regression evidence

Historical-cutoff replay `f1322bef-da28-49d7-ac16-fc431bafb43a` completed at
`2026-07-27T05:53:03.528Z`.

- Generic `COMMIT` ran at about 839.9 executions/s and consumed about 1121.1s
  of CPU in the TopSQL summary.
- `INSERT INTO dh_active_receive_logs ...` ran at about 1.48 executions/s and
  consumed about 16.7s of CPU.
- No direct transaction, connection, request, or `prev_stmt` linkage joined
  that INSERT to the dominant COMMIT rows.
- The agent nevertheless promoted the INSERT as originating DML and used the
  only Slow Query refinement with both the business-table text and that exact
  INSERT Digest. The query returned zero rows.
- The second-window gap then repeated instance selection and stopped without
  reusing the first-window business-table transaction query.
- The first TopSQL command also passed invalid `--window 10` before a retry
  produced a valid provider request with `window: "10s"`.

This is branch-local pre-fix evidence. It does not verify the candidate change.

## Branch-local query-chain evidence

Replay `63a5df5e-b424-4bc5-bae0-b78e3199d161` applied the candidate-priority
query path correctly. In about three minutes it ran readiness, one mechanism
query, TopSQL with `window: "10s"`, and one business-table Slow Query ordered
by `write_keys` without a low-confidence DML Digest.

The Slow Query returned COMMIT rows whose `prev_stmt` identified
`UPDATE dh_active_receive_logs ... WHERE id IN (...)`, with 68,000 write keys,
about 6.21 MB per transaction, 0.32–0.58 seconds total time, and zero retry,
lock, or backoff evidence. This is positive evidence for the query selection
and transaction-unwrapping behavior.

The diagnostic initial call later timed out during finalization and returned
no JSON. Therefore this record remains `candidate`: the replay did not produce
an accepted case result, and the separate finalization problem is recorded in
`write-diagnosis-finalization-2026-07-27`.

Post-change branch-local replay
`f79d1126-2a24-46ea-b1d8-f0a0a949a735` returned HTTP 200 in 353.271 seconds.
It preserved the intended candidate ordering: generic transaction wrappers
were not replaced by an unlinked low-ranked DML, and no broad TiDB TopSQL or
Statements query ran after the wrapper result. The one table-text Slow Query
unwrapped COMMIT `prev_stmt` to
`UPDATE dh_active_receive_logs ... WHERE id IN (...)` and showed 68,000 write
keys, about 5.55-6.21 MB per transaction, about 0.30-0.65 seconds total time,
and about 0.28-0.63 seconds in Prewrite. Retry, lock, backoff, and latch
evidence were zero. The accepted output kept the originating UPDATE as the
leading workload and was rated `ai_better`.

This is passing branch-local evidence for candidate selection and transaction
unwrapping. It is not evidence that the optimization is available to
auto-oncall, so its machine-readable status remains `candidate`.

## Candidate change

- Compare generic wrapper dominance with every DML candidate using both
  execution rate and CPU.
- Without direct transaction linkage, keep a materially lower-ranked DML as a
  co-window candidate rather than calling it originating.
- When the wrapper dominates, spend the unique Slow Query refinement on
  `--text <business table>`, ordered by `write_keys`, with `prev_stmt` and
  transaction size/stage/retry/lock/backoff fields. Do not add the
  low-confidence DML Digest.
- Use COMMIT rows' `prev_stmt` to unwrap the originating DML.
- Prefer an exact DML Digest only when direct linkage already exists or the
  wrapper does not materially dominate.
- If that first-window table-text query identifies the transaction, reuse the
  same narrow query in the second-window gap. Do not repeat instance selection
  or TopSQL.
- Use Wetech TopSQL `--window 10s` literally; prohibit `--window 10`.

## Unresolved gaps

- The change is not effective in staging or production.
- A table-text Slow Query may still return no rows for short transactions. That
  remains unavailable evidence and must produce a bounded failure rather than
  an inferred attribution.
- No Jira internal comment has been refreshed.

## Next validation action

After staging publication, replay the same cutoff through the rendered pod and
verify the same accepted attribution without broad TiDB TopSQL or Statements
exploration. Separately validate a case with a genuinely load-bearing
second-window gap and confirm it reuses the same narrow Slow Query without
instance selection or TopSQL.