IsAutoCommit = false & transactionIsolation = "Transaction_Serializable" result in a PSQLException #1575
Comments
|
Anyone maybe has an idea how to handle this situation? Not upgrading the package anymore does not seem to good. Maybe i'm also just doing it wrong? Any hints appreciated. Thanks. |
|
@AlbRoehm few questions:
|
|
Hi @Tapac, I did the following tests with
|
|
The only way to change the outcome of the test was |
|
Found possible cause. I will ask you to check if the problem is gone after the next release (possibly today) |
|
Please check 0.41.1 version and let me know if the issue was solved |
|
Hey, sorry to inform you that the problem is still not fixed. If i use if i 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 |
|
Hmm. it looks like |
|
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. |
|
I use the @Test
fun `Create a new Connection after it was closed, PSQLException`() = testCoroutine {
val db = DatabaseMock(EnvironmentService())
createEntry()
delay(TimeUnit.SECONDS.toMillis(31))
createEntry()
}
private suspend fun getPSQLVersion() = dbQuery {
exec("SELECT VERSION();") { it.next(); it.getString(1) }
}
private suspend fun createEntry() = dbQuery {
RandomEntity.new { test = UUID.randomUUID().toString() }
}
}
object RandomTable: IntIdTable(){
var test = text("text")
}
class RandomEntity(id: EntityID<Int>): IntEntity(id){
companion object : IntEntityClass<RandomEntity>(RandomTable)
var test by RandomTable.test
}
suspend fun <T> dbQuery(dispatcher: CoroutineDispatcher = Dispatchers.IO, block: suspend Transaction.() -> T): T =
newSuspendedTransaction(
dispatcher,
transactionIsolation = Connection.TRANSACTION_SERIALIZABLE
) { block() }
class DatabaseMock(envVars: IEnvironmentService) : IDatabase {
private val isolationLevel = "TRANSACTION_SERIALIZABLE"
private val driverName = "org.postgresql.Driver"
init {
setupDatasource(envVars)
}
private fun setupDatasource(envVars: IEnvironmentService) {
val dataSource = hikari(envVars)
org.jetbrains.exposed.sql.Database.connect(dataSource)
transaction(Connection.TRANSACTION_SERIALIZABLE, 1) {
val schema = Schema(envVars["DATABASE_SCHEMA"], envVars["DATABASE_USER"])
SchemaUtils.createSchema(schema)
SchemaUtils.setSchema(schema)
SchemaUtils.create(RandomTable)
}
}
private fun hikari(envVars: IEnvironmentService): HikariDataSource {
val hikariConfig = HikariConfig().apply {
driverClassName = driverName
jdbcUrl = envVars["DATABASE_URL"]
schema = envVars["DATABASE_SCHEMA"]
username = envVars["DATABASE_USER"]
password = envVars["DATABASE_PASSWORD"]
maximumPoolSize = 2
isAutoCommit = false
maxLifetime = 30020
transactionIsolation = isolationLevel
}
hikariConfig.validate()
return HikariDataSource(hikariConfig)
}
} |
|
Is it really fails with same transaction level set into hikari config and into |
|
I've go the exception when I have different isolation levels and (!) when schema is defined on pool. Can you, just of curious, try the same code with that driver? |
It fails with the same transaction level in hikari and in transaction. But i got the feeling that either one is setting it not, otherwise i don't get why hikari wants to reset the connection. |
I tried that driver and the test did not fail, but if i understand it correctly there is also no connection pool involved that closes and reopens connections. So I'm not sure if it is comparable. |
|
I think i found a pretty interesting thing that is related to the hikariConfig.schema property. I seems like there is something going wrong when hikari is creating the connection when the schema is set. It might be the case that with setting the schema property the transaction is already opened? So i created seperate files for this case and i checked:
|
|
After looking some more into the schema issue i found this ticket in the hikari repo. brettwooldridge/HikariCP#1633 In case you still want to have a look, i added a test for this scenario too in the repo |
Following test will always fail with a
PSQLException : Cannot change transaction isolation level in the middle of a transactionChanging 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:
gradle.build.kts:
The text was updated successfully, but these errors were encountered: