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 (

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.