Clarify `transaction_timeout` and ` idle_in_transaction_session_timeout`

[mgartner]

The docs for transaction_timeout state:

Aborts an explicit transaction when it runs longer than the configured duration. Stored in milliseconds; can be expressed in milliseconds or as an INTERVAL.

This is misleading because, while it is true that the transaction automatically enters an "aborted" state, any locks that it acquired on previously mutated rows remain held, blocking concurrent mutations on the same rows. In other words, transaction_timeout is not a sufficient safeguard against a poorly behaving application that begins a transaction and never commits, aborts, or rolls-back.

I believe the idle_in_transaction_session_timeout setting is the correct safeguard to use in this case:

Automatically terminates sessions that are idle in a transaction past the specified threshold.

Some suggestions I have for making this more clear in the docs:

1. The description of transaction_timeout should be clear that it is insufficient to safeguard against a txn that is never explicity committed, aborted, rolled-back, and it should mention the idle_in_transaction_session_timeout as an alternative.
2. The description of idle_in_transaction_session_timeout should make it clear that it is a good safeguard to use. It should also be more explicit about how locks held by the txn are released when the connection is closed.

---

There is some more context in the release note in https://github.com/cockroachdb/cockroach/pull/89033 that added transaction_timeout.

Jira issue: DOC-12189

[rafiss]

While we're documenting this, we may want to clarify that our transaction_timeout differs from Postgres. Ours causes the transaction to abort, but Postgres' causes the session to terminate. (Postgres added this setting after we added it to CRDB). https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT