# BATCH ON Shard Semantics

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "batch-on-shard-semantics-2026-08-18",
  "problems": [
    {
      "problemId": "batch-on-low-cardinality-limit-treated-as-row-cap",
      "summary": "The diagnosis treated BATCH ON create_time LIMIT 20000 as a real transaction-size control and recommended lowering only LIMIT, missing that duplicate shard-column boundary values can keep a much larger group in one generated DML statement.",
      "status": "optimized_verified",
      "applicability": "TiDB non-transactional BATCH DML tickets where actual affected rows differ from LIMIT or the shard-column cardinality and safe batch tuning are material to the answer.",
      "issueKeys": ["APID-29340"],
      "sourceRefs": ["https://tidb.atlassian.net/browse/APID-29340#problem:batch-on-shard-column-semantics-missing"],
      "signalTextIncludesAny": ["BATCH ON create_time", "actual rows exceed LIMIT", "low-cardinality shard column", "lowering LIMIT may not reduce the transaction"],
      "observedThrough": "2026-08-19",
      "effectiveAt": "2026-08-19T00:57:14+08:00",
      "verifiedAt": "2026-08-19T10:24:47+08:00",
      "attemptedAction": "Add bounded official TiDB BATCH ON product knowledge so the Scenario Router distinguishes shard-column range semantics from a hard row cap and recommends an indexed low-duplicate column plus DRY RUN validation before case-specific LIMIT tuning.",
      "evidenceUrl": "https://tidb.atlassian.net/browse/APID-29340?focusedCommentId=23548869"
    }
  ]
}
-->

- Optimization ID: `batch-on-shard-semantics-2026-08-18`
- Owner: repository product knowledge selected and applied by Scenario Router
- Source reference: `APID-29340`
- Implementation status: deployed in image `00add7fc3ad53efc10d99e6fa9e687b1fae3197c`
- Effective status: `optimized_verified`

## Bounded problem

The post-route replay correlated the DELETE window with write pressure but
recommended only reducing the existing `LIMIT`. It did not apply TiDB's
non-transactional DML range-splitting behavior: duplicate `create_time` values
at a range boundary can keep far more than the configured limit in one batch.
That made the mitigation potentially ineffective even though its evidence
wording was cautious.

## Change

- Add one searchable product topic for `BATCH ON` shard-column selection,
  duplicate boundary values, actual versus configured batch size, and `DRY RUN`
  prechecks.
- Keep semantic applicability with the Scenario Router; deterministic keyword
  recall only supplies a bounded candidate.
- Keep product behavior separate from incident causality and avoid a universal
  batch-size recommendation.

## Verification evidence

Read replay `ee2e4329-e044-4ea1-bd7a-9de9c8553337` and write replay
`e3615672-9bb2-4245-9f6c-9ad2f5e565c0` both applied the product boundary. The
written answer explains that `LIMIT 20000` is not a hard affected-row limit,
that duplicate `create_time` boundary values can keep a much larger group in
one generated statement, and that lowering only `LIMIT` can therefore be
ineffective. It recommends `DRY RUN QUERY` plus `DRY RUN` and an indexed,
lower-duplicate shard-column candidate without asserting that `id` is always
correct. Judge returned `ai_better` with no AI gaps.

The write replay updated existing Jira internal comment `23548869`; direct Jira
and Service Desk readback confirmed the expected text and `public=false`.
Database run `150100`, evaluation, and Feishu Base publication all succeeded.

## Unresolved gaps

The table definition and exact data distribution still determine whether `id`
or another column is the appropriate shard column. The product rule alone does
not prove that the oversized transaction caused the observed latch alert.

This bounded product-semantics problem is verified. Store-level attribution of
the rare scheduler tail remains a separate evidence gap and does not change
the `BATCH ON` result.