I've noticed that the below query in my web application creates a race condition which causes an inaccurate quantity_remaining amount. My application uses the values in the row returned by this query, and inserts them (along with other data) into a separate table, called charges
. However, I've noticed that after this statement runs hundreds or thousands of times, the quantity remaining is higher than it should be - I've inserted more rows into the charges table than I've decremented the quantity remaining.
I can't seem to reason what the problem would be as the UPDATE part should execute atomically. I know that there is a race condition when the quantity_remaining is near 0, but I've see problems when quantity remaining is in the tens of thousands and does not approach zero rapidly.
What is the race condition in this query that would cause it return the row from lro
and not decrement quantity_remaining, with a quantity_remaining not near zero? Note that I'm using PostgreSQL 9.3.3.
UPDATE reservations SET quantity_remaining = quantity_remaining - 1
FROM (
SELECT lr.id, lo.base_price, lo.page_price
FROM reservations lr INNER JOIN offerings lo ON lr.offering_id = lo.id
WHERE lr.account_id = 'some-id'
AND lo.type = 'blue'
AND lr.quantity_remaining > 0
AND lr.end_date >= now()
ORDER BY lr.start_date ASC LIMIT(1)
) AS lro
WHERE reservations.id = lro.id
AND reservations.quantity_remaining > 0
RETURNING lro.*;