Running out of connections while using non-default isolation level with SpringTransactionManager #1673
Description
Hi,
I have spotted (and am able to reproduce) this issue using:
exposed-spring-boot-starter 0.41.1postgres 11.6
The issue can be seen most prominently while using a connection pool along with Exposed to connect to the database, along with org.jetbrains.exposed.spring.SpringTransactionManager. Have observed that using a connection pool with maximum connections property set to 1, causes an issue while using non-default isolation levels. Basically, SpringTransactionManager will timeout in trying to acquire a connection whenever a transaction is being made in this case with overridden isolation level.
Code reproducing the issue
Please refer to https://github.com/dantinkakkar/exposed-spring-connection-issue for more examples.
package me.dantink.application
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import org.jetbrains.exposed.spring.SpringTransactionManager
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.Transaction
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.transactions.transaction
object Cities : Table() {
val id = integer("id").autoIncrement() // Column<Int>
val name = varchar("name", 50) // Column<String>
override val primaryKey = PrimaryKey(id, name = "PK_Cities_ID")
}
private fun hikari(): HikariDataSource {
val config = HikariConfig()
config.driverClassName = "org.postgresql.Driver"
config.jdbcUrl = "jdbc:postgresql://localhost:5432/exposed_bug"
config.username = "postgres"
config.password = "postgres"
config.maximumPoolSize = 1
config.connectionTimeout = 5000L
config.isAutoCommit = false
config.validate()
return HikariDataSource(config)
}
fun main() {
val txnManager = SpringTransactionManager(hikari())
val txn: Transaction = txnManager.newTransaction(isolation = 8)
Cities.insert {
it[name] = "Vladivostok"
}
txn.commit()
txn.close()
}
Dependencies:
implementation("org.jetbrains.exposed:exposed-spring-boot-starter:0.41.1")
implementation("org.postgresql:postgresql:42.1.4")
implementation("org.slf4j:slf4j-api:2.0.6")
implementation("ch.qos.logback:logback-classic:1.4.5")
implementation("com.zaxxer:HikariCP:5.0.1")
Stacktrace:
Exception in thread "main" java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 5002ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
at org.jetbrains.exposed.sql.Database$Companion$connect$3.invoke(Database.kt:141)
at org.jetbrains.exposed.sql.Database$Companion$connect$3.invoke(Database.kt:138)
at org.jetbrains.exposed.sql.Database$Companion$doConnect$3.invoke(Database.kt:126)
at org.jetbrains.exposed.sql.Database$Companion$doConnect$3.invoke(Database.kt:127)
at org.jetbrains.exposed.sql.Database.metadata$exposed_core(Database.kt:31)
at org.jetbrains.exposed.sql.Database$vendor$2.invoke(Database.kt:44)
at org.jetbrains.exposed.sql.Database$vendor$2.invoke(Database.kt:43)
at kotlin.SynchronizedLazyImpl.getValue(LazyJVM.kt:74)
at org.jetbrains.exposed.sql.Database.getVendor(Database.kt:43)
at org.jetbrains.exposed.sql.Database$dialect$2.invoke(Database.kt:48)
at org.jetbrains.exposed.sql.Database$dialect$2.invoke(Database.kt:47)
at kotlin.SynchronizedLazyImpl.getValue(LazyJVM.kt:74)
at org.jetbrains.exposed.sql.Database.getDialect(Database.kt:47)
at org.jetbrains.exposed.sql.Database$Companion.getDefaultIsolationLevel(Database.kt:210)
at org.jetbrains.exposed.spring.SpringTransactionManager.getDefaultIsolationLevel(SpringTransactionManager.kt:42)
at org.jetbrains.exposed.spring.SpringTransactionManager.initTransaction(SpringTransactionManager.kt:110)
at org.jetbrains.exposed.spring.SpringTransactionManager.doBegin(SpringTransactionManager.kt:53)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
at org.jetbrains.exposed.spring.SpringTransactionManager.newTransaction(SpringTransactionManager.kt:101)
at org.jetbrains.exposed.sql.transactions.TransactionManager$DefaultImpls.newTransaction$default(TransactionApi.kt:54)
at me.dantink.application.ServerKt.main(Server.kt:33)
at me.dantink.application.ServerKt.main(Server.kt)
In this case, trying to perform any transaction will fail, as the Exposed SpringTransactionManager reaches a state where it is trying to acquire 2 connections (one acquired via the super.doBegin() call to AbstractPlatformTransactionManager, and one via connector() when it tries to fetch the db.dialect property to return the default isolation level.
This issue does not happen while using the default transaction manager (i.e., without any exposed-spring usage) even while using a connection pool.
UPDATE (Adding more context)
You don't need a connection pool to reproduce this issue. It can be reproduced even with SingleConnectionDataSource() + SpringTransactionManager. Working examples reproducing this bug can be found here: https://github.com/dantinkakkar/exposed-spring-connection-issue