Exposed: Leaking prepared statements

[inikolaev]

While running sample application I noticed that statements are not being closed.

Below is modified example where I insert 1,000,000 records using Exposed API and using JDBC PreparedStatements directly. When using JDBC directly I can manually close statements, but with Exposed API I can see a growing number of PreparedStatements in profiler.

Is there any way to manually close statements?

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.SchemaUtils.create
import org.jetbrains.exposed.sql.SchemaUtils.drop

object Cities : Table() {
    val id = integer("id").autoIncrement().primaryKey() // Column<Int>
    val name = varchar("name", 50) // Column<String>
}

fun main(args: Array<String>) {
    Database.connect("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")

    transaction {
        create(Cities)
    }

    // Kotlin Expose syntax
    (1..1000000).forEach {
        transaction {
            val saintPetersburgId = Cities.insert {
                it[name] = "St. Petersburg"
            } get Cities.id
        }
    }

    // JDBC syntax
    (1..1000000).forEach {
        transaction {
            val stmt = prepareStatement("INSERT INTO cities (name) VALUES (?)")
            stmt.setString(1, "Moscow")
            stmt.execute()
            stmt.close()
        }
    }

    transaction {
        drop (Cities)
    }
}

[Tapac]

On exit from transaction {} block there is a Connection.close() method execution, which will close all underlying statements (in common case). Also if DBMS doesn't support multiple opened result sets (e.g. sqlite), last prepared statement will be closed before execution of new one.

I did profile of both parts (Kotlin/JDBC syntax) and can't see much difference in allocation of PreparedStatements and its lifecycle within a heap.

Can you provide profiler snapshot to investigate leaking source?

[inikolaev]

I will create snapshots later today. Meanwhile I have a screenshot from YourKit, that is why I started looking into that:

The point where the graph flattens is when the JDBC syntax starts working and at least it looks like all statements opened by Kotlin are not being closed.

As for multiple opened result sets - is it correct that it has to be supported for all types of statements? Reading documentation here it looks like it should only be used for CallableStatement. For other types there is a different flag, if I understand correctly.

[Tapac]

Ouch, you are absolutely right that we used wrong flag to detect when previous statement should be closed.
Strange that H2 driver doesn't close statements while closing connection.

I have change flag to supportsMultipleResultSets and now it works like a charm:

I will push changes and release it soon. Thank you for reporting!

[inikolaev]

Glad I could help :)

[Tapac]

Fix should be available with 0.7.6 release, please feel free to check it on bintray

[inikolaev]

@Tapac I'm still getting the same graph, though when I look at snapshot it says that those objects are only accessible via Weak or Soft references. But you had a different graph, how did you get it?

[Tapac]

@inikolaev . Looks like file was missed from commit (-_-).
Could you, please, test it with latest master before I make new release?

[inikolaev]

@Tapac Looks good now!