Microsoft – SQL Server – Identity Columns – Return Values Problems (well sometimes when Queries are ran in parallel)

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
http://support.microsoft.com/kb/2019779

2) SCOPE_IDENTITY() sometimes returns incorrect value by _davedave
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

3) Six reasons you should be nervous about Parallelism
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx 

An an aside:

Thanks to _DaveDave for working hard & tirelessly to find, isolate, and confirm this bug.

As noted in the KB Article

(http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779)

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.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s