Go + pgx pool + Postgres, 기본값인 Read Committed, 출금 한 건당 트랜잭션 하나예요. 잔액 1000, daily_limit 50, goroutine 20개가 각각 10씩 출금해요. 성공이 5번일 거라고 예상했는데 7, 8, 9번이 나와요. 오늘 날짜의 sum(amount)는 70~90이 되고요. MaxConns=1이면 정확히 5예요.
잔액 가드는 문제없어 보여요. 잔액이 15일 때는 하나만 성공하고 나머지는 UPDATE에서 그냥 0 rows가 나와요. 블록되지도 않고 에러도 안 나요.
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
Postgres가 UPDATE처럼 INSERT ... SELECT의 WHERE도 다시 확인하나요? 아니면 이 둘은 별개의 문제인가요? 로그에는 이상한 게 없고 -race도 깨끗해요.
제가 보고 있는 isolation 문서예요: https://www.postgresql.org/docs/current/transaction-iso.html
영어에서 번역·원문 보기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
