Daily limit overshoots under 20 goroutines, but the balance guard holds. One bug or two?

Go + pgx pool + Postgres, default Read Committed, one transaction per withdrawal. Balance 1000, daily_limit 50, 20 goroutines each withdrawing 10. I expect 5 successes and get 7, 8 or 9. sum(amount) for today ends up 70 to 90. With MaxConns=1 it's exactly 5.

The balance guard looks fine. With balance 15, one wins and the rest just get zero rows from the UPDATE. They don't block or error.

INSERT INTO withdrawals (account_id, amount)
SELECT $1, $2
WHERE (SELECT coalesce(sum(amount), 0)
         FROM withdrawals
        WHERE account_id = $1 AND day = current_date) + $2
      <= (SELECT daily_limit FROM accounts WHERE id = $1)

UPDATE accounts
SET balance = balance - $2
WHERE id = $1 AND balance >= $2

Does Postgres recheck the WHERE for the INSERT ... SELECT the way it does for the UPDATE? Or are these two separate things? Logging shows nothing odd, and -race is clean.

Isolation docs I'm reading: https://www.postgresql.org/docs/current/transaction-iso.html

The conversation continues 3

Follow the reasoning

bonsai19 Sept Permalink
As far as I understand it, they are two separate things. Under Read Committed, an UPDATE that runs into a row another transaction has just changed waits for that transaction, then re-evaluates the WHERE against the new row version, which is why your balance guard holds. The INSERT ... SELECT has no such row to wait on, because it only reads a snapshot taken when the statement started, so twenty of them can all see the same sum and all pass. Taking a row lock first, with SELECT ... FROM accounts WHERE id = $1 FOR UPDATE, or running the balance UPDATE before the INSERT, should serialize them per account, as described in https://www.postgresql.org/docs/current/explicit-locking.html.
tally620 Sept Permalink
Following an earlier reply ↗
That matches what I'm seeing. One caveat on the fix, so nobody copies it wrong: the lock has to be its own statement, before the INSERT. Under Read Committed every statement takes a fresh snapshot. So SELECT ... FROM accounts WHERE id = $1 FOR UPDATE as step one, then the INSERT as step two, means the INSERT's snapshot is taken after the lock is granted. It then sees the withdrawal the previous transaction committed.

Fold the lock into the same statement with a CTE and you're back to one snapshot taken before the wait. I'd expect the overshoot to stay, though I haven't run that variant yet.

The balance-UPDATE-first idea also works, but only as a side effect of the row lock on a matching row. If the UPDATE matches nothing, it locks nothing. I'd rather have the explicit lock, since it says what it's for.

The "EvalPlanQual" recheck is described in the Read Committed section here: https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED

I'll rerun the 20 goroutines with the lock and see if it lands on exactly 5.
bonsai20 Sept Permalink
To add one thing to my earlier answer, since you asked whether these are one bug or two: they are two, and the MaxConns=1 result is the clue, because with a single connection the transactions are forced to run one after another, so every INSERT sees the previous commit and you land on exactly 5. The pool size isn't hiding a Go problem, it is only hiding the missing serialization in the database.

If you would rather not take a row lock on every withdrawal, there are two other routes, both of which I would consider before reaching for anything cleverer. The first is to keep a running total on the accounts row, say a spent_today column that resets by date, and fold the limit check into the same UPDATE as the balance check, with spent_today + $2 <= daily_limit in the WHERE clause. That puts both guards on the one row, so the recheck you already trust does the work. The second is Serializable isolation, with the application retrying on SQLSTATE 40001 — it works, but you are then committed to writing the retry loop properly.

Either way, I would test the result under the same twenty goroutines before trusting it.

Daily limit overshoots under 20 goroutines, but the balance guard holds. One bug or two?

The conversation continues 3

Other sides of the same question

rows.Next said false and the error went nowhere

A partial index and a parameter in the WHERE clause

grand total prints digits glued together, not a sum