Exposed: Support explicit transaction manager

[oshai]

ExplicitTransactionManager (as opposed to ThreadLocalTransactionManager) will allow supporting features that require switching threads inside a transaction. Examples of that are coroutines and thread pools usage inside a transaction.

I think that in order to be able to do that TransactionManager companion object references should be replaced in statements and queries.
I can try to do a priliminary pull request to make the suggestion more clear if that sounds like an approach we would like to take. I guess it might change some interfaces/method signaures.

[Tapac]

I thought about supporting of coroutines in Exposed, but can't imagine real use-cases. Possibly that because I don't work with coroutines in a real project, so if you can share your vision, with short example of how it might be used, then we can discuss implementation details.

[raharrison]

Maybe using it within your own web library https://github.com/ktorio/ktor

Even the samples in that project are making use of Exposed even though it would no doubt start to cause issues under load.

[oshai]

I will give some examples below, but first I would like to say thread local is not a good design choice, and it makes the code less readable which is important for maintainability and especially for open source.
In Stackoverflow you can see this answer for example https://stackoverflow.com/a/44750794/411965
Also, some comments about it can be seen here with relating to Exposed: https://www.reddit.com/r/Kotlin/comments/7z44uk/bits_and_blobs_of_kotlinexposed_jdbc_framework/

And now for examples/ use cases:
Threads - consider the following example:

     transaction {
      // 'select *' SQL: SELECT Cities.id, Cities.name FROM Cities
      val cities = Cities.selectAll()
      //now I would like to create a new thread to fire & forget about action on all cities
     //do some action on other thread
      thread(start = true) {  
      println("running from thread(): ${Cities.selectAll()}") //here I would like to update cities in the backgound for example
      }
    }

I can also give a similar example of coroutines and ui/background thread

[orangy]

Coroutines doesn't magically make anything handle the load. Making Exposed (or any other SQL library) truly asynchronous requires huge amount of work. There are very few attempts, specifically for MySQL an Postgres, and both reimplement client-server protocols and do not use JDBC. If current effort for asynchronous JDBC is successful, and when DB vendors release their drivers, that would make much more sense.

Said that, even with JDBC there are places for improvement. For example, it's connection acquisition, it can be asynchronous. I've filed an issue with HikariCP, but they closed it with the same rationale as above: https://github.com/brettwooldridge/HikariCP/issues/1090

So basically, any SQL library wishing async support needs to wait for java.sql2 to graduate.

[oshai]

@orangy I am not talking here about something that is truly async/reactive. I still think that the usage of ThreadLocal is not a good idea and can be replaced.

[orangy]

For TLS I agree, in squash I don't use TLS, but I don't have time to maintain or develop it, so can't recommend using it over Exposed.

[Tapac]

I don't protect TLS approach, but before starting a refactoring (or creating alternative TransactionManager) we should understand what the main problem with using Exposed with TLS.

From my point of view, many of users do not really need a "entire transaction per request". I mean that if you use database mostly to read data and show it at UI (e.g. render a page of a blog or CMS) then you easily can execute requests in parallel using separate connections/transactions and then combine the results. Somthing like that is prototyped here.

Another case is "run and forget" from above - when you don't need a result of the execution and can call it in another thread.

Both cases can be implemented with current TL Manager just by adding "sugar" functions (and maybe a bit coroutines), but if you have to work with transactions (modify data and entities) then this won't work, imo.

I thought about using async/await for select queries (see example below), but I don't think that this is quite common case:

val fooRecords = FooTable.select{...}.asyncExecute()
... // Another code which don't rely on fooRecords
fooRecords.await().forEach {
// data already loaded
}

From my experience, you call DB and then use the result. The approach when you call/stack all requests at the beginning of a function and then use the results can lower the readability of code and also force you to remember that you should request "everything" before using it or you will be blocked (this how current (sync) approach works).

That's why I asked for examples and use-cases. Coroutines and pools is not a magic and it can even lower your performance/throughput if you use it in a wrong way. As @orangy stated above - only async on driver level can help, but sql2 will not be very soon...

[orangy]

Async at connection acquisition point would solve 80% of issues with async web framework (such as ktor.io). Suspending a coroutine while waiting for a connection from a pool instead of blocking a thread is all most of use cases require. Any asynchronous processing of incoming data records without loading the whole result set in memory or not locking to a single thread will require support from JDBC and driver, and they are not capable of doing it as of now. One can try to employ native clients, e.g. look at PostgreSQL interfaces, but that's again a lot of work for no clear reason.

[dave08]

Vert.x does use async clients, and for connection aquisition and for actual requests... I still wonder if it would be too hard to adapt that? I think for reactive platforms that handle requests async it would be a big plus especially for ktor... It's just a pity that vertx coupled it a bit too much with their framework, and the original lib they base themselves on is in Scala...

[oshai]

Isn't that supported now with explicit db?

[Tapac]

@oshai , no, it's about refactoring TransactionManager to remove ThreadLocal storage

[oshai]

Any plans to implement that?

[Tapac]

Yes, just after datetime refactoring.

[oshai]

Thanks!

בתאריך יום ב׳, 2 ביולי 2018, 23:32, מאת Andrey.Tarashevskiy ‏<
[email protected]>:

Yes, just after datetime refactoring.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/JetBrains/Exposed/issues/255#issuecomment-401925596,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ACj0QSW_W6Pv6Lc8RmEzSUC-z5A20YSJks5uCoNwgaJpZM4SOb6b
.

[doxavore]

In the meantime, I have a case where I need to carry a transaction across a coroutine context. The transaction itself will not be running simultaneously in multiple coroutines. Is there any reason why something like this should be avoided until we have proper support?

fun currentTransactionContext(): CoroutineContext {
    val manager = TransactionManager.manager as? ThreadLocalTransactionManager
    return manager?.currentOrNull()?.let {
        manager.threadLocal.asContextElement(it)
    } ?: EmptyCoroutineContext
}

// Usage:
launch(Dispatchers.Default + currentTransactionContext()) {
    // Do something that has access to outer's existing transaction, if it was in one.
}

[Tapac]

https://github.com/JetBrains/Exposed/wiki/Transactions#working-with-coroutines

[RdeWilde]

This topic mentions setting up a async/reactive client in Vert.x, but from what I understand this never happened, did it?