0

In the following method I'm trying to run an update query as batches. But this method hangs time to time when it's run. My assumption is that for some reason it creates a DB table lock on the sample_table and then when the executebatch is run again, it waits for the lock to be released. Eventually making the process hang.

My questions are

  1. whether this is the best way to implement an update query for a batch execute?
  2. Should i not set autocommit to false and then commit after each ps.executeBatch() within the loop.
  3. Which is more performance effective, adding all the batches and then committing or committing each batch again and again.

Note : Number of records updated might go up to 9000 records (9000 IDs) and batchSize variable is set to 1000

private void updateMethod( List<Long> idList)
{
    int batchSizeCount = 0;
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = criteriaWrapper.getConnection();
    StringBuilder sb = new StringBuilder( "UPDATE sample_table SET column_name1 = 1 , column_name2 = SYSTIMESTAMP WHERE sample_table.table_id = ? " );

    try
    {
        con.setAutoCommit( false );
        ps = con.prepareStatement( sb.toString() );

        for(Long table_id : idList)
        {
            int count = 0;
            ps.setLong( ++count, table_id );
            ps.addBatch();
            if ( ++batchSizeCount % batchSize == 0 )
            {
                ps.executeBatch();
                functionProvider.logger(); //Prints previously appended logs
            }
        }
        ps.executeBatch();
        con.commit();
        con.setAutoCommit( true );
    }
    catch ( Exception e )
    {
        e.printStackTrace();
    }
    finally
    {
        DBUtility.close( rs );
        DBUtility.close( ps );
    }
}
direndd
  • 642
  • 2
  • 16
  • 47

1 Answers1

0

If you can use spring, it has an easy option:

jdbcTemplate.batchUpdate(query, insertParameters). I have tested millions of transaction and it takes few seconds only.

krmanish007
  • 6,749
  • 16
  • 58
  • 100
  • Unfortunately using spring is not an option for me. – direndd Jun 17 '16 at 06:56
  • spring batch is internally doing the same thing of what you are doing. It is capable of handling much more than 9k in a single batch. You can easily execute in a single batch. You may need to monitor the return value of execute to see how much was successful, to decide if you want to do the commit or not. – krmanish007 Jun 17 '16 at 08:14