I have an application on Spring Boot with PostgreSQL.
The application performs updates for rows in a database. In the past it was SELECT FOR UPDATE SKIP LOCKED to fetch new data and do updates in one thread, this was made to prevent several nodes to update same row (and as consequence to speed up update process for documents).
Now for speed up processing time, select rows and perform requests for update (to external service) are in separate threads (multiple workers with RestTemplate to smooth I/O waiting time) that fill this queue with ready updates and another thread worker perform post-processing by selecting from queue and insert to database. So now select and update in separate process and works in different transactions.
What is a good way to save behavior of SELECT FOR UPDATE SKIP LOCKED when processing is separated to different threads to prevent different nodes update same rows?
I think about adding few fields to table like update-status update-started node, and select like WHERE STATUS != 'IN PROGRESS' and to prevent holding rows if app crash add something like AND update-stared < now() - '20 minutes::INTERVAL'.
And second way to send connection from pool with document to another process, maybe it is better solution. As I know I can also select which node acquire lock so it also good for monitoring.
