How to use the cursor in a multithreaded environment?

[TheDeadOne]

I am developing a reactive application that needs to lazily consume a large amount of data from a database and make a stream from it, processed by at least three thread pools. I can open a session before stream materialization and close it after stream consumption but SqlSession isn't thread-safe, so I am afraid of the consequences. How to do it right? Is it possible?

[Guy Rouillier]

Use a session pool.  I had excellent results using SqlSessionManager, but your runtime environment will determine what sort of connection pools are available.  As long as you obtain a session/connection at the start of your method, and return it at the end, then your session pool will be fine.  Where you run into trouble is when you attempt to use the *same* connection simultaneously across multiple threads.

--

Guy Rouillier

On 6/30/2021 9:12:39 AM, "TheDeadOne" <sputte...@gmail.com> wrote:

I am developing a reactive application that needs to lazily consume a large amount of data from a database and make a stream from it, processed by at least three thread pools. I can open a session before stream materialization and close it after stream consumption but SqlSession isn't thread-safe, so I am afraid of the consequences. How to do it right? Is it possible?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/8f41f191-d72a-42b3-b710-e7c7c0ae7b17n%40googlegroups.com.

[TheDeadOne]

The fact of the matter is that the cursor is opened in one thread, the data is converted in another, and consumed in the third. I cannot guarantee to open and close a session within the same method. Part of the execution is not under my control at all, as it takes place in the framework code. This code works in web service with a highly concurrent environment and tons of external integrations. I don't see a way to apply the SqlSessionManager. Probably, I just don't know something.

четверг, 1 июля 2021 г. в 09:15:44 UTC+3, Guy Rouillier:

[Guy Rouillier]

Going to be difficult to help any further without seeing some working code.  You say "the cursor is opened in one thread, the data is converted in another", what do you mean by converted?  As long as you retrieve the raw data (and put it into Java objects) in the same thread in which the SqlSession is opened, you'll be fine.  You can then pass the Java objects (including Collections of them) onto other threads for further processing without issue.

On Fri, 2021-07-02 at 22:50 -0700, TheDeadOne wrote:

The fact of the matter is that the cursor is opened in one thread, the data is converted in another, and consumed in the third. I cannot guarantee to open and close a session within the same method. Part of the execution is not under my control at all, as it takes place in the framework code. This code works in web service with a highly concurrent environment and tons of external integrations. I don't see a way to apply the SqlSessionManager. Probably, I just don't know something.

четверг, 1 июля 2021 г. в 09:15:44 UTC+3, Guy Rouillier:

Use a session pool.  I had excellent results using SqlSessionManager, but your runtime environment will determine what sort of connection pools are available.  As long as you obtain a session/connection at the start of your method, and return it at the end, then your session pool will be fine.  Where you run into trouble is when you attempt to use the *same* connection simultaneously across multiple threads.

--

Guy Rouillier

On 6/30/2021 9:12:39 AM, "TheDeadOne" <sputte...@gmail.com> wrote:

I am developing a reactive application that needs to lazily consume a large amount of data from a database and make a stream from it, processed by at least three thread pools. I can open a session before stream materialization and close it after stream consumption but SqlSession isn't thread-safe, so I am afraid of the consequences. How to do it right? Is it possible?

-- 

--

Guy Rouillier

[TheDeadOne]

A simplified example of what this might look like:

public class SomeController extends Controller {

    public CompletionStage<Result> someAction() {

        return someService.doHairyStuff()

            .thenApplyAsync(dataStream -> ok().chunked(dataStream), httpExecutionContext);

    }

}

public class SomeService {

    public SomeReactiveType<SomeByteBuff> doHairyStuff() {

        return someRepository.fetchDataFromDb()

            .thenCompose(dataStream -> makeHttpCallToExternalService(dataStream), someExecutionContext)

            .thenComponse(dataStream -> fetchSomethingFromKafka(dataStream), anotherExecutionContext)

            .thenCompose(dataStream -> composeAndTransformData(dataStream), oneMoreExecutionContext);

    }

}

public class SomeRepository() {

    public class Cursor<SomeEntity, ?> fetchDataFromDb() {

        return ComplatableFuture.supplyAsync(() -> mybatisMapper::selectBillionsOfRows, databaseExecutionContext);

    } 

}

Chains of CompletionStages may be long and in every called method may do more and more calls that add data to dataStream or transform it somehow. CompletionStage that returns from someAction is form HTTP response by web framework and the first part of this response consumed by client long before cursor returned last entity. And all the time session must stay opened and the cursor must produce data, row by row, as the client reads. The idea is to form a very large HTTP response without wasting more memory than on one entity. Of course, the data stream may be closed at any point of execution, including in the framework code. Accordingly, the session should be closed at the same moment.

суббота, 3 июля 2021 г. в 12:54:11 UTC+3, Guy Rouillier: