Hi there. I have a problem with exposed 1.0.0-beta...

# exposed

[d.bellingroth]

Hi there. I have a problem with exposed 1.0.0-beta4. If I use r2dbc connection pool, somehow transactions seem to be reused between different transaction blocks. In our application, we use postgresql advisory locks to coordinate some work. It could for example look like this:

suspend fun main() {
    R2dbcDatabase.connect("r2dbc:pool:<postgresql://postgres:postgres@localhost:54325/postgres>")

    val coroutineScope = CoroutineScope(SupervisorJob() + Dispatchers.IO)

    val lockId = 1337L

    val jobs = mutableListOf<kotlinx.coroutines.Job>()

    for (i in 1..10) {
        jobs += coroutineScope.launch {
            suspendTransaction {
                println("Task $i trying to acquire lock with ID $lockId")
                exec("SELECT pg_advisory_xact_lock(?)", listOf(
                    LongColumnType() to lockId
                ))
                println("Task $i acquired lock with ID $lockId")

                async {
                    println("Task $i started")
                    delay(1000) // Simulate some work
                    println("Task $i completed after 1 second")
                }.await()

                exec("SELECT pg_advisory_unlock(?)", listOf(
                    LongColumnType() to lockId
                ))
                println("Task $i released lock with ID $lockId")

            }
        }
    }

    jobs.joinAll()
}

Without the connection pool everything works as expected and the jobs wait for each other. But as soon as I enable the connection pool all jobs are able to get the advisory lock at the same time and run in parallel.

I just wanted to make sure, that I'm not getting something totally wrong. Am I using something not as intended?