0

Context

We have a (Java, JPA:EclipseLink) application connected to MySQL.

There are cases where we want to fetch thousands of results, and perform an action per-result. We do not want to hold the whole result set in-memory.

JPA:EclipseLink's queries with a "scrollable cursor" allow us to iterate lazily through a result set, keeping only one result in-memory at a time.

It is my understanding that these queries with a "scrollable cursor" are implemented in MySQL as a cursor.

Problem

It is the consumer's responsibility to "close" any MySQL cursor.

We have trouble guaranteeing that the cursor gets closed. Naturally we close the cursor when we reach the end of the result set, but there are problems that would prevent us iterating as far as that:

  • Exception thrown
  • Program exit
  • Intentional early-exit of iteration
    • e.g. where the question is simply "is a non-zero amount of results returned?"

Anecdotes

We have observed before that: leaving many cursors open, uses up available connections in our database connection pool. Once the limit is reached: subsequent queries fail because no further database connections are available.

We have a suspicion that completing a transaction will close any open database cursors participating in that transaction. If so: this would provide a very useful safeguard. However we have yet to measure whether this does indeed happen, and haven't yet worked out what level of transaction provides this guard (MySQL transaction? JPA transaction? Spring transaction?). An answer to the question below, would enable us to measure this…

Question

Is it possible in MySQL to show a list of all currently-open database cursors? If so: how?

Community
  • 1
  • 1
Birchlabs
  • 7,437
  • 5
  • 35
  • 54
  • 1
    If using the JPA API it is the JPA providers responsibility to close such things. It is NEVER the users responsibility. And why do you think your JPA provider isn't? – Neil Stockton Jul 25 '16 at 17:09
  • If it's the JPA provider's responsibility: how could JPA know when I'm finished with the cursor? Certainly I expect that if my transaction ends, it can infer that I am done with the cursor. But otherwise: I think the only way for it to know is if I explicitly call `.close()` on the `Cursor` object. And _maybe_ it would be bold enough to clear the cursor when my `Cursor` object gets garbage-collected. But essentially I suspect I can only bet on its closing my cursor automatically if I am inside a transaction. [Part 1 of 2] – Birchlabs Jul 25 '16 at 17:23
  • JPA provider pass you a List from getResultList. That List should be one of the type provided by your JPA provider. It knows when you close an EntityManager and so can close all remaining Query result lists etc. If the JPA provider allows you direct access to a java.sql.Cursor then it is truly very trusting and not really managing the persistence process – Neil Stockton Jul 25 '16 at 17:26
  • The main reason I believe EclipseLink _may not_ close my cursors, is: we once updated a JPA query to be scrollable. We found that running our main loop for more than a few iterations resulted in exhausting the available database connections in our pool. Our main loop's query never explicitly invoked `.close()` upon its `Cursor` object, and would not necessarily scroll to the end of the result set. We found that the increase in database connections did *not* occur if we used a regular query. We concluded that we were leaving open cursors. Possibly we were outside of a transaction. [Part 2 of 2] – Birchlabs Jul 25 '16 at 17:33
  • suggest you look at the EclipseLink code and see how it handles such things. – Neil Stockton Jul 25 '16 at 17:35
  • Clarification: EclipseLink gives me a `org.eclipse.persistence.queries.Cursor`, not a `java.sql.Cursor`. – Birchlabs Jul 25 '16 at 17:46

0 Answers0