JdbcPagingItemReader with SELECT FOR UDATE and SKIP LOCKED

Question

I have a multi instance application and each instance is multi threaded.

To make each thread only process rows not already fetched by another thread, I'm thinking of using pessimistic locks combined with skip locked.

My database is PostgreSQL11 and I use Spring batch.

For the spring batch part I use a classic chunk step (reader, processor, writer). The reader is a jdbcPagingItemReader.

However, I don't see how to use the pessimist lock (SELECT FOR UPDATE) and SKIP LOCKED with the jdbcPaginItemReader. And I can't find a tutorial on the net explaining simply how this is done.

Any help would be welcome.

Thank you

Answer 1

I have approached similar problem with a different pattern. Please refer to https://docs.spring.io/spring-batch/docs/current/reference/html/scalability.html#remoteChunking

Here you need to break job in two parts:

• Master

Master picks records to be processed from DB and sent a chunk as message to queue task-queue. Then wait for acknowledgement on separate queue ack-queue once it get all acknowledgements it move to next step.

• Slave

Slave receives the message and process it. send acknowledgement to ack-queue.