sql: run singleflight txns at high-priority, snapshot isolation

There are a few instances in SQL where we run operations on behalf of one or more txns in a singleflight group. These operations are always read-only (right?).

Because reads block on conflicting writes to the same key, these operations are at risk of deadlocking with the transactions that initiate them, which can lead to subtle bugs like https://github.com/cockroachdb/cockroach/issues/117144. To work around this and at the expense of potentially preventing the initiating txns from committing, we often run these reads with a high-priority txn. This causes the reads to not block on normal priority writes. However, this does not work to avoid deadlock if the initiating txn is also high priority. Remember, serializable txns at the same priority block each other.

One thing we could do to avoid this is to exploit the fact that a weak isolation read (snapshot or read committed) does not block on writes from other txns at the same priority level. This means that a high-priority, snapshot isolation read never blocks on writes. If we really want to ensure that these reads don't block, we could run that at high-priority + snapshot isolation. Assuming these are read-only txns (again, needs to be validated), snapshot isolation will behave the same as serializable.

https://github.com/cockroachdb/cockroach/blob/58502d1125b70dafeea34b96733967a387f9ef0d/pkg/kv/kvserver/txnwait/queue_test.go#L276

Jira issue: CRDB-35214

Epic CRDB-60942