sql: explore adding retry limit and backoff to conn_fsm automatic transaction retries

In https://github.com/cockroachdb/cockroach/pull/146860 and https://github.com/cockroachdb/cockroach/pull/147869 we increased the retry limit and added exponential backoff to the [automatic statement retry loop](https://github.com/cockroachdb/cockroach/blob/378f52f17046cee55760bb7d7bfb754f4a7b4521/pkg/sql/conn_executor_exec.go#L2762) used for Read Committed isolation. This improves throughput of some high-contention workloads from 0 (congestion collapse) to above 0.

We should explore whether it would be beneficial to also add a retry limit and exponential backoff to the [automatic transaction retries](https://github.com/cockroachdb/cockroach/blob/378f52f17046cee55760bb7d7bfb754f4a7b4521/pkg/sql/conn_fsm.go#L644) in conn_fsm. It's possible that there are high-contention workloads that hit automatic transaction retry instead of automatic statement retry, and would benefit from exponential backoff.

While investigating this, it would be worth considering the odd interaction between the two retry loops we noted in https://github.com/cockroachdb/cockroach/blob/378f52f17046cee55760bb7d7bfb754f4a7b4521/pkg/ccl/logictestccl/testdata/logic_test/txn_retry#L13-L50

Jira issue: CRDB-51527