10 application handlers take tasks from the database. How to prevent two instances from taking the same task?

To avoid a situation where two applications simultaneously pick up the same task from the database, it is necessary to ensure the atomicity of the operation of selecting and locking the task. One common approach is to use transactions with row locking or updating the task status.

For example, you can do the following:

1. Add a status field to the task table (e.g., "new", "processing", "done").
2. When selecting a task for processing, execute a query that atomically updates the task status from "new" to "processing" and returns the updated row.

Example in SQL (PostgreSQL):

UPDATE tasks
SET status = 'processing'
WHERE id = (
    SELECT id FROM tasks WHERE status = 'new' LIMIT 1 FOR UPDATE SKIP LOCKED
)
RETURNING *;

Here, FOR UPDATE SKIP LOCKED allows other transactions to skip already locked rows, preventing the same task from being taken twice.

In Java, you can use JDBC and transactions to execute such a query. This guarantees that each task will be processed by exactly one application handler.