PostgreSQL task: multiple workers read tasks from a table and generate promo codes. How to avoid duplication of work?

To allow multiple workers to safely read tasks from a table and generate promo codes without duplication, it is necessary to ensure that each task is processed by exactly one worker. In PostgreSQL, this is often achieved using transactions with row locking.

The main approach is to use SELECT ... FOR UPDATE SKIP LOCKED:

1. Workers select tasks that are not yet processed, locking them for themselves:

BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

2. After selecting a task, the worker marks it as "in processing" or immediately generates a promo code and updates the status.

3. Commit the transaction.

SKIP LOCKED allows other workers to skip already locked tasks, preventing competition and duplication.

Example in Go using the database:

func worker(db *sql.DB) {
  for {
    tx, _ := db.Begin()
    row := tx.QueryRow(`
      SELECT id FROM tasks
      WHERE status = 'pending'
      FOR UPDATE SKIP LOCKED
      LIMIT 1
    `)

    var taskID int
    err := row.Scan(&taskID)
    if err == sql.ErrNoRows {
      tx.Rollback()
      break // No tasks
    }

    // Generate promo code and update the task
    promoCode := generatePromoCode()
    _, err = tx.Exec(`
      UPDATE tasks SET status = 'done', promo_code = $1 WHERE id = $2
    `, promoCode, taskID)

    if err != nil {
      tx.Rollback()
      continue
    }

    tx.Commit()
  }
}

Thus, each worker receives a unique task without overlaps.