I have got a serious problem where the DAO-layer stops returning records after a few calls. I'm using Spring Framework 5.3.10. The main components involved are:

• Spring MVC Connection pooling over HikariCP 5.0.0
• JDBC connector Jaybird 4.0.3 (Firebird 3.0.7 database server)
• ThreadPoolExecutor (using default values)
• Spring Transactions
• Mybatis

I have got one Spring controller (A), that repeatedly (every 2 - 3 seconds) calls a method of a Spring Service (B) asynchronously (method marked with @Async) and a different parameter for each call. There is a DAO-layer (C) declared as a Spring service. The worker method in the Spring service (B) calls a DAO-method in the beginning of each run to retrieve a data set from a database table corresponding to the passed parameter. At the end of the execution of the worker method in the Spring service (B), rows corresponding to the input parameter are updated (not the field corresponding to the input parameter). The method in the Spring service (B) takes a long time to process the data, about 10 - 15 seconds.

After about the third or fourth call from the Spring controller (A), the call to the DAO-method returns an empty result set. When calling the method in the Spring service (B) slowly, waiting for the previous call to complete, everything is working correctly.

Setting transaction isolation has got no effect whatsoever.

I have tried to solve this problem for a couple of days now, and getting nowhere. I would be very grateful if somebody can point me in the right direction how to solve this. Using some kind of mutexes or semaphores is just a way to circumvent the problem without really solving it.

Schematically

Controller A <---------
    |                  |
    |                  | repeats every 2-3 secs.
Service B              |
worker method          |
takes 15 - 20 secs. ----

calls DAO-method getData(token)
    |
  do work
    |
calls DAO-method updateData(token)

Controller (A)

@Controller
@RequestMapping("/test")
public class TestController {

    @Autowired
    private TestService testService;
    ...
    ...

    @GetMapping(value="/RunWorker")
    public String runWorker(ModelMap map, HttpServletRequest hsr)  {
        ...
        testService.workerMethod(token);
        ...
    }
}

Service (B)

public interface TestService {
    public void workerMethod(long token);
}

@Service
public class TestServiceImpl implements TestService {

    @Autowired
    private TestDAO testDao;

    @Override
    public void workerMethod(long token) {
        List<MyData> myDataSet = testDao.getData(token);
        ...
        // very long process
        ...
        testDao.updateData(token);
    }
}

DAO (C)

public interface TestDAO {
    public List<MyData> getData(long token);
    public void updateData(long token);
}

@Service
public class TestDAOImpl implements TestDAO {

    @Autowired
    private TestMapper testMapper;  // using Mybatis mappers

    public List<MyData> getData(long token) {
        return testMapper.getData(token);
    }

    public void updateData(long token) {
        testMapper.updateData(token);
    }
}

Mapper class (D)

public interface TestMapper {
    @Select("SELECT * FROM TESTTABLE WHERE TOKEN=#{token}")
    public List<MyData> getData(@Param("token") long token);

    @Update("UPDATE TESTTABLE SET STATUS=9 WHERE TOKEN=#{token}
    public void updateData(@Param("token") long token);
}

Thanks @M. Deinum for the suggestion about @Repository. This did not help, however.

I remade the Spring service (B) to a Spring bean with prototype scope, and injecting it with @Lookup. The behavior is still the same. After the second call, the DAO-method getData returns an empty result set. Very puzzling and frustrating.

CodePudding user response：

I solved the problem. It was probably resource exhaustion due to repeated multiple calls to the Spring service (B) with the same call parameters. I guess the statement pool got depleted, active statements not returning fast enough, and then returning empty data sets for each call.

Best regards,

Peter