Proposal: On interactive connection, timeouts, and connection conccurency

I propose changing how the hrana protocol handles interactive connections.

How it currently works

Hrana-over-HTTP has become the de facto protocol to interact with sqld. When a request is received by hrana, a Hrana Stream is created. This Stream owns an SQLite connection to execute queries sent to that stream. A Stream is identified with a baton. When a stream is opened, the baton for that stream is returned to the client. The client can pass the baton to subsequent requests so that they are executed on the stream identified by that baton. When clients want to dispose of that stream, they emit a Close command on that stream, which is closed, freeing the SQLite connection in the process. To avoid keeping streams open forever, a watchdog is associated with each stream if a client fails to complete it. If the watchdog fails to be kicked (by receiving a request on that stream), then, after a defined period (10s currently), the stream is expired, and the SQLite connection is released.

Transactions also have a timeout mechanism that is separate from the stream. Transactions are given a (configurable, default to 5s) timeslot to complete. Failing to do so yields a timeout error. The lock-stealer ensures that the transaction lock is delivered to another waiting connection, if any.

Motivation

This change is motivated by multiple factors:

• Resource management: Each interactive connection holds resources during its lifetime. The incentive for sqld is to keep resource usage low. Therefore, the protocol should be designed in such a way that minimizes resource squatting.
• We do see a lot of hrana stream expired in prod, which leads to user errors. That is not desirable, and we must address that somehow.
• The current protocol expects that the client will be well-behaved and close their stream when they are done with it, which I believe is a fundamental design flaw, for clients, by nature, need to be trusted.
• The current model limits the transaction duration, which limits the users, especially when evaluating/using the shell to interact with the database.

Solution

1. Default to non-interactive transaction:
   On the hrana side, only return keep a stream open if the connection is not in an auto-commit state (i.e., the connection is currently in a transaction state). In other words, if the connection is not in a transaction state after executing a query, the default behavior is to close it. In that case, no baton is returned, and the resources are released. On subsequent queries, a new connection will be opened.
   This solves the following issues:

• A connection is by default not used for longer than necessary, alleviating resource contention on the server
• The client is less likely to hold to a stream for too long, reducing the risk that their stream expires.
• The interactive transaction issue is solved straightforwardly: if a transaction is still opened, a baton is returned, and the client uses it for subsequent queries. No transaction => no baton => no problem.
• This API is less likely to be misused by a client not closing the streams properly

It may be desired cases. I'll use interactive connections outside of a transaction, but it would be an opt-in rather than an opt-out as it is today.

2. Hard and soft transaction limits
   The current transaction timeout is a hard timeout. It is enforced in two ways:

• The lock stealer: if a transaction runs too long, and another transaction is waiting for the lock, the waiting transaction can steal the lock and timeout the long-running transaction. I call that a soft timeout, as if no other transaction is trying to acquire the lock, a transaction is allowed to run forever.
• Transaction self-monitoring: upon its execution, a transaction monitors itself to ensure that they don't exceed its allocated transaction time. This is a hard timeout since it happens no matter if another connection is trying to acquire the lock.

We should increase the timeout to let transactions run longer if there is no contention. We will still need to limit transactions so as not to prevent checkpoints, but we can also let them run for as long as a stream is open if the connection is uncontended.

3. Kick the watchdog*
   In the CLI scenario, keeping a stream open for an extended period may be desirable. We can allow that by having the client periodically kick the stream watchdog. This may require exposing a Noop operation in the hrana protocol, whose sole purpose is to keep the stream open.

The proposal names no implementation files, tests, or entry points. Start by locating the hrana stream, transaction-timeout, watchdog, and CLI protocol code, then review existing tests for stream expiry and transaction limits. Done should include an agreed design and corresponding behavior for default stream closing, contention-aware timeouts, and watchdog keep-alives.