0

Is the following construct blocking or locking in any possible way? Is it a proper way of using R2DB? If not, how else to process all the records of a table in a reactive way?

My concern is what is happening with the database connection before the whole flux is consumed, with the table, and with the Reactor threads. If I am blocking the thread, or if I keep the table locked, or the database connection blocked.

The purpose is to write a batch processing of all the rows from a table. For each row I want to perform an activity, which consists of fetching data from an external web service and in the end overwriting the original invoice in the very same table.

The assumption is that the single-row-processing method (in this example recalculateInvoice()) is fully reactive.

import org.springframework.r2dbc.core.DatabaseClient;
import org.springframework.stereotype.Repository;
import reactor.core.publisher.Mono;
import lombok.RequiredArgsConstructor;

@Repository
@RequiredArgsConstructor
public class DbConnector {
    private final DatabaseClient databaseClient;

    public Mono<Void> batchProcessing() {
        return databaseClient
                .sql("SELECT * FROM invoice")
                .fetch()
                .all()
                .delayElements(Duration.ofSeconds(10)) 
                // a costly operation with the data ...
                .then()
        ;
    }
}
Honza Zidek
  • 9,204
  • 4
  • 72
  • 118
  • Apart from that print, everything seems fine. – lkatiforis Oct 15 '21 at 13:56
  • @lkatiforis That `println` is there on purpose, to show that the processing is costly. I'd like to know what is happening with the DB connection if I process the data from the `Flux` very slowly. – Honza Zidek Oct 15 '21 at 15:07
  • Use `delayElements(Duration delay)` to simulate a costly operation. – lkatiforis Oct 15 '21 at 15:12
  • @lkatiforis I changed the sample code in the question. What happens to the database connection and to the database table, if I do something like this? – Honza Zidek Oct 15 '21 at 16:11

1 Answers1

1

Your code snippet is fully reactive. It does not block any thread or the database. This is the whole idea of using R2DBC.

Under the hood, something like this will be performed by the R2DBC driver:

Flux.usingWhen(connectionFactory.create(),
        connection -> 
            Flux.from(connection.createStatement("SELECT * FROM invoice").execute()),
        Connection::close);

The database connection will be closed as soon as the pipeline completes or an error occurs.

You could read here about how backpressure is applied on such queries.

lkatiforis
  • 5,703
  • 2
  • 16
  • 35
  • "The database connection will be closed as soon as the pipeline completes." That's exactly my concern. "As soon as" - it means it stays open until I go through all the rows? And does it mean that one thread is occupied for the connection all the time? – Honza Zidek Oct 15 '21 at 19:22
  • The connection remains open, but this does not mean that the threads are blocked. You can capture the thread dump of your application while query is running. – lkatiforis Oct 15 '21 at 19:53