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 cockroachdb/cockroach#89033 that added transaction_timeout.

Jira issue: DOC-12189