So many Open Cursors because of "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"

[darkosika]

Questions

Is there any bug related with Transaction management because whenever I start a transaction, I see increasing open cursor number in database.

Version

vertx-oracle-client : 4.2.7
database : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Context

I encountered an exception which looks suspicious while I am calling method which is triggered on every 5 seconds but with batch size 10. I am using latest vert.x version and also I am using "pool.withTransaction" method. I never manage any cursor operation manually but it always open cursors and never close them. After a while, I saw below exception on console :

"io.vertx.oracleclient.OracleException: Error : 1000, Position : 0, Sql = SET TRANSACTION ISOLATION LEVEL READ COMMITTED, OriginalSql = SET TRANSACTION ISOLATION LEVEL READ COMMITTED, Error Msg = ORA-01000: maximum open cursors exceeded"

Actually, it happens whenever a transaction begin but not understand why cursors not closed because I expect to close them from vert.x but anyway it stay opens and caused that error.

Do you have a reproducer?

I put some example codes to reproduce issue because it can observed easily. I have one Verticle which always process item periodically. You can see main part from start :

   vertx.setPeriodic(5000) {
      launch {
          for (i in 0 until 10) {
            storageService.processItem()
          }

      }
    }

Also I have another service which I manage storage operations and you can see how I use transaction in here :

   override suspend fun processItem() {
pool.withTransaction { conn ->
     conn.query("select *  from TABLE where ROWNUM='1'") .execute()
}}

Also you can observe open cursors from database with below sql commands after you waited a little. You will see some increasing cursors numbers :

   select  sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

Steps to reproduce

1. Implement Verticle and service
2. Start project and wait around 5 min
3. Run sql query which show open cursors and observe increasing cursor number
4. Also you will get exception on console after a while regarding exceeding cursor number

[d53dave]

Hi, I work on the same team as @darkosika, and I was able to create a very small reproducer for this problem: https://github.com/d53dave/oracle-cursor-leak-vertx

The TL;DR is: this is a test that, in a loop, grabs a connection from the pool, does a conn.begin() to get a transaction, does a simple query, commits the transaction and closes the connection. It always leads to a ORA-01000 around iteration 300.

[vietj]

assigned you @tsegismont

[tsegismont]

Thank you @darkosika and @d53dave , a fix is coming in the next release.