Hi all, I’m looking for some insight into this iss...

# exposed

[Paul Rule]

Hi all, I’m looking for some insight into this issue I’m having with my Ktor/exposed application…

If I do the following in a KTOR route, after the newSuspendedTransaction block is close, TransactionManager.currentOrNull() returns a not null value - I would expect it to be null.

If I remove the delay() call, it will work as expected.

get("/") {
    require(TransactionManager.currentOrNull() == null) { "1 - transaction should not exist at the start" }
    newSuspendedTransaction {
      require(TransactionManager.currentOrNull() != null) { "2 - transaction should exist now" }
      delay(2000) // Simulate some processing time - if I remove this delay things will work as I expect
    }
    // this next line fails
    require(TransactionManager.currentOrNull() == null) { "3 - transaction should not exist at the end" }
    call.respondText("OK")
  }

To make it even more mysterious I cannot get this scenario to fail in a test - the following code works fine, and TransactionManager.currentOrNull() returns null after the newSuspendedTransaction scope closes.

suspend fun main() {
  val database = TestPostgresDatabase

  val fixtures = Fixtures(database).initialise()

  coroutineScope {
    launch(Dispatchers.IO) {
      require(TransactionManager.currentOrNull() == null) { "1 - transaction should not exist at the start" }
      newSuspendedTransaction {
        require(TransactionManager.currentOrNull() != null) { "2 - transaction should exist now" }
        delay(2000) // Simulate some processing time
      }
      require(TransactionManager.currentOrNull() == null) { "3 - transaction should not exist at the end" }
      println("Transaction completed successfully ${TransactionManager.currentOrNull()}")
    }.join()
  }
}

Can anyone tell me what I’m doing wrong or missing please? What I'm trying to is test if a transaction is already in progress or not using TransactionManager.currentOrNull() so I know when to initialise my connection with row level security parameters. For the most part its working well but there is this one condition where there is a network call - which must result in yielding similar to the delay above, and then I get left with a closed connection...

[Paul Rule]

From what I gather, this is what is happening:
When you call delay() inside the newSuspendedTransaction block, the coroutine gets suspended and may resume on a different thread. However, the transaction state in Exposed is stored in a ThreadLocal variable. Here's what happens:
1. Before delay: Transaction runs on Thread A, ThreadLocal contains the transaction
2. During delay: Coroutine suspends, transaction completes, but ThreadLocal on Thread A still holds the reference
3. After delay: Coroutine resumes on Thread B, but Thread B's ThreadLocal may still contain a stale transaction reference from a previous operation
4. I don't know why tests don't show up this problem but I assume that when running in the KTOR environment a lot more is happening and thus different threads are being used - while in tests no such complexity existed.
The solution appears to be to use <http://Dispatchers.IO|Dispatchers.IO> - eg wrap my transaction with

withContext(<http://Dispatchers.IO|Dispatchers.IO>) { ... }

as in:

get("/") {
    require(TransactionManager.currentOrNull() == null) { "1 - transaction should not exist at the start" }
    withContext(<http://Dispatchers.IO|Dispatchers.IO>) {
        newSuspendedTransaction {
            require(TransactionManager.currentOrNull() != null) { "2 - transaction should exist now" }
            delay(2000) // Simulate some processing time - if I remove this delay things will work as I expect
        }
    }
    // this next line fails
    require(TransactionManager.currentOrNull() == null) { "3 - transaction should not exist at the end" }
    call.respondText("OK")
  }

This seems to work properly - it also seems to be recommend to "*Always use `Dispatchers.IO`* for database operations in Ktor".
While its great I've found a solution to my problem, if anyone has further insight it would be welcome - I'm not sure why this complexity needs to be exposed in the code, and who knows what else I might be missing...

[Adam Chęciński]

@Paul Rule does it work the same when you change it to newSuspendedTransaction(<http://Dispatchers.IO|Dispatchers.IO>) or newSuspendedTransaction(currentCoroutineContext()) ?
Something like this

get("/") {
    require(TransactionManager.currentOrNull() == null) { "1 - transaction should not exist at the start" }
        newSuspendedTransaction(<http://Dispatchers.IO|Dispatchers.IO>) {
            require(TransactionManager.currentOrNull() != null) { "2 - transaction should exist now" }
            delay(2000) // Simulate some processing time - if I remove this delay things will work as I expect
        }
    // this next line fails
    require(TransactionManager.currentOrNull() == null) { "3 - transaction should not exist at the end" }
    call.respondText("OK")
  }

[koji.lin]

maybe related to https://youtrack.jetbrains.com/issue/KTOR-6118/CallMonitoring-SuspendFunctionGun-sometimes-leaks-coroutine-context ?

[Paul Rule]

@Adam Chęciński thanks, it works with Dispatchers.IO but not with currentCoroutineContext()

[Paul Rule]

@koji.lin thanks, I’ll follow that issue

[koji.lin]

https://gist.github.com/kojilin/3fbb5a27563c2ba409f01e41fd4c08ab
Not sure my test/expectation is fully correct or not, but it's weird for me of the behavior that ktor coroutine context + thread local context.

[Paul Rule]

I’m trying to upgrade to ktor 3.2.0 and now my previous fix doesn’t work… always left with a transaction visible which leads to a connection closed error

[Paul Rule]

Without the delay I now get the transaction left so ‘TransactionManager.currentOrNull()’ gives me a non-null value after the block closes

[Paul Rule]

With the delay() included I get a different problem - “Can’t init value outside the transaction”.

[Paul Rule]

I assume it must be a coroutine issue because I haven’t changed anything with exposed. Any tips from anyone?

[Paul Rule]

It seems to happen only when inside a ktor route…