Exposed: IsAutoCommit = false & transactionIsolation = "Transaction_Serializable" result in a PSQLException
Following test will always fail with a PSQLException : Cannot change transaction isolation level in the middle of a transaction
Changing the autoCommit = false to true will make this behavior disappear, but since what if have read so far, autoCommit should always be set to false for exposed.
This behavior was not present for version 0.37.3
Testcase:
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import kotlinx.coroutines.*
import kotlinx.coroutines.test.resetMain
import kotlinx.coroutines.test.setMain
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction
import org.jetbrains.exposed.sql.transactions.transaction
import org.junit.jupiter.api.*
import java.sql.Connection
import java.util.concurrent.*
import javax.sql.DataSource
@ExperimentalCoroutinesApi
@DelicateCoroutinesApi
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
class DatebaseTest {
private val mainThreadSurrogate = newSingleThreadContext("Coroutine thread")
@BeforeAll
fun setUp() {
Dispatchers.setMain(mainThreadSurrogate)
}
@AfterAll
fun tearDown() {
Dispatchers.resetMain() // reset the main dispatcher to the original Main dispatcher
mainThreadSurrogate.close()
}
@Test
fun `Create a new Connection after it was closed, PSQLException`() = testCoroutine {
val db = DatabaseMock(EnvironmentService())
db.getPSQLVersion()
delay(TimeUnit.SECONDS.toMillis(31))
db.getPSQLVersion()
}
private suspend fun DatabaseMock.getPSQLVersion() = this.dbQuery {
exec("SELECT VERSION();") { it.next(); it.getString(1) }
}
}
class DatabaseMock(envVars: IEnvironmentService) : IDatabase {
private val ISOLATION_LEVEL = "TRANSACTION_SERIALIZABLE"
private val DRIVER_NAME = "org.postgresql.Driver"
init {
setupDatasource(envVars)
}
private fun setupDatasource(envVars: IEnvironmentService): DataSource {
val dataSource = hikari(envVars)
val database = org.jetbrains.exposed.sql.Database.connect(dataSource)
// database.transactionManager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE
transaction(Connection.TRANSACTION_SERIALIZABLE, 1) {
val schema = Schema(envVars["DATABASE_SCHEMA"], envVars["DATABASE_USER"])
SchemaUtils.createSchema(schema)
SchemaUtils.setSchema(schema)
}
return dataSource
}
private fun hikari(envVars: IEnvironmentService): HikariDataSource {
val hikariConfig = HikariConfig().apply {
driverClassName = DRIVER_NAME
jdbcUrl = envVars["DATABASE_URL"]
schema = envVars["DATABASE_SCHEMA"]
username = envVars["DATABASE_USER"]
password = envVars["DATABASE_PASSWORD"]
maximumPoolSize = 5
maxLifetime = 30020
isAutoCommit = false
transactionIsolation = ISOLATION_LEVEL
}
hikariConfig.validate()
return HikariDataSource(hikariConfig)
}
override suspend fun <T> dbQuery(block: suspend Transaction.() -> T): T =
newSuspendedTransaction(
Dispatchers.IO,
transactionIsolation = Connection.TRANSACTION_SERIALIZABLE
) {
block()
}
}
gradle.build.kts:
val exposedVersion = "0.39.2"
dependencies {
implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-jodatime:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")
implementation("org.jetbrains.exposed:exposed-dao:$exposedVersion")
implementation("com.zaxxer:HikariCP:5.0.1")
implementation("org.postgresql:postgresql:42.3.6")
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 2
- Comments: 16 (6 by maintainers)
Commits related to this issue
- IsAutoCommit = false & transactionIsolation = "Transaction_Serializable" result in a PSQLException #1575 — committed to JetBrains/Exposed by Tapac 2 years ago
Also i think the problem was introduced sometime between 1.1.2022 and release of 0.38.1 where i first started to see it to happen.
Hey, sorry to inform you that the problem is still not fixed.
If i use
transaction(Connection.TRANSACTION_SERIALIZABLE, 1)insetupDatasource()it still breaks immediately with:if i
setupDatabase()only withtransaction()and no arguments the test fails after 31 seconds when hikari is opening new connections with:Even if i don’t define the TRANSACTION_SERIALIZABLE level no where in my code (not in transaction, not in dbQuery, not in hikariConfig) the test is still failing after 31s with `org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
Still only changing to
isAutoCommit = truesolves the issue with following output:Found possible cause. I will ask you to check if the problem is gone after the next release (possibly today)
Hi @Tapac,
I did the following tests with
exposedVersion=0.40.1Dispatchers.setMain(Dispatchers.Default)does not affect the test outcome. Test fails immediately.transaction(Connection.TRANSACTION_SERIALIZABLE, 1)insetupDatasourceor after 31s if i remove the isolation level in that call.transactionIsolation = Connection.TRANSACTION_SERIALIZABLEis not possible since hikari expects a string.transactionIsolation = "8"will give that same result as 2.