How to change set lock mode to wait in in spring boot application which use informix and jdbcTemplate?

Question

I'm using Spring boot 2 with jdbcTemplate in my application. I used Informix. I'm testing concurrency and I want my application to wait for a while when a query try to update a row that is locked. This time can be set in informix with 'SET LOCK MODE TO WAIT 20'. But I'm looking for a method in spring jdbcTemplate or something in spring.

I tried setting a timeout in the jdbcTemplate.setQueryTimeout(60); , but it did not work.

Any idea is welcome.

jdbcTemplate.setQueryTimeout(60);
cachedExecutor.execute(() -> jdbcTemplate.update(updatenIndoPeriodeInLfa()) );

cachedExecutor.execute(() -> jdbcTemplate.update(updatenAfroPeriodeInLfa()) );

private String updatenIndoPeriodeInLfa(){
        String query = "UPDATE  lfa " +
                "set indomonth = 09, indoyear = 2019 , indotype = indo " +
                "WHERE lfa.id in (select id " +
                "                   from   procindo " +
                "                   where  month   =   09    and" +
                "                          year    =   2019     and" +
                "                          type=   indo );";

        return query;
    }

    private String updatenAfroPeriodeInLfa(){
        String query = "UPDATE  lfa " +
                "set afromonth = 09, afroyear  = 2019 , afrotype = indo " +
                "WHERE lfa.id in (select id " +
                "                   from   procafro " +
                "                   where  month   =   09    and" +
                "                          year    =   2019     and" +
                "                          type=   afro );";

        return query;
    }

Answer 1

After digging the Informix documentation I come across this:

Application can use this property to override the default server process for accessing a locked row or table. Gets the value of the IFX_LOCK_MODE_WAIT variable, which is specific to Informix. The default value is 0 (do not wait for the lock). If the value has been set explicitly, it returns the set value. Returns: integer. Sets the value of the IFX_LOCK_MODE_WAIT variable, which is specific to Informix. Possible values:

-1 WAIT until the lock is released.

0 DO NOT WAIT, end the operation, and return with error.

nn WAIT for nn seconds for the lock to be released.

Then in my application datasource configuration, passed this property in as below:

@Bean(name = "informixDataSource")
public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.informix.jdbc.IfxDriver");

        Properties properties = new Properties();
        properties.put("IFX_LOCK_MODE_WAIT", "60");

        dataSource.setConnectionProperties(properties);
        dataSource.setUrl(jdbc.getUrl());
        dataSource.setUsername(jdbc.getUser());
        dataSource.setPassword(jdbc.getPassword());

        return dataSource;
    }