# Write Workload Attribution SOP - Optimization ID: `write-workload-attribution-sop-2026-07-24` - Owner: auto-oncall-services - Source references: `APID-29194`, `APID-29231` - Implementation status: problem A deployed and verified in staging; problem B remains a separate candidate - Effective status: mixed problem-level result; problem A is `optimized_verified` and problem B remains `candidate` ## Bounded problems ### A. Generic transaction wrapper attribution - Problem ID: `write-path-attribution-stops-at-generic-transaction-wrapper` - Ticket: `APID-29194` - Status: `optimized_verified` The scenario router previously identified a Commit/Prewrite incident while stopping before it mapped the generic transaction wrapper to a concrete write workload and transaction shape. The earlier adaptive mechanism-first action failed, but the later bounded workload-first gate closed this exact problem in the deployed source-case replay. ### B. Causal magnitude and proxy attribution - Problem ID: `write-path-causal-magnitude-prevents-proxy-overattribution` - Ticket: `APID-29231` - Status: `candidate` An aligned wait may be too small to explain the end-to-end result, while CPU pressure or a cumulative ANALYZE ranking may be correlated without being the direct mechanism. These signals must not be promoted without a magnitude check and direct mechanism evidence. ## Candidate change The main router now records whether the user needs mechanism, workload attribution, remediation, or a combination. In write-path cases that ask for workload, SQL, batch, or transaction attribution/remediation, `workload_identity` and `transaction_shape` are required scenario exit gates. The write-path scenario adaptively follows generic Commit/Prewrite evidence through TiDB TopSQL and Statements, write-specific Slow Query fields and ordering, pagination or narrower slices, then exact Digest and connection/transaction mapping. It separately evaluates scheduler, lock, Raft/apply, storage/flow-control, and service mechanisms and checks whether a candidate wait is large enough to explain the observed result. ## Verification evidence - The skill validator passed for `skills/op-diagnosis`. - `npm run build` passed. - Focused router, runner, and optimization-record tests passed: 18/18. - The full repository test suite passed: 423/423. ### APID-29194 exact candidate replay On 2026-07-24, the exact candidate replay was PARTIAL and did not satisfy problem A. - Run ID: `022f2bb2-c867-4ca5-85a5-0306f7a44cec` - Run created: `2026-07-24T12:36:32.861Z` - Result completed: `2026-07-24T12:50:02.468Z` - Historical replay workspace: `/tmp/apid-29194-runs-WWaTOo/022f2bb2-c867-4ca5-85a5-0306f7a44cec` - Mode: read-only historical replay; no Jira, database, deployment, or customer-visible write was requested. The candidate correctly routed the ticket to write-path contention, found repeated generic COMMIT transactions of about 255,000 keys and 22 MB, ruled out latch, lock, retry, and backoff as explanations, and gave the appropriate bounded advice to reduce transaction batch size and concurrency. However, it did not map those generic transactions to the concrete `dh_active_receive_logs` INSERT or its digest. It also did not complete the scheduler, RocksDB, or Raft apply mechanism chain. Because the acceptance criterion requires the generic Commit/Prewrite wrapper to be mapped to the originating DML and transaction evidence, this attempted action is `ineffective`, not verified or merely awaiting deployment. The problem remains unresolved and may be selected for a materially different action; the same SOP change and drill-down attempt must not be repeated as if it were untried. ### APID-29194 installed-candidate regression replay The later isolated replay `25755f4a-4f75-4537-b4e9-3732a7dfb3fe`, completed at `2026-07-27T05:31:31.797Z`, confirms that the prior flexible/adaptive action remains ineffective for problem A even after compact provider evidence became available. The provider was ready and raw evidence did not expand the model context, but the initial turn spent five mechanism-oriented queries before attempting the required workload gate: broad scheduler latency, the same scheduler latency on the affected store, latch wait, an optional TiDB commit counterpart, and an extra scheduler-to-latch ratio. It then ran neither TopSQL nor Slow Query and asked the customer for the originating DML and transaction shape. The final answer therefore reproduced the exact problem signal: it stopped at a generic Commit/Prewrite mechanism while provider workload surfaces remained unattempted. This is regression evidence for the same bounded problem, not a ticket-wide failure and not evidence that compact provider input itself was ineffective. It reinforces that another open-ended instruction to continue adaptively is the failed action that must not be repeated. ### APID-29194 deployed verification replay The staging runtime started the deployed image at `2026-07-27T09:52:53.000Z`. Deployed read replay `315d0756-5ec8-471d-a446-86aff517fecc` mapped the generic write transaction to the exact `UPDATE dh_active_receive_logs ... WHERE id IN (...)`, measured about 68,000 write keys and 5.6-6.2 MB, identified Prewrite as dominant, found no retry, lock, or backoff evidence, and recommended bounded batching and concurrency changes. Deployed write replay `6156ddd4-aafd-436f-a72e-48b1dfaa9754` reproduced the same originating DML and transaction shape and updated the existing Jira internal comment `23202442` in place. Jira readback verified the exact comment ID and updated body. This later materially different workload-first action satisfies problem A and supersedes the earlier `ineffective` status for this problem signal; the historical failed action remains recorded above. ### APID-29231 blind branch replay On 2026-07-24, a blind historical replay and an independent judge both passed for problem B. - Run ID: `fbc1b065-0fce-4c4e-b56f-09721f5157bf` - Run created: `2026-07-24T10:27:32.279Z` - Historical cutoff: `2026-07-24T03:34:38+0800`, immediately before the target support reply; only comments 0 and 1 plus the two visible screenshots were included. - Workspace: `/tmp/auto-oncall-apid29231-blind.uQeBZv/runs/fbc1b065-0fce-4c4e-b56f-09721f5157bf` - Mode: read-only historical replay; no Jira, database, deployment, or customer-visible write was requested. The candidate routed write-path contention as primary and capacity pressure as secondary. On TiKV `10.107.3.2:20180`, incident-window Prewrite/Commit P99 rose from about 23/24 ms to event means of 1.26/1.16 s and peaks of 2.91/2.73 s. Prewrite scheduler latch P99 rose from about 0.033 ms to an event mean near 399 ms and a 1.11 s peak. `check_txn_status` and `resolve_lock_lite` latch waits peaked at 20.35 s and 6.66 s with aligned operation-rate increases. These second-scale waits were material enough to support the immediate latch/lock mechanism. The same output applied the magnitude boundary to alternatives. Storage async write P99 peaked near 0.30 s and was retained as only a partial contributor to the 2.9 s result. TiKV CPU peaked at 26.96 of 32 cores, about 84%, so CPU was kept as pressure rather than called saturation or root cause. The output did not substitute cumulative ANALYZE ranking for direct mechanism evidence. The independent judge returned PASS for selecting the direct write-path mechanism, rejecting CPU/ANALYZE proxy attribution, and preserving the remaining causal boundary. This replay validates only problem B on the candidate branch. The diagnostic objective was `mechanism`; `workload_identity` and `transaction_shape` were correctly `not_required`. It did not identify the originating SQL, batch, or transaction shape and therefore does not verify problem A. ### Adjacent regression controls Two additional blind controls were PARTIAL. They are useful overall regression evidence, but they do not verify either bounded problem in this record and must not change their statuses. - `APID-29223`, run `1bec57b2-a72d-4b5a-a583-266113e84281`: PARTIAL. The candidate preserved the main incident direction, but left adjacent time-ordering and alternative-cause gaps. These signals belong to separate connection/control-plane causal problems. - `APID-29214`, run `a3306cf2-86b4-4f0c-8fce-1721733559b9`: PARTIAL. The candidate answered the configuration question but performed unnecessary diagnostic exploration and lost some answer precision. These signals belong to separate objective-completion and stop-condition problems. Across the four formal cases, the candidate produced one branch-level PASS (`APID-29231`) and three PARTIAL results (`APID-29194`, `APID-29223`, and `APID-29214`). Only APID-29194 and APID-29231 are evidence for the two problem signals owned by this record. ## Unresolved gaps - Problem B has branch-level replay and judge evidence, but remains `candidate` because the change is not merged, deployed, or effective in the auto-oncall runtime. The replay is not live `optimized_verified` evidence. - The APID-29223 time-ordering/alternative-cause gaps and APID-29214 objective-completion/stop-condition gaps are separate unresolved problems; they are not verification failures for problem B and are not owned by this optimization record. - The APID-29194 read replay returned `human_required`, while the write replay returned `internal_only`. Both were internal-note-only executions and the diagnosis was consistent, but the safety-classification nondeterminism is a separate unresolved problem. ## Next validation action For problem A, monitor later matching signals as regressions and track the safety-classification fluctuation separately. For problem B, if the change is later merged and deployed through the normal rollout path, repeat the APID-29231 case-specific replay before promoting it beyond `candidate`.