Author: Kevin Feasel

Ajay Dwivedi runs into an error:

I work with developers quite a lot. There are scenarios where data is received from various sources in an application, and asynchronously pushed to database in multiple sessions/connections.

There are situations where concurrency and transaction speed is high enough to cause below UPSERT code blocks to fail with error message like Cannot insert duplicate key row in object dbo.person with unique index ‘pk_person’

Click through for one way to do things. I will note that Ajay has concerns about the MERGE operator, but Hugo Kornelis took a deep dive into all of the known problems in MERGE and found that most of them were fixed. Hugo’s post does make it clear when using MERGE is a bad idea, as there are still some situations in which it won’t work effectively, but for something like this, it would be fine.

I’ll say that I’m generally not a fan of app locking. There are specific circumstances in which it’s the best answer, but those are rare. Here, I’d rather just use a ROWLOCK table hint or change the serialization level.

Andy Brownsword changes things around:

Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues.

Usually the downside from implicit conversion is seen through performance impact. But we’ve got something a little different today – an inconsistent result set.

Read on for Andy’s example.