Ten application handlers constantly fetch tasks from the database and process them. How to prevent a situation where two instances of the application pick up the same task?

To avoid situations where two applications simultaneously pick the same task from the database, the following approaches can be used:

1. Database-level locking (pessimistic locking)

   • Use transactions with row locking (SELECT ... FOR UPDATE) so that when a task is read, it is locked for other applications.

2. Optimistic locking

   • Add a version or timestamp field to the table.
   • When attempting to take a task, update this field with a check that the version has not changed.
   • If the update is successful — the task is taken; otherwise — someone else has already taken it.

3. Atomic update of task status

   • Execute a query that simultaneously selects and marks the task as "in progress".
   • For example, UPDATE tasks SET status = 'processing' WHERE status = 'pending' LIMIT 1 RETURNING *.
   • This way, only one application will get the task.

4. Using message queues

   • Instead of polling the database directly, use a queue (RabbitMQ, Kafka, Redis Streams) where tasks are distributed among consumers.

5. Using distributed locks

   • For example, with Redis (Redlock) or ZooKeeper to coordinate access to tasks.

Example of an atomic update in SQL:

UPDATE tasks
SET status = 'processing', worker_id = :workerId
WHERE id = (
  SELECT id FROM tasks WHERE status = 'pending' LIMIT 1
)
RETURNING *;

This guarantees that only one application will take the task.

Thus, the key is to ensure the atomicity of the operation of selecting and marking the task to avoid race conditions between applications.