I have controler:
@GetMapping("/old")
public Product getOld() {
Product omeOld = productService.getOneOld();
log.info(String.valueOf(omeOld.getId()));
return omeOld;
}
Service:
@Override
@Transactional
public Product getOneOld() {
Product aNew = productsRepository.findTop1ByStatusOrderByCountAsc("NEW");
try {
Thread.sleep(5000L);
} catch (InterruptedException e) {
e.printStackTrace();
}
return aNew;
}
And repository:
@Repository
public interface ProductsRepository extends JpaRepository<Product, Long> {
Product findTop1ByStatusOrderByCountAsc(String status);
}
I start JMeter and send 5 request in 5 threads. In result I get 5 response after 5 seconds. Each request was processed by seconds. But in log I see next:
2018-09-14 14:04:35.524 INFO 9048 --- [nio-8080-exec-1] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:04:35.525 INFO 9048 --- [nio-8080-exec-2] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:04:35.532 INFO 9048 --- [nio-8080-exec-3] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:04:35.534 INFO 9048 --- [nio-8080-exec-4] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:04:35.534 INFO 9048 --- [nio-8080-exec-6] c.e.l.demo.controller.ProductController : 1
Each thread select the same row and process it. I need that first thread select first row, second thread select second row and etc. I try use @Lock(LockModeType.PESSIMISTIC_WRITE) :
@Lock(LockModeType.PESSIMISTIC_WRITE)
Product findTop1ByStatusOrderByCountAsc(String status);
Now when I start JMeter I have next behavior:
first thread worck 5 sec, after that second thread work 5 sec and etc. 25 secons all 5 threads. And in log:
2018-09-14 14:11:40.564 INFO 13724 --- [nio-8080-exec-5] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:11:45.566 INFO 13724 --- [nio-8080-exec-4] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:11:50.567 INFO 13724 --- [nio-8080-exec-2] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:11:55.568 INFO 13724 --- [nio-8080-exec-1] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:12:00.570 INFO 13724 --- [nio-8080-exec-3] c.e.l.demo.controller.ProductController : 1
All threads select the same row(if I change this roe in first thread - it will not select in second thread if the conditions do not match).
I try this:
@Query(value = "Select * from products where status = ?1 order by count asc LIMIT 1 for update", nativeQuery = true)
Product findTop1ByStatusOrderByCountAsc(String status);
the result is the same.
But I need - first thread select first row and block it/ Second thread select next not blocked row and process. I try next:
@Query(value = "Select * from products where status = ?1 order by count asc LIMIT 1 for update of products skip locked", nativeQuery = true)
Product findTop1ByStatusOrderByCountAsc(String status);
And it work fine! :
2018-09-14 14:25:00.355 INFO 7904 --- [io-8080-exec-10] c.e.l.demo.controller.ProductController : 4
2018-09-14 14:25:00.355 INFO 7904 --- [nio-8080-exec-4] c.e.l.demo.controller.ProductController : 3
2018-09-14 14:25:00.355 INFO 7904 --- [nio-8080-exec-9] c.e.l.demo.controller.ProductController : 1
2018-09-14 14:25:00.358 INFO 7904 --- [nio-8080-exec-5] c.e.l.demo.controller.ProductController : 5
2018-09-14 14:25:00.359 INFO 7904 --- [nio-8080-exec-2] c.e.l.demo.controller.ProductController : 6
Each select in each thread select one row from non blocked rows!
But how can I repeat this with Oracle? In oracle I can not write LIMIT 1 and if I use ROWNUM = 1 each thread select same row always.