# Expected Diagnosis: O11Y-2179140

## Root Cause

High-frequency point UPDATE statements on `t_merchant_online` (`amount_by_day`, `total_amount`) caused severe pessimistic lock contention on a single TiKV instance (`db-a924bb0f-tikv-29grsn`). The hotspot instance handled Lock Manager conflicts at 49,990/s (peak 124,313/s), with scheduler latch wait P99 at 45ms and gRPC P99 at 98ms (vs 3ms on healthy instances). Commit phase consumed 86.9% of DB time with 13.1% transaction abort rate, pushing TiDB P999 latency to 19.29s (peak 29.61s).

A secondary large JOIN UPDATE (`UPDATE t_merchant_online JOIN (SELECT ... SUM(real_amount))`) scanning 177M keys added load but was not the primary contention driver.

## Evidence Chain

1. **Metrics (Database Time)**: `tidb_server_handle_query_duration_seconds_sum{sql_type="Update"}` = 216.4s/s (peak 595.8s/s), accounting for 97.7% of total DB time
2. **Metrics (TiKV hotspot)**: `tikv_grpc_msg_duration_seconds{instance="db-a924bb0f-tikv-29grsn"}` P99=98ms vs 3ms on other instances; CPU 2.92 cores vs <1 core
3. **Metrics (Lock contention)**: `tikv_lock_manager_waiter_lifetime_duration` = 49,990/s (peak 124,313/s); `tikv_scheduler_latch_wait_duration_seconds{type="acquire_pessimistic_lock"}` P99=45ms (peak 81ms)
4. **Metrics (Transaction phases)**: Commit = 86.9% of DB time (192.4s/s); Abort = 13.1% (29.1s/s) — abnormally high abort rate
5. **SlowQuery**: digest `d8f5b58...` = `UPDATE t_merchant_online SET amount_by_day=...` with 177,529 executions, avg 13.75s; digest `c823661...` = `UPDATE t_merchant_online JOIN...` scanning 177M total_keys, 200s single execution
6. **Logs**: Only JWKS loading errors (non-critical), no panic/OOM/fatal — rules out system-level failure
7. **Events**: No cluster change events during incident window — rules out infrastructure changes

## Causality

High-freq UPDATE on `t_merchant_online` (177K executions) → pessimistic lock contention on single TiKV Region → Lock Manager conflicts 49,990/s → scheduler latch wait P99 45ms → TiKV gRPC P99 98ms (hotspot) → TiDB commit phase 86.9% of DB time + 13.1% abort → P999 latency 19.29s → alert fired

## Severity

Critical

## Recommendations

1. **Immediate**: Reduce UPDATE frequency on `t_merchant_online.amount_by_day` / `total_amount` — batch aggregation instead of per-transaction point updates
2. **Short-term**: Refactor large JOIN UPDATE (digest `c823661...`) to avoid scanning 177M keys in a single transaction; split into batch updates
3. **Medium-term**: Consider async aggregation pattern (Redis cache + periodic flush) to eliminate real-time row-level contention; evaluate `SHARD_ROW_ID_BITS` for hotspot mitigation

## Scoring Rubric

To pass this case, the Agent must:
- [ ] Correctly identify pessimistic lock contention on `t_merchant_online` as root cause (not attribute to RCU throttling or infra issues)
- [ ] Identify the hotspot TiKV instance (`db-a924bb0f-tikv-29grsn`) with per-instance metric breakdown
- [ ] Query Lock Manager conflicts and scheduler latch wait metrics as evidence
- [ ] Query SlowQuery to identify the high-frequency UPDATE digests
- [ ] Link the evidence chain: UPDATE frequency → lock contention → TiKV hotspot → latency surge
- [ ] Provide specific recommendations targeting the `t_merchant_online` UPDATE pattern