If we want to have multiple readers from the outbox table, how to synchronize them so they don't take the same data?

To synchronize multiple workers reading from the same outbox table to avoid processing the same data, the following approaches are usually used:

1. Status flag or record status — add a field to the table, such as processed or in_progress. Workers filter only those records with a "new" status when selecting records. When starting processing, the worker updates the status to "in progress" using an atomic operation.

2. Optimistic locking using version or timestamp — the worker reads a record with a version, tries to update the status, checking that the version has not changed. If the update is successful, the worker processes the record.

3. Using an SQL query with row locking (SELECT ... FOR UPDATE SKIP LOCKED) — in PostgreSQL and some other DBMS, you can select records with locking, skipping those already locked by other transactions. This allows multiple workers to take non-overlapping records in parallel.

Example in Go using PostgreSQL and FOR UPDATE SKIP LOCKED:

func fetchNextOutboxItem(db *sql.DB) (*OutboxItem, error) {
    tx, err := db.Begin()
    if err != nil {
        return nil, err
    }
    defer tx.Rollback()

    row := tx.QueryRow(`
        SELECT id, payload FROM outbox
        WHERE processed = false
        FOR UPDATE SKIP LOCKED
        LIMIT 1
    `)

    var item OutboxItem
    if err := row.Scan(&item.ID, &item.Payload); err != nil {
        return nil, err
    }

    // Mark as in progress or processed
    _, err = tx.Exec(`UPDATE outbox SET processed = true WHERE id = $1`, item.ID)
    if err != nil {
        return nil, err
    }

    err = tx.Commit()
    if err != nil {
        return nil, err
    }

    return &item, nil
}

Thus, row locking and atomic status updates help prevent race conditions and duplicate processing.