Non-atomic dequeue (SELECT then UPDATE/DELETE) causes spurious empty results under concurrency

[fahimfaisaal]

Summary

Dequeue is done as a non-atomic SELECT then UPDATE/DELETE inside a DEFERRED transaction. Correctness under concurrency currently relies on SQLite returning SQLITE_BUSY, not on the design. The visible symptom is that Dequeue() returns false (indistinguishable from empty) while items are still pending.

Root cause

queue.go:141-180 and priority_queue.go:114-140:

row := tx.QueryRow("SELECT id, data ... WHERE status='pending' ORDER BY created_at LIMIT 1")
row.Scan(&id, &data)
// ... later ...
tx.Exec("DELETE FROM ... WHERE id = ?", id)   // or UPDATE to 'processing'

database/sql hands each goroutine its own pooled connection, and Begin() opens a DEFERRED transaction that takes only a read lock for the SELECT. Two workers can select the same row; when the second tries to upgrade to a write it hits SQLITE_BUSY. Because no busy_timeout is set, BUSY fires immediately, the Exec errors, and the method returns false.

Impact

Medium/High. Dequeue() can report the queue as empty when it is not, under normal concurrent load. Callers that treat false as "nothing to do" will stall or exit early. The only reason this is not outright duplicate delivery is the incidental BUSY error.

Reproduction

8 goroutines draining a 200-item queue: no duplicates and no loss (BUSY masks the race), but individual Dequeue() calls return false while items remain — workers that break on the first false exit prematurely.

Possible fix

Make dequeue a single atomic statement using RETURNING (SQLite >= 3.35, the bundled version supports it):

row := tx.QueryRow(fmt.Sprintf(
    `DELETE FROM %s WHERE id = (
        SELECT id FROM %[1]s WHERE status='pending' ORDER BY created_at ASC LIMIT 1
     ) RETURNING data`, quoteIdent(q.tableName)))

For the ack path, use UPDATE ... WHERE id = (SELECT ... LIMIT 1) RETURNING data. Additionally:

• open transactions as BEGIN IMMEDIATE (via _txlock=immediate DSN param), and/or
• set a busy_timeout (see companion issue on pool/DSN config)

so contention retries instead of failing.