sql/stmtdiagnostics: transaction bundle collection is unreliable for workloads with similarly shaped transactions

## Summary

Transaction bundle collection (`crdb_internal.request_transaction_bundle()`) can fail to collect bundles or take an extremely long time to complete when the workload contains many similarly shaped transactions that share the same first statement fingerprint. This has been observed in customer environments where bundle requests remain in `WAITING` state indefinitely despite the target transaction executing thousands of times.

## Background: How Bundle Collection Works

When a bundle request is created for a transaction fingerprint, the system stores the request along with the ordered list of statement fingerprints that make up that transaction. Collection then works as follows:

1. When a session executes the first non-"allowed" statement in a transaction (i.e., not a `SAVEPOINT`, `RELEASE SAVEPOINT`, `ROLLBACK TO SAVEPOINT`, or `PREPARE`), it calls `ShouldStartTxnDiagnostic()` which checks if the statement's fingerprint matches the **first** statement fingerprint of any pending bundle request.
2. If matched and the request is **unconditional** (no `min_execution_latency`), the request is moved from the available pool (`mu.requests`) to `mu.unconditionalOngoingRequests`, giving this session exclusive ownership of fulfilling this request **on this node**. No other session on the same node can attempt to fulfill the same request while it is held. **Conditional** requests (those with a `min_execution_latency`) remain in `mu.requests` and can be attempted by multiple sessions concurrently, even on the same node.
3. As the transaction executes subsequent statements, each statement fingerprint is validated against the expected sequence via `MaybeContinueDiagnostics()` and `AddStatementBundle()`.
4. If a statement fingerprint does **not** match the expected sequence, the collected data is discarded and the request is released back to the available pool for another session to attempt.
5. If all statements match, the bundle is finalized and inserted. A check in `InsertTxnDiagnostic()` ensures only one bundle is persisted per request across all nodes (last-writer-wins via system table).

Key code paths:
- `pkg/sql/stmtdiagnostics/txn_diagnostics.go:194` - `ShouldStartTxnDiagnostic()`
- `pkg/sql/stmtdiagnostics/txn_diagnostics.go:364` - `ResetTxnRequest()`
- `pkg/sql/txn_instrumentation.go:184` - `AddStatementBundle()` (fingerprint validation)
- `pkg/sql/txn_instrumentation.go:210` - `MaybeStartDiagnostics()` (request acquisition)
- `pkg/sql/txn_instrumentation.go:245` - `MaybeContinueDiagnostics()` (continuation check)

## Problem: Competition Between Similarly Shaped Transactions

Consider a workload with transactions that share the same first statement fingerprint:

```
txn_target: [stmt_a, stmt_b, stmt_c, stmt_d] -- 200 executions/hour
txn_similar1: [stmt_a, stmt_b, stmt_c, stmt_e] -- 50,000 executions/hour
txn_similar2: [stmt_a, stmt_b, stmt_x] -- 30,000 executions/hour
txn_similar3: [stmt_a, stmt_y] -- 10,000 executions/hour
```

When an unconditional bundle request exists for `txn_target`:
- Any session executing `stmt_a` as its first real statement may grab the request.
- Since `txn_similar1`, `txn_similar2`, and `txn_similar3` all start with `stmt_a` and execute far more frequently, they are overwhelmingly likely to grab the request first.
- Each time a non-matching transaction grabs the request, it holds the request exclusively (on that node) for the duration of the matching prefix before discovering a mismatch and releasing it.
- With the request held exclusively per node, the actual target transaction may execute on that node during this window and miss the opportunity entirely.

In the customer's case, the target transaction executed hundreds of times per hour but competed with tens of thousands of similarly shaped transactions. Bundle requests remained in `WAITING` state for days.

Note: each node maintains its own local copy of pending requests (synced via polling `system.transaction_diagnostics_requests` every 10 seconds), so the exclusive lock is per-node. A different node could theoretically fulfill the request, but if the competing workload is distributed across all nodes, the same problem occurs everywhere.

## Specific Limitations

1. **First-statement-only matching**: The initial match is based solely on the first statement fingerprint. This means any transaction starting with the same first statement can grab and hold the request, even if it has no chance of matching subsequent statements.

2. **Exclusive per-node lock (unconditional requests)**: Once a session takes ownership of an unconditional request on a node, no other session on that node can attempt to fulfill it until the current session either completes or releases it. This means even if the target transaction runs on the same node concurrently, it cannot collect the bundle. (Conditional requests with `min_execution_latency` do not have this limitation, as they remain in the shared pool.)

3. **No prioritization or backoff**: There is no mechanism to prioritize matching attempts based on how many statement fingerprints match, or to avoid repeatedly giving the request to transaction types that have already failed to match.

4. **No observability**: There is no logging, metrics, or diagnostic information about failed collection attempts. Users have no way to understand why a request remains in `WAITING` state or how many times collection has been attempted and failed.

5. **Multiple concurrent requests exacerbate the problem**: Having multiple bundle requests active simultaneously increases contention, as each request can be grabbed by non-matching transactions independently.

## Potential Improvements

Some ideas for improving reliability (non-exhaustive, not prioritized):

- **Match on more than the first statement**: Use multiple leading statement fingerprints to narrow down which sessions attempt collection, reducing false starts.
- **Non-exclusive collection**: Allow multiple sessions on the same node to attempt collection concurrently for the same request, with the existing last-writer-wins mechanism in `InsertTxnDiagnostic()` handling deduplication.
- **Add observability**: Expose metrics for collection attempts, failures, and reasons (e.g., mismatched at statement N). Consider logging failed attempts at a debug level.

## Version

First reported on v25.4.2, but the limitation exists in the current implementation on `master`.

Jira issue: CRDB-60242