How is data consistency guaranteed between PostgreSQL substatements of a top statement?

user_1570711 0
Viewed 61

I'm still unable to figure out how SQL statements execute in parallel without messing each other's work. I understand the ACID principle in general, but I'm struggling to understand it on particular cases. For example, in the snippet below, could the value of bar.max_count not reflect the actual maximum value of count in table foo when the UPDATE is being executed? (for example, if someone updates or inserts new rows in table foo between the WITH statement and the UPDATE statement).

WITH 
  bar AS (
   SELECT id, MAX(count) AS max_count
   FROM foo
   GROUP BY id
  )
UPDATE mytable 
SET count = bar.max_count
FROM bar
WHERE mytable.id = bar.id

In other words: would this statement "block" other concurrent statements that attempt to write in table foo?

Thank you

postgresql transactions
1 Answers

It could happen that concurrent statements write to foo before you update mytable. However, as long as the concurrent statements don't also read from mytable, everything is fine, because then your transaction can be considered to logically predate these other transactions – it is as if though your transaction ran first.

But if one of those concurrent transactions also read a value from mytable, the result need no longer be consistent, if that information went into modifying foo, because then you can no longer logically order the transactions. This is known as a transaction anomaly, because it could never happen if the transactions were executed one after the other (serialized).

You get this kind of anomaly if you operate on the default READ COMMITTED isolation level, which offers a low level of transaction isolation.

To avoid such anomalies, you would have to use the SERIALIZABLE isolation level, where you are guaranteed to always have a serializable outcome. But there is a performance cost associated with SERIALIZABLE, and anomalies are resolved by terminating one of the involved transactions with a serialization error.

The alternative is pessimistic locking, but that would lead to excessive locking in your case.

user_64643080
Related