4 ms·

I understand that MERGE or something like that would be a nice convenience. But if I personally had this situation what I would do is (in pseudo code, but thin

by jhh 13y ago

I understand that MERGE or something like that would be a nice convenience. But if I personally had this situation what I would do is (in pseudo code, but think this would be in e. g. Python, or in a stored procedure): begin transaction does row with unique key x exist? if exists: update all relevant fields else: insert new row commit How does this have a different concurrency behavior than e. g. an equivalent Oracle MERGE statement or MySQL ON DUPLICATE KEY? If another process tries to write to this row before the change is committed, it would have to wait (in any case). If another process tries to read from this row before commit then what happens depends on the transaction isolation level and maybe even on implementation details. But I don't see how this is changed from having a built-in MERGE-like utility.

einhverfr 13y ago
What happens is this: Concurrent transactions marked A and B: A: Does row exist? Answer no A: Ok, so insert row. A: .... B: Does row exist? Answer no (A has not committed yet) B: Insert row (locks, waits to see if A commits or rolls back) A: Commits B: Unique constraint violation error. Now here's where it gets really tricky: What is the desired behavior? Do we: 1. Just raise a constraint violation error? That's what we do with LedgerSMB because, frankly, unique constraints are far more likely to be violated by human error than concurrency issues and we can tackle other related errors elsewhere. This is specific to our app. Other apps may differ. 2. Should the upsert lock, wait, and retry if the the upsert criteria is violated by a pending transaction? That's the cleanest option. However, this requires being aware of which unique indexes are affected by the upsert criteria. A quick and dirty upsert like yours is good enough for most applications based on human input. It is not good enough, IMO, for applications with lots of direct automation coming in from various sources. So those are very different use cases and they have very different criteria.
- jhh 13y ago
  Thanks for this explanation. I would have expected #1 to happen in the case that you outline. You're right, since a built in merge utility has more information about the semantical intent of the operation it can essentially repeat the complete operation whereas my naive implementation would not.

by jhh 13y ago

einhverfr 13y ago

What happens is this: Concurrent transactions marked A and B: A: Does row exist? Answer no A: Ok, so insert row. A: .... B: Does row exist? Answer no (A has not committed yet) B: Insert row (locks, waits to see if A commits or rolls back) A: Commits B: Unique constraint violation error. Now here's where it gets really tricky: What is the desired behavior? Do we: 1. Just raise a constraint violation error? That's what we do with LedgerSMB because, frankly, unique constraints are far more likely to be violated by human error than concurrency issues and we can tackle other related errors elsewhere. This is specific to our app. Other apps may differ. 2. Should the upsert lock, wait, and retry if the the upsert criteria is violated by a pending transaction? That's the cleanest option. However, this requires being aware of which unique indexes are affected by the upsert criteria. A quick and dirty upsert like yours is good enough for most applications based on human input. It is not good enough, IMO, for applications with lots of direct automation coming in from various sources. So those are very different use cases and they have very different criteria.

jhh 13y ago
Thanks for this explanation. I would have expected #1 to happen in the case that you outline. You're right, since a built in merge utility has more information about the semantical intent of the operation it can essentially repeat the complete operation whereas my naive implementation would not.