Microsoft – SQL Server – Identity Columns – Return Values Problems (well sometimes when Queries are ran in Parallel)
Potentially when a Query is ran in parallel the Identity values retrieved via conventional
means might not be correct.
In this case, conventional means refers to @@identity and SCOPE_IDENTITY.
As implied by the term Parallelism, a Query in parallel, has multiple threads executing
the same Statement. Potentially each thread is able to generate Identity Values and when
they do so, SQL does not do a complete job isolating the “returned” values from each
thread; this makes a bit of sense has only a single returned value is exposed via the
“outlets” noted earlier.
Please read more….
1) You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY
2) SCOPE_IDENTITY() sometimes returns incorrect value by _davedave
3) Six reasons you should be nervous about Parallelism
An an aside:
Thanks to _DaveDave for working hard & tirelessly to find, isolate, and confirm this bug.
As noted in the KB Article
the workarounds will degrade the System’s Performance.
a) Usage of the ‘OUTPUT’ clause ends up creating, populating, and retrieving values from
a table; a table variable in this case.
b) The other work-around (MAXDOP 1) limits the Query to a single-processor; possible
less performant Query.