Transactions and CoroutineContext in Kotlin

[Anders Sveen]

Hi,

We're using JDBI with the Kotlin additions, but as we switched to async for several methods we realized that jdbi.inTransaction {...} etc. uses a ThreadLocal to keep the transaction. Since Kotlin co-routines jump threads, this of course won't work. 

I've been looking and can not really find much. Is there something that I can use that will handle transactions, just with CoroutineContext instead of ThreadLocals?

Thanks,

Anders,

[Steven Schlansker]

Hi Anders,

We'd love to support Kotlin coroutines, but as you point out currently we use plain Java ThreadLocal.

It looks like Kotlin has the possibility to plumb ThreadLocal into the coroutine context:
https://kotlinlang.org/docs/coroutine-context-and-dispatchers.html#thread-local-data

however, to my knowledge, nobody has explored what code would be necessary to make this work.
Feel free to tinker with it and see if you can make anything work! We'd be happy to accept reasonable PRs that implement this feature.

> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/b80b5747-8507-459d-a417-d46341afbfaen%40googlegroups.com.

[Anders Sveen]

Thanks for the pointers, I might just do that. :)

My current thinking would be that I would need a class CoroutineJdbi that extends Jdbi and overrides withHandle/withExtension . Is that enough? Any other angles to attack this from?

Regards,

Anders,

[Brian McCallister]

Rather than overiding them, the behavior might already be achievable using current extension mechanics: see if it can be done with a HandleSupplier directly. 

If it cannot, I would probably replace threadHandleSupplier with something that abstracts over thread locals or contexts. Given getting this behavior would probably be a plugin, this would need to become something configurable on the Jdbi instance, rather than a final thread local.

-Brian

To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/2bd9444d-46de-4233-877c-ab1c5c022fc2n%40googlegroups.com.

[Brian McCallister]

I don't grok kotlin coroutine semantics, but if a coroutine can ever migrate threads then things will get tricky. IIRC, JDBC drivers are not required to be thread safe. If it cannot, it amounts to managing N connections per thread instead of one, which is definitely doable.

-Brian

[Anders Sveen]

I have to admit I am not totally up to speed with co-routines myself, but I am fairly certain it will work. Putting something on the co-routine context just makes sure the connection/work is available to the code when it resumes on a different thread. It does not mean it will be used simultaneously on multiple threads. A naive view is that it is just like ThreadLocal, but only on one thread at a time as your task "jumps around". 

Well... Unless you actually tell it to do it concurrenctly it, but that would get you into (different) problems with ThreadLocal as well.

I tried to find some pointers on how to work/activate HandleSupplier specifically, but found very little. Do you have any pointers? Just FYI, we are only using the JDBI API, not annotations etc etc...

Ideally I would like something like inTransaction {}, just CoroutineContext aware. And ideally skipping over the ThreadLocal as that can lead to some nasty issues if it is bound to the thread unintentionally.

Regards,

Anders,

[Steven Schlansker]

> On Nov 2, 2022, at 12:48 AM, Anders Sveen <anders....@gmail.com> wrote:
>
> I have to admit I am not totally up to speed with co-routines myself, but I am fairly certain it will work. Putting something on the co-routine context just makes sure the connection/work is available to the code when it resumes on a different thread. It does not mean it will be used simultaneously on multiple threads. A naive view is that it is just like ThreadLocal, but only on one thread at a time as your task "jumps around".
>
> Well... Unless you actually tell it to do it concurrenctly it, but that would get you into (different) problems with ThreadLocal as well.
>
> I tried to find some pointers on how to work/activate HandleSupplier specifically, but found very little. Do you have any pointers? Just FYI, we are only using the JDBI API, not annotations etc etc...

Unfortunately there's not much guidance on the internals of Jdbi. If you have more specific questions we can try to answer them. To hack on it, I would start by branching Jdbi and trying to just replace the ThreadLocals with context-locals outright, and see if you can get anything working. Once you have a prototype then we could figure out a nicer way to put it in the API.

> Ideally I would like something like inTransaction {}, just CoroutineContext aware. And ideally skipping over the ThreadLocal as that can lead to some nasty issues if it is bound to the thread unintentionally.

Yes, ideally this change would only affect internals, and by e.g. installing a KotlinCoroutinePlugin then Jdbi knows how to do it otherwise transparently to the end-user.

> To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/3591f244-78de-43e1-a24a-65d4343ca08en%40googlegroups.com.

