[sujeetsr]

"The reason for this is that transactions just serialize the execution, they don't guarantee any atomicity of independent row updates. After the delete happens the second transaction gets a chance to run and the update will fail because it no longer sees a row" Umm - I thought everything in a transaction can be treated as atomic wrt to other transactions.. ie they don't see "in between" states?

[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.

[einhverfr]

It's actually that isolation that causes the problem. The issue is you have updates which dont see the existing inserted (and uncommitted) row and so the resulting inserts collide.

[amirouche]

that is called isolation. I don't understand either what he meant.