Spring Cancelling jdbc queries (enhancement/discussion)

Affects: Any spring versions having

Hello,

As I did not find anything about it, I ask it here.

Problem

Why there isn't any way to cancel a query easily using the JdbcOperations, NamedParameterJdbcOperations or JdbcClient ?

For sure, there is queryForStream that provides a cancelable Stream, that only cancels after the query execution, and is not using the PreparedStatement.cancel() to interrupt the maybe long running query.

Solution idea (kotlin)

We can do it using a PreparedStatementCallback within (NamedParameter)JdbcOperations.execute(...) to plug the PreparedStatement.cancel() within the continuation.invokeOnCancellation triggered when the continuation is cancelled.

The following is able to forward the coroutine cancel signal. But there is still another issue: we are not able to cancel getting the connection (sadly) — maybe play with the connection timeout depending on the needs. But, by being able to cancel at least after obtained the connection, it prevents to overload the database with useless queries.

suspend fun <T> NamedParameterJdbcOperations.coExecute(sql: String, params: Map<String, *>, context: CoroutineContext = Dispatchers.IO, rowMapper: (ResultSet) -> T): List<T> = withContext(context) { // Force to switch of context to easily not block the caller thread
    suspendCancellableCoroutine { continuation ->
        continuation.resumeWith(runCatching {
            this@coExecute.execute(sql, params) { statement ->
                continuation.invokeOnCancellation { error ->
                    // When the coroutine is cancelled or had an error, do cancel the statement
                    // An potential issue here is that the statement cancel() is a blocking method, that is not the preferred way by kotlin. It should instead be non-blocking and fast. Why not wrapping the cancellation in a non blocking `launch { cancel() }`
                    if (error != null) {
                        statement.cancel()
                    }
                }
                if (continuation.isCancelled)
                // When getting inside this prepared statement callback, we acquired the connection, and it's possible that the continuation has been cancelled in between.
                // Then we don't execute the query itself for nothing by just returning an empty list (whatever else, it won't be used at the end).
                    emptyList()
                else
                    RowMapperResultSetExtractor { row, _ -> rowMapper(row) }.extractData(statement.executeQuery())
            }!!
        })
    }
}

TL;DR:

It would be great to have an easiest way of cancelling queries, with by example having a JdbcOperations.asyncQuery giving a Future<List<T>> (or any other interface to propose cancellation). That way, it's much more simple to cancel queries while keeping a comprehensible user interface.

I can make a PR, but I prefer talking a little before to see if it could be acceptable and discuss about other potential issues.

Comment From: sdeleuze

If we provide something here, that should probably be not Kotlin specific. Also when Spring provides a Coroutines based-feature, it is to translate a Reactive concept so more tied to R2DBC than JDBC. So I would maybe suggest to discuss this feature from a R2DBC angle with @simonbasle and/or @mp911de.

For JDBC, not sure how we would expose that.

Comment From: mp911de

In R2DBC we always operate on a stream whose subscription can be cancelled at any point. Focusing on the driver, Postgres and SQL Server drivers stop consuming the cursor upon cancellation.

There is also a way to cancel the server-side query but taking pipelining into account, a cancellation signal can always target a later query depending on when the cancellation actually happens.

Comment From: jhoeller

I don't see much we can do about this with JDBC since Statement.cancel() is barely supported among JDBC drivers and even throws an exception when not supported, incurring a cost. In queryForStream, we can only return the Stream once the initial query execution has completed, with no chance for cancellation to have an effect.

Pretty much the only case where cancellation could make sense is async queries as suggested. However, even that would be rather involved with JDBC, having to prepare a special Future that tries to call cancel() on a tracked Statement in the execution thread... with no reliable semantics across database drivers.

All in all, we are not going to introduce this at the JdbcTemplate level, and it does not seem to be common enough to justify a dedicated AsyncJdbcTemplate. FWIW we used to have an AsyncRestTemplate next to RestTemplate which we eventually deprecated and removed. Such asynchronous pipelines really suggest reactive processing instead, e.g. with WebClient or R2DBC.

Comment From: Chuckame

@mp911de Do you know where I can find such information about the risk of cancelling a future query ? We are using the drivers org.postgresql:postgresql:42.7.1 and mysql:mysql-connector-java:8.0.33.

We have a service with thousands of calls per second, where we have only 75 connections on 5 instances, each http call is making multiple sql queries.

Comment From: Chuckame

As far as we understood, pgsql and mysql jdbc drivers are cancelling the current executing query of the connection's thread. But if the cancel happens and is a little late (let's say because of high cpu usage), then the cancelled query is maybe finished and another query is running on the same connection, so the same db server-thread, so the another query will be cancelled.