For batch processing I need to process all rows in a table. If the table is
too big, this leads to an OutOfMemoryException. I've found this article as
an solution:

http://bugs.mysql.com/bug.php?id=7698

with using 

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The problem is, that while streaming the table is locked. The article says:
"This is going to be fixed in MySQL 5.0". I assume that the "select * from
bigTable" was meant, but I've tried it with MySQL 5.0.32 and it still
throws the OutOfMemoryException, when trying without the two lines above
(with the latest JConnector).

So my question is: How can I iterate all rows of a big table, without
locking the table (allowing parallel read/write), with JDBC? 

-- 
Frank Buss, fb@frank-buss.de
http://www.frank-buss.de, http://www.it4-systems.de

On Fri, 25 May 2007 11:15:13 +0200, Frank Buss <fb@frank-buss.de>
wrote:

>So my question is: How can I iterate all rows of a big table, without
>locking the table (allowing parallel read/write), with JDBC? 

Piece by piece with a 
LIMIT n,m  
and iterate over 
n+=m ?

subtenante wrote:

> Piece by piece with a 
> LIMIT n,m  
> and iterate over 
> n+=m ?

This could fix the OutOfMemory problem, but how long will the table be
locked, when using e.g. LIMIT 10000000, 100 and how much time needs the
database to skip x times for each request the first n records?

-- 
Frank Buss, fb@frank-buss.de
http://www.frank-buss.de, http://www.it4-systems.de

On Fri, 25 May 2007 11:35:39 +0200, Frank Buss <fb@frank-buss.de>
wrote:

>subtenante wrote:
>
>> Piece by piece with a 
>> LIMIT n,m  
>> and iterate over 
>> n+=m ?
>
>This could fix the OutOfMemory problem, but how long will the table be
>locked, when using e.g. LIMIT 10000000, 100 and how much time needs the
>database to skip x times for each request the first n records?

Wait... Hmmm... my magic cristal ball is telling me... hmm... what ?
Oops sorry, that was not my cristal ball.

Give it a try ? See how many rows you have try to benchmark some data
about how long it gets to treat one row, make a division.
What is the engine of your tables ?, are you sure the whole table will
be locked and not only selected rows ?

I'm trying t o help but you don't give much hints.