News for hackers. Stuff that matters.

A comment on Choose Postgres Queue Technology. Parent comment

Re:Choose Postgres Queue Technology by klauserc on Monday September 25, 2023 @04:59AM (#37641104)
With a transaction isolation level below SERIALIZABLE you can have two transactions that both read the old row (with `used = 0`) at the time they perform the update (but before they commit the transaction). In that case, both transactions will have performed an update (rows affected = 1).

Why would both transactions see `used = 0`? The DB server tries to isolate transactions and actively hides effects of other transactions that have not committed yet.

[ Parent | on HN ]
Re:Choose Postgres Queue Technology by singron on Monday September 25, 2023 @06:11AM (#37641526)
This is not true in postgres. When the second transaction tries to update the row, it will wait for the first transaction to commit first and then recheck the WHERE.

https://www.postgresql.org/docs/current/transaction-iso.html...

[ Parent | on HN ]