Set statement/lock timeouts

[badgerwithagun]

Feature Request

Is your feature request related to a problem? Please describe

JDBC's Statement has a setQueryTimeout method which is a driver-specific mechanism of requesting that statements timeout within a given number of seconds. This is essential for processing that encounters frequent lock contention, particularly on dialects without support for SKIP LOCKED or similar.

It is implemented in a number of driver-specific ways, but can be mimicked with commands, e.g.:

• MySQL: SET innodb_lock_wait_timeout = ? (mysql)
• H2 SET QUERY_TIMEOUT ? (H2)
• SET LOCAL lock_timeout = ... or SET LOCAL statement_timeout = ... (Postgres)

Thread here: https://gitter.im/R2DBC/r2dbc?at=5ed3fdcf89941d051a350548

Describe the solution you'd like

Either at the statement level, like JDBC:

interface Statement {
   default Statement setQueryTimeout(int seconds) {
      throw new UnsupportedOperationException();
   }
   default Statement setLockTimeout(int seconds) {
      throw new UnsupportedOperationException();
   }
}

Or at the connection level (simply executes the appropriate session setup command):

interface Connection {
   default Publisher<Void> setQueryTimeout(int seconds) {
      throw new UnsupportedOperationException();
   }
   default Publisher<Void> setLockTimeout(int seconds) {
      throw new UnsupportedOperationException();
   }
}

In both cases, driver implementations should be expected to emit an exception extending R2dbcTimeoutException if a subsequent command times out.

Describe alternatives you've considered

• Flux.timeout()/Mono.timeout(): The timeout works OK but the connection remains blocked, blocking commit/rollback and Connection.close().
• Calling the appropriate commands as listed above prior to executing statements. However, for my project I support multiple database dialects so it forces my client code to manage what seems more like the task of a driver implementation.

Teachability, Documentation, Adoption, Migration Strategy

Default UnsupportedOperationException methods seem a reasonable move while waiting for drivers to implement.

[mp911de]

Thanks for bringing this issue up. We have an open issue (#159) that addresses transactional attributes in which lock timeouts play nicely.

Care to elaborate what should happen if the query timeout exceeds?

[badgerwithagun]

IMO

in both cases, driver implementations should be expected to emit an exception extending R2dbcTimeoutException if a subsequent command times out

[mp911de]

Makes sense. What about the connection itself? Databases implementing cancellation/interrupt/kill signals could propagate the timeout to the server side but what about those which do not have such a feature? Would the connection be reusable thereafter?

[badgerwithagun]

I'll have a look at the JDBC implementations and see what they do. I do know there is some subtle variation in behaviour between each, but all the ones I've tried do work as designed, in the sense that the query times out and the connection remains usable. The differences are more around the isolation of the timeout setting (e.g. H2 applies it to the entire Connection whereas MySQL and Postgres scope it to only the Statement itself, I believe).

I've had a skim of #159. I'm comfortable with this being at transaction level in theory, but do quite strongly believe that timeouts are not a behaviour that should be left to driver-specific extensions, but covered by the SPI and (relatively) consistently supported.

[mp911de]

Lock timeouts aren’t supported on every database IIRC, but that’s subject for further investigation. A setLockTimeout method that no-ops isn’t different from a not supported timeout attribute in the transaction definition.

Do we need query timeouts on connection-level, statement-love or both? A neat way to keep the connection API rather tight is introducing a ConnectionOption "queryTimout" (similar to the connect timeout) that provides a default an on the statement level we could override the timeout.

We need to pay also attention to cursored executive. The timeout would apply either to a direct query (timeout I canceled upon receiving the first response for the query) or per cursor operation (open cursor, fetch next, close cursor).

[mp911de]

For now, let's include the connection option and connection-bound setter methods. A per-statement timeout suggests additional timeout and cancellation handling which isn't necessarily available for every database. Also, a per-connection setting may interfere with per-statement settings especially when the database has a variable/setting that is switched right before running a statement.

[alampada]

Thank you for your work on R2DBC, it's great!

For those using postgres, it seems it's possible to pass the statement_timeout configuration to the ConnectionFactory as a workaround, until this ticket is implemented.
Sharing example code, in case it's useful for anyone. https://github.com/alampada/pg-spring-r2dbc-timeouts/blob/main/src/main/java/com/example/pgtimeouts/configuration/PgTimeoutsConfiguration.java#L20

[mp911de]

@alampada Care to file a new ticket in R2DBC Postgres to introduce a proper connection factory option statementTimeout that can be configured through the connection URL?

[alampada]

@mp911de of course. pgjdbc/r2dbc-postgresql#381 raised.