Statement-level atomicity doesn't mean that you cannot get race conditions like the one that I described - it just means that the statement succeeds or fails as a whole.
It's quite clear that you can get those race conditions in both Oracle and SQL Server's MERGE (same with the new Postgres implementation). Last I checked neither SQL Server nor Oracle address the fact that MERGE pretty much doesn't do what many of its users imagine it can do. (There is a narrow theoretical sense in which it does meet expectations provided serializable isolation level is used, but in practice that isn't worth much to users.)
> anyway my experience has been that adding the lock hints "fixes" the problem with pk violations during concurrent upserts and it seemed to be commonly known and widely used afaict
That's not my impression:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with...
"Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements."
MERGE doesn't claim to work as an atomic statement, and doesn't work as an atomic statement in practice. In other words you can get a unique violation error where a "true upsert" would update the row instead. You can work around this "limitation" in MERGE by retrying the statement until it succeeds, but at that point you might as well use a regular INSERT instead.
The syntax for INSERT .. ON CONFLICT constrains the problem in certain ways, which is quite deliberate. It's a pretty faithful representation of what's really going on. Hiding that creates a lot of subtle but real problems.