4 ms·
"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
by sujeetsr 13y ago
"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?
- amirouche 13y agothat is called isolation. I don't understand either what he meant.
- ash 13y ago"…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-iso.html http://www.postgresql.org/docs/current/static/transaction-is...
- sp332 13y agoIs this true for MySQL as well?
- jeltz 13y agoYes, 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.
- anonymouz 13y agoThat 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 13y agoA row lock is released on a delete.
- anonymouz 13y agoWhat 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.
- einhverfr 13y agoIt'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.