[ash]

"…it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects of those commands on other rows in the database."

http://www.postgresql.org/docs/current/static/transaction-is...

[anonymouz]

That only applies for the read committed isolation level. It seems to me that at least serializable should not show inconsistent state in this situation?

[the_mitsuhiko]

A row lock is released on a delete.

[anonymouz]

What does that have to do with serializable transactions?

"The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently."

If you don't get the right behavior with serializable transactions, as you seem to be claiming, it seems to me that serializable transactions should be considered buggy. In this case they do not provide the guarantees they are claimed to provide.

[jeltz]

You get the right behavior (a serialization conflict) in PostgreSQL. If you do not in some other database I would consider it to be buggy.

[amirouche]

This is an already known problem. See SSI vs PSSI.

[sp332]

Is this true for MySQL as well?

[jeltz]

Yes, it is true for all databases which do not take table level locks. In PostgreSQL you can increase the isolation level to serializable and get an error when this happens instead of incorrect query results, not sure about MySQL.