4 ms·

I believe the issue is a concurrent transaction (i.e. a separate one from the one you're currently in) may perform the INSERT and COMMIT that insert after your

by aseipp 8y ago

I believe the issue is a concurrent transaction (i.e. a separate one from the one you're currently in) may perform the INSERT and COMMIT that insert after your transaction's SELECT subquery returns nothing (thus allowing INSERT to proceed, by the WHERE clause), but before the actual INSERT itself is committed. This means there is a window between the sub-SELECT and the INSERT itself where a secondary transaction can insert something, causing both your original update to do nothing, and the insert to fail. Thus you've lost the write entirely.

jessaustin 8y ago
In that case the other effort (I'm reluctant to call it a transaction) wins. Maybe it was later and should win, maybe it was earlier and shouldn't. It takes a lot more care than seen here to preserve a meaning of "earlier" and "later" that isn't vulnerable to this issue. The way the schema is designed, there can be only one "Alice". I'm trying and failing to imagine a way in which both this effort and the other effort can fail.
- aseipp 8y ago
  I think the particular point made by Andres (and elsewhere in this thread) is really not about what the schema for a phone book is like, and more that "Accurately replicating what UPSERT does without it is exceedingly hard and you will probably get it wrong or suboptimal in a number of scenarios you can't possibly imagine until they happen". Isolation levels will for example have impact on how transactions proceed and what results you see (REPEATABLE READ vs READ COMMITTED); the error handling (did your transaction actually fail from a concurrent INSERT succeeding, or did the update fail, or did something else happen entirely? UPSERT is often not a standalone operation in a transaction) must be carefully decided for each use-case or else you're likely to redo needless transactions or throw away good ones, and you have to work around incidental problems like latency using stored procedures to keep everything server-side, etc etc. It's just not the kind of thing you want to replicate a number of places at each use site in a number of tools. It's something only the database knows how to manage properly by having "global knowledge".
  - endymi0n 8y ago
    Exactly this. It‘s so hard that it took PostgreSQL almost two decades to finally implement that feature and they had to support a lot of internal features first, like speculative insertion. If you want to do UPSERT 100% correctly and ACID, the list of edge cases just grows and grows. Here‘s a great article describing these problems in detail if you‘re interested: https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ https://www.depesz.com/2012/06/10/why-is-upsert-so-complicat...
chacham15 8y ago
Im sorry, maybe im just inexperienced here, but doesnt what you're describing violate the isolation part of ACID? https://en.wikipedia.org/wiki/Isolation_(database_systems) https://en.wikipedia.org/wiki/Isolation_(database_systems)

by aseipp 8y ago

jessaustin 8y ago

In that case the other effort (I'm reluctant to call it a transaction) wins. Maybe it was later and should win, maybe it was earlier and shouldn't. It takes a lot more care than seen here to preserve a meaning of "earlier" and "later" that isn't vulnerable to this issue. The way the schema is designed, there can be only one "Alice". I'm trying and failing to imagine a way in which both this effort and the other effort can fail.

aseipp 8y ago
I think the particular point made by Andres (and elsewhere in this thread) is really not about what the schema for a phone book is like, and more that "Accurately replicating what UPSERT does without it is exceedingly hard and you will probably get it wrong or suboptimal in a number of scenarios you can't possibly imagine until they happen". Isolation levels will for example have impact on how transactions proceed and what results you see (REPEATABLE READ vs READ COMMITTED); the error handling (did your transaction actually fail from a concurrent INSERT succeeding, or did the update fail, or did something else happen entirely? UPSERT is often not a standalone operation in a transaction) must be carefully decided for each use-case or else you're likely to redo needless transactions or throw away good ones, and you have to work around incidental problems like latency using stored procedures to keep everything server-side, etc etc. It's just not the kind of thing you want to replicate a number of places at each use site in a number of tools. It's something only the database knows how to manage properly by having "global knowledge".
- endymi0n 8y ago
  Exactly this. It‘s so hard that it took PostgreSQL almost two decades to finally implement that feature and they had to support a lot of internal features first, like speculative insertion. If you want to do UPSERT 100% correctly and ACID, the list of edge cases just grows and grows. Here‘s a great article describing these problems in detail if you‘re interested: https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ https://www.depesz.com/2012/06/10/why-is-upsert-so-complicat...

chacham15 8y ago

Im sorry, maybe im just inexperienced here, but doesnt what you're describing violate the isolation part of ACID? https://en.wikipedia.org/wiki/Isolation_(database_systems) https://en.wikipedia.org/wiki/Isolation_(database_systems)