Pessimistic lock per page on 2 instances

Question

I have a scheduler which runs on 2 instances, the purpose is to allow the two instances to parallel process the task to gain in time/performance.

I'm using the pessimistic lock on oracle db, with skip locked in order not to block parallel process and allow the lock per page, so that each instance processes a different page.

@Transactional
@Scheduled(cron = ...)
public void process() {
  Pageable pageRequest = PageRequest.of(0, 100);
  Page<Event> pages = eventRepository.findAll(pageRequest);
  while(!pages.isLast()){
     pageRequest.next();
     pages.forEach(this::processEvent);
     pages = eventRepository.findAll(pageRequest);
     }
  pages.forEach(this::processEvent);
}

EventRepository

@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints({QueryHint(name= "javax.persistence.lock.timeout", value = "-2")})
Page<Event> findAll(Pageable pageable);

what happens is when the first instance applies the lock (on a page), the second instance can't see anything in the table, and the first continues to process all the pages.

I tried to initiate a new transaction in the service (propagation = REQUIRE_NEW) but in vain.

what is missing so that each instance locks one page in one transaction, and if there is an error, it should rollback only the page which has been processed ?

Thank you in advance

Answer 1

By default, a simple SKIP LOCKED query is going to grab everything it can based on the size of the fetch, eg

Session 1

SQL> create table t as
  2  select rownum r from dual
  3  connect by level <= 10;

Table created.

SQL>
SQL> select * from t
  2  for update skip locked;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Session 2

SQL> select * from t
  2  for update skip locked;

no rows selected

Session 1 grabbed (and locked) everything that was fetched, leaving nothing for session 2. If you want concurrent access then you need your code to limit the size of the fetch to something you would consider reasonable for your functionality needs, eg I'll use PLSQL but the same concept applies for any language:

Session 1

SQL> set serverout on
SQL> declare
  2    rows_to_process sys.odcinumberlist;
  3    cursor C is
  4      select * from t
  5      for update skip locked;
  6  begin
  7    open c;
  8    fetch c bulk collect into rows_to_process limit 5 ;
  9    for i in 1 .. rows_to_process.count loop
 10      dbms_output.put_line(rows_to_process(i));
 11    end loop;
 12    close c;
 13  end;
 14  /
1
2
3
4
5

Session 2

SQL> declare
  2    rows_to_process sys.odcinumberlist;
  3    cursor C is
  4      select * from t
  5      for update skip locked;
  6  begin
  7    open c;
  8    fetch c bulk collect into rows_to_process limit 5 ;
  9    for i in 1 .. rows_to_process.count loop
 10      dbms_output.put_line(rows_to_process(i));
 11    end loop;
 12    close c;
 13  end;
 14  /
6
7
8
9
10