Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Not a Bug
-
3.1.4
-
None
-
Windows 10
MariaDB 10.6.5
Java 17
SpringBoot 3.1.1, Hibernate 6.2.5
Description
Background: I created a test project that demonstrates various ways in Jakarta Persistence API (JPA) how to iterate over arbitrary number of entities read from the database
and possibly modify them, without running into OutOfMemoryError (OOME). The project is written in Kotlin & SpringBoot and connects to a MariaDB database. I was able to iterate over many (even milions) of entities (rows) from the database without any memory issues even when explicitely lowering heap size to only 200M. But when the iterated entities were also modified in the loop, with higher count (e.g. 100 000), OutOfMemoryError occurred (I guess the complete ResultSet was read into memory). I first suspected that the ORM framework (Hibernate) had some hidden bug so I also tried doing the same in plain JDBC but OutOfMemoryError occurred too! Then I tried connection to an Oracle database and to my surprise, everything worked correctly - either by using JPA or JDBC! So my conclusion is that there is some bug in the MariaDB JDBC driver OR that something else also must be set in the connection properties.
Attached is the complete project. Just unzip it, import it to some IDE (set JDK 17) and run the Gradle build. Set Set the -Xmx=200M in order to detect possible memory problems soon. Follow README.md.
Basically, run create 1 000 000 objects first and then run `jdbcresultset` endpoint with count = 100 000 and modifyData=true -> you will get OutOfMemoryError.
The most important class in MassDataService. It has various functions that show the possible ways how to iterate over objects in JPA (resultList, offsetPaging, resultStream, stream) and in JDBC:
|
//pass 100 000, 1000, 1, true -> OutOfMemoryError
|
fun jdbcResultSet(count: Int, flushAfter: Int, fetchSize: Int, modifyData: Boolean): Int {
|
val connection = dataSource.connection // take connection from the connection pool
|
val autoCommit = connection.autoCommit
|
connection.autoCommit = false
|
try {
|
val limitClause = "limit $count" // MariaDB
|
//val limitClause = "fetch first $count rows only" // Oracle
|
val ps = connection.prepareStatement("select sys_pk, amount, name, description, valid from something $limitClause")
|
// ResultSet.TYPE_FORWARD_ONLY, // default TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE-> OOME
|
// ResultSet.CONCUR_READ_ONLY, // default CONCUR_READ_ONLY -> PreparedStatement.executeBatch() gets stuck!, CONCUR_UPDATABLE -> OOME
|
// ResultSet.HOLD_CURSORS_OVER_COMMIT)
|
ps.fetchSize = fetchSize // if not set to positive number then OOM occurs!
|
printStatementInfo(ps)
|
val rs = ps.executeQuery()
|
var i = 0
|
val toUpdate = mutableListOf<Something>()
|
while (rs.next()) {
|
i++
|
val something = Something(
|
sysPk = rs.getInt("sys_pk"),
|
sysId = null,
|
name = rs.getString("name"),
|
description = rs.getString("description"),
|
amount = rs.getInt("amount"),
|
valid = rs.getBoolean("valid"))
|
if (i % flushAfter == 0) {
|
println("Entity $i: sysPk = ${something.sysPk}, name = ${something.name}")
|
}
|
if (modifyData) {
|
something.amount = something.amount + 1
|
toUpdate.add(something)
|
if (i % flushAfter == 0) {
|
update(toUpdate, connection) // uncomment this line if you want to update the row one by one
|
//batchUpdate(toUpdate, connection) // uncomment this line if you want to update the rows in batches
|
toUpdate.clear()
|
}
|
}
|
if (i == count) {
|
println("desired count reached..stopping")
|
rs.close()
|
ps.close()
|
break
|
}
|
}
|
rs.close()
|
ps.close()
|
} catch (ex: SQLException) {
|
connection.rollback()
|
println("Error: " + ex.message)
|
} finally {
|
if (autoCommit) {
|
connection.autoCommit = true
|
}
|
connection.close() // return connection to the pool no matter what
|
}
|
return 0
|
}
|
 |
private fun update(toUpdate: List<Something>, connection: Connection) {
|
var numUpdated = 0
|
val upds = connection.prepareStatement("update something set amount = ?, name = ?, description = ?, valid = ? where sys_pk = ?")
|
for(something in toUpdate) {
|
upds.setInt(1, something.amount)
|
upds.setString(2, something.name)
|
upds.setString(3, something.description)
|
upds.setBoolean(4, something.valid)
|
upds.setInt(5, something.sysPk!!)
|
numUpdated += upds.executeUpdate() // OOME even on first call !!!
|
}
|
upds.close()
|
println("updated $numUpdated record(s)")
|
}
|
 |
 |
|