"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> Jignesh Shah <jkshah@gmail.com> wrote:
>
>> The question is should the delete fail if it doesn't exist and
>> cause a rollback or succeed with DELETE 0 ?
>
> I think existing behavior is consistent with both the standard and
> the other behaviors of PostgreSQL at the READ COMMITTED isolation
> level.
I actually woke up in the middle of the night after sending this
with the realization that I was wrong in that statement. Well, half
wrong -- it *is* entirely consistent with the other behaviors of
PostgreSQL at the READ COMMITTED isolation level, but it is *not*
consistent with the standard. Since the row existed both before and
after the first transaction; for the other transaction to see zero
rows it has to violate atomicity, and see *part* of the work of the
transaction but not *all* of it.
This issue has been discussed on the list before, regarding other
curious behaviors, and what came of it was that to prevent such
behavior PostgreSQL would need to wrap each statement within a READ
COMMITTED transaction with an implicit subtransaction and upon
detecting that a transaction on which it was blocked due to a write
had committed, it would need to roll back the subtransaction and
acquire a new snapshot. Nobody seemed excited about either doing
the work to make that happen or what the performance implications
might be. The upshot was more or less that if you care about such
anomalies you should be running at a more strict isolation level --
what you describe is not a problem at REPEATABLE READ or
SERIALIZABLE isolation levels.
-Kevin