[Henning Schmiedehausen]

Hey Anders,

Thank you for raising this issue here. I did some work in the current snapshot (which will become 3.35.0) that removes configuration threadlocals from the handle. I could take a similar stab at this for the other thread locals, however I would need a test case / use case to work against (I have some glancing familiarity with Kotlin but not really deep knowledge, so I don't know much about how Coroutines work). 

Happy to work with you on this; Jdbi and Kotlin are a great fit and I would like to work this well.

-h

[Henning Schmiedehausen]

Some JDBC drivers (well the three that I looked at) are either serializing threads or at least tolerate multiple threads using a single connection as long as there is only one thread at a time. So handing a connection / handle from one thread to the next should be ok as long as only one thread at a time uses them.

-h

[Anders Sveen]

Hi Henning,

Great! I intend to look at this, but I am a bit swamped right now. 

I did have a quick peek around the code though, and I am not sure but maybe the "carrying" mechanism should be tied to the TransactionHandler? I am quite new to the internals of the JDBI codebase though, so I might be connecting the wrong dots based on my previous experiences.

I don't have a full example now, but the issue would be to figure out a way to "carry" the handle across calls just like thread locals. I guess this Kotlin "converter" for ThreadLocal to CoroutineContextElement is relevant to understand how something like it should work. https://kotlinlang.org/api/kotlinx.coroutines/kotlinx-coroutines-core/kotlinx.coroutines/as-context-element.html

I'll let you know if I find the time to look at it deeper. :)

Regards,

Anders,

[Henning Schmiedehausen]

Looking through the code a bit, I would need to understand better how you are using co-routines and handles. An example (ideally of a failing piece of code!) would help me a lot.

If you are *not* using the Extension framework (and inside Jdbi today, there exists only the SqlObject extension), then the thread local should not be an issue for you:

- any of the useHandle, withHandle, inTransaction or withTransaction operations ends up in withHandle(HandleCallback)

- this method will try to acquire an existing handle through the threadlocal. This can happen when you use a construct like this:

@Test
public void testUseHandleUsesSameHandle() {
    Jdbi jdbi = h2Extension.getJdbi();
    jdbi.useHandle(handle1 ->
        jdbi.useHandle(handle2 ->
            assertThat(handle2).isSameAs(handle1)));
}

So if you call nested operations on the same thread, you will receive the same handle. That is intentional and, unless you plan on executing multiple operations concurrently on different connections (hence also different handles), passing the handle around is the right thing to do. Most databases only support per-connections transactions (I would not be shocked to learn that some of the commercial offerings do cross-connection transactions) otherwise you have a distributed transaction which works if you replace the transaction handler in the Jdbi with one that supports distributed transactions. 

You can move the handle from one thread to another (so coroutines should work in theory), but you need to coordinate thread access to them (only one thread can use them at a time, otherwise funny things might happen) and your connection (which is a single TCP connection to the database) will most likely serialize the threads.

SQLObject extensions work in a similar way. As long as you do not try to do anything against the nature of transactions (which are attached to connections, which in turn are single-strand-of-execution), I don't think there should be a problem. What you can not do is creating an object that uses a single handle (like an attached sql object) and then use it in coroutine scope for parallel execution. That will in any case re-serialize your operations on the database connection and most likely lead to major confusion of the handle state as it cycles in and out of parallel execution of the sql object extension methods.

Some code of what you are trying to do would be super-helpful.

-h

[Anders Sveen]

Hi Henning,

I have had the time to play around a little bit, but no real progress. Maybe it can serve as an example, but I totally understand if it is too incomplete to do anything more with.

First of all: Any way I tried to approach it I ended up having problems because JDBI is not written in Kotlin and thus there are no suspend implementations of the functions. Suspend will have to be propagated (like in JDBI.inTransaction(...)) through all the code which ended up being an impossible task, at least with my limited knowledge of the codebase. This might be a blocker and not sure there are good ways around it.

I reverted to find a way to illustrate the problem (in a test), and got the tests to nearly work. But I am having a hard time making sure that the executions run on separate threads. With coroutines it is possible, but not guaranteed. Maybe someone out there that knows coroutines better than me will have a bright idea. :)

Like I said, incomplete, but hopefully illustrative: https://github.com/anderssv/jdbi/blob/handle-coroutines/kotlin/src/test/kotlin/org/jdbi/v3/core/kotlin/coroutines/CoRoutineContextTest.kt

Regards,

Anders,