JdbcTemplate select for update

Question

I have a spring application which reads data from the Database and sends it to system 'X'. I am using task executors to spin up threads, so there are like 5 threads which are reading the database for rows at the same time. For each thread I need to make sure that unique records are selected. To achieve this I am using JdbcTemplate and "select for update"

I have written the code but in the logs I am able to see 2 threads picking up the same rows. I am not able to figure out the root cause of this issue. Does anyone has a suggestion

try {
    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(
                        SELECT_FOR_UPDATE, 
                        new Object[] {a,b,c,d});
            
            
    for (Map<String,Object> row : rows) {
        
        Header a = new Header();
        a.setMailID(((BigDecimal)row.get("mailID")).intValue());
        a.setVersion(((BigDecimal)row.get("version")).intValue());
        // some other parameters to get 
        
        getJdbcTemplate().update(UPDATE_MSG_STATE_VERSION_N_ORIG_MSG_STAT, 
                                 x,
                                 a.getVersion()+1,
                                 y),
                                 a.getMailID(),
                                 a.getVersion());
        
        headers.add(a);
    }
}

UPDATE_MSG_STATE_VERSION_N_ORIG_MSG_STAT = update MESSAGE set MSG_STAT_CD = ?, VERSION_NBR = ?, ORIG_MSG_STAT_CD=?, LAST_UPD_TS=SYSTIMESTAMP where MESSAGE.MAIL_ID = ? and VERSION_NBR = ? 

String SELECT_FOR_UPDATE = "select m.MAIL_ID mailID, m.VERSION_NBR version, m.MSG_STAT_CD state,"
                                
                + "from message m "
                + "and m.MSG_STAT_CD in ('Nerwerw')"
                + " and m.create_ts > (sysdate - ?)"
                + " and mod(mail_id,?) = ?"
                + " and ROWNUM <= ?"
                + " order by mt.MSG_PRIORITY FOR UPDATE";
                

Answer 1

Do you have access to modify the database? If I understand your question correctly I recently had a similar problem and implemented a scheme like this:

Add a new column to your database like "thread_number" or something like that. Set it to some default value like 0. Give each thread a unique identifier. Then you "claim" a record in the database by updating its "thread_number" to the identifier of the thread processing it. Then the other threads will not find it when querying if you include "where thread_number = 0" in the SQL.

I know it's kind of broad, but I hope it helps.

Answer 2

Have you had transaction control properly set up?

If not, the transaction will only happen for the duration of the update statement, and will be committed automatically (You are using Oracle I believe, base on your syntax).

That means, although you acquired the lock of those records, they are released right-away.

Answer 3

You need to annotate your class with @Repostitory tag and the @Transactional tag to make sure that all the actions in the same call are handled in one transaction.

If they are not handled in the same transaction then each SELECT_FOR_UPDATE will happen on a different transaction and thus your threads queries will not be syncronized and your select_for_update does not matter.