3 ms·

Am I missing something? Wikipedia[1] says: "The isolation property ensures that the concurrent execution of transactions results in a system state that would be

Am I missing something? Wikipedia[1] says: "The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially." Oh, could the exception here be caused by the raw insert (without the where clause)? If you retried your example where both inserts were: INSERT INTO data SELECT 'alice' WHERE NOT EXISTS(SELECT * FROM data WHERE key = 'alice'); would that still cause the constraint error? If so, isnt that not equivalent to running the two queries sequentially hence violating the isolation property? Have I misunderstood something? [1]https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID

deleted 8y ago
[deleted]
imtringued 8y ago
Think of it like git branching. In the master branch there is no file "Alice". Two branches get created. One of them runs sed via find on all files with the name "Alice" and then it creates a file called "Alice" if find doesn't find any files called Alice. The second branch creates a file called "Alice". Both want to merge now but unfortunately there is a merge conflict. In this case automatic conflict resolution is impossible so one of the branches has to be discarded (transaction rollback). You can now "rerun" the branch and everything should work on the next merge. Conclusion: "update + insert where" is not equivalent to "upsert". The former can cause a transaction to fail and must be retried on failure. With "upsert" the database engine has enough information to handle the unique constraint.
- chacham15 8y ago
  Awesome! Thanks for the explanation! Sorry for the additional questions, but if we "rerun" the branch, shouldnt everything work as expected? (And in parallel, the answer to my above question is: yes, the raw insert is being rerun (after the insert where) which is causing the constraint violation; is that right?
anarazel 8y ago
What you're not supposed to allow is letting both succeed, because then the clients would assume everything went fine. If one errored before the commit, both transactions didn't execute successfully and you're fine. Pretty much every approach to isolation has a good chunk of errors that aren't strictly necessary (deadlocks, spurious serialization errors, ...).

deleted 8y ago

[deleted]

imtringued 8y ago

Think of it like git branching. In the master branch there is no file "Alice". Two branches get created. One of them runs sed via find on all files with the name "Alice" and then it creates a file called "Alice" if find doesn't find any files called Alice. The second branch creates a file called "Alice". Both want to merge now but unfortunately there is a merge conflict. In this case automatic conflict resolution is impossible so one of the branches has to be discarded (transaction rollback). You can now "rerun" the branch and everything should work on the next merge. Conclusion: "update + insert where" is not equivalent to "upsert". The former can cause a transaction to fail and must be retried on failure. With "upsert" the database engine has enough information to handle the unique constraint.

chacham15 8y ago
Awesome! Thanks for the explanation! Sorry for the additional questions, but if we "rerun" the branch, shouldnt everything work as expected? (And in parallel, the answer to my above question is: yes, the raw insert is being rerun (after the insert where) which is causing the constraint violation; is that right?

anarazel 8y ago

What you're not supposed to allow is letting both succeed, because then the clients would assume everything went fine. If one errored before the commit, both transactions didn't execute successfully and you're fine. Pretty much every approach to isolation has a good chunk of errors that aren't strictly necessary (deadlocks, spurious serialization errors, ...).