how to repeat this on Oracle? (Select for update + order by + LIMIT 1 + skip locked)

Viewed 153

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.

0 Answers
Related