How to avoid two different java threads read the same rows from table contains million records.

I have to process million records and there wont be any where clause in the query (as need to process all the available records)

How can i do it ? Not using any java framework

CodePudding user response：

If I got you right, you have a SQL query like this:

SELECT * FROM mytable

that is executed in a method doProcess(), running in several threads.

From all I know about RDBMS (and that is not that much), SELECT does not lock the records it reads. That means that all instances of doProcess() will get the same set of records and will process them – with the result, that each record is processed multiple times.

To get out of this, think about this structure (THIS IS NOT JAVA CODE!):

class Processor implements Runnable
{
  private final MyRecord m_Record;
  public Processor( final MyRecord record ) { m_Record = record; }
  final void run() { doProcess(); }
}

…
var resultSet = executeQuery( "SELECT * FROM myTable" );
while( resultSet.hasMoreEntries() )
{
    var record = resultSet.getEntry();
    var processor = new Processor( record );
    threadPool.execute( processor );
    thread.start();
}

To implement this in real Java, have a look to java.sql.ResultSet, Executors and alike. You also need to create a MyRecord type that is initialised from the ResultSet.

Basically, you have one single thread that is reading the data from the database (and nothing more!), then it triggers another thread from a thread pool that does the processing.

CodePudding user response：

You could read the rows from one thread but pass each Row, as read from a ResultSet, to a pool of worker threads for faster processing.

Each row will be processed exactly once and you can configure your pool for maximum throughput.