JdbcCursorInputSource and OutOfMemoryError

[gonzalad]

As a further improvement, why couldn't we use the RDBMS to do the scrolling?

Something like the following for Oracle ?
Taken from http://www.oracle.com/technology/ora...o56asktom.html, section 'Pagination with ROWNUM' :

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

[lucasward]

Right, but wouldn't that be something like a paginated item reader, rather than a cursor item reader? It seems like two completely different things.

[Dave Syer]

If the framework is smart enough to know about the different RDBMS flavours the interface for the user is identical to the existing readers, isnt it? So we could implement it as a new reader, but that would mean two choices to achieve the same goal with one clear winner (as long as you were on a supported platform).

[gonzalad]

Sorry to come in the middle of a very interesting conversation (this new jdbc reader can be really interesting)

I'm glad we fixed the issue.

We found a bug in 1.0.0-FINAL JdbcCursorItemReader (sorry !).

It happens on second or subsequent restarts.
This is because bufferredReader don't remember the previous processedRowCount.
bufferredReader#processedRowCount only contains the rows processed on the current restart.

When update is called, JdbcCursorItemReader stores in the execution context the rows during this restart. It should store the rows processed during this restart AND the previous ones.

This issue can be resolved changing the JdbcCursorItemReader#open code - see //CHANGE, //END CHANGE block at the end (note : a new constructor BufferredResultSetReader(ResultSet,RowMapper,long processedRowCount) would have been better than setting the field directly) :

public void open(ExecutionContext context) {
Assert.state(!initialized, "Stream is already initialized. Close before re-opening.");
Assert.isNull(rs);
Assert.notNull(context, "ExecutionContext must not be null");
executeQuery();
initialized = true;
 
long currentProcessedRow = 0;
if (context.containsKey(getKey(CURRENT_PROCESSED_ROW))) {
  try {
    currentProcessedRow = context.getLong(getKey(CURRENT_PROCESSED_ROW));
    while(rs.next()){
      if(rs.getRow() == currentProcessedRow){
        break;
      }
   }
 } catch (SQLException se) {
 throw getExceptionTranslator().translate("Attempted to move ResultSet to last committed row", sql, se);
}
}
 
//CHANGE
bufferredReader = new BufferredResultSetReader(rs, mapper);
bufferredReader.processedRowCount = currentProcessedRow;
//END CHANGE
}

[lucasward]

You're right, I'll add an extra constructor:

http://jira.springframework.org/browse/BATCH-549

[lucasward]

It's fixed in 1.0.1 dev.

[gonzalad]

Thanks Lucas !

[hessenmob]

HI All,

actually I'am getting this problem again when using a HibernateCursoItemReader. The driver caches every Row internally which results into 500MB heap data. This constantly crashes all my bigger data dump tasks.
Is the bug that existed in the JdbcCursorItemReader not fixed in the hibernate one?
Any ideas?

I'am using the thin oracle jdbc type 4 driver on 10g.

[ndefreitas]

Do you have the crash dump file? Are you using an IBM or Sun JVM?

[hessenmob]

Hi,

i will get the crash dump as soon as i can, i found the "leak" by profiling through the application with jprofiler. in oracle.jdbc the number of byte[] is constantly rising.
The driver stores all the read rows in the OracleResultSetCacheImpl in the resultset. As this is a normal behaviour of the oracle driver, i wonder how i can change that behaviour. Any idea?

I'am using sun 1.6, attatched a screen shot.
Did a dump by specifiing: -XX:+HeapDumpOnOutOfMemoryError
Used the SAP Memory Analyzer, very good to dig through dumps.

h-t-t-p://img-up.net/?up=oracleProb1DC2Xi0.png
h-t-t-p://img-up.net/?up=dumpVisualX1oB1n.png
Sorry, new links, old ones didn't work.

Sorry, attatchments are resized to strong, can't link pictures as i'am a new user (need 15 posts).