2 ms·
Unfortunately, SQL Server's merge statement has a LOT of issues. One of the worst being that it is isn't atomic (without non-SQL-standard hints)[0]! It really
by cogman10 6y ago
Unfortunately, SQL Server's merge statement has a LOT of issues. One of the worst being that it is isn't atomic (without non-SQL-standard hints)[0]!
It really feels like it was a half-assed feature.
Great that they followed the standard, not so great that they made it nearly unusable with such a poor implementation.
I was super excited when we got SQL server 2008 at work (since upserts are a relatively common issue) and was devastated to learn all the issues with MERGE. I didn't care that the syntax was funky, I just wanted to avoid needing to manage transactions/locks/etc.
[0] https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/ https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-cond...
- gfody 6y ago> it is isn't atomic merge is a single, logically-atomic statement in sql server (with or without hints) it doesn't have the same physical locking characteristics as a single insert or update statement, how could it?