SQL SERVER – 2008 – SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution

This article is very serious and I would like to explain this as simple as I can. SCOPE_IDENTITY() which is commonly used in place of @@Identity has bug when run in Parallel Plan. You can read my explanation of @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT in earlier article.

The bug is listed here in connect site SCOPE_IDENTITY() sometimes returns incorrect value. Additionally, the bug is also listed in Book Online on last line of the SCOPE_IDENTITY() documentation.

When parallel plan is executed SCOPE_IDENTITY or IDENTITY may produce inconsistent results. The bug will be fixed in future versions of SQL Server. For SQL Server 2008 or any of the earlier version it should be fixed right away using following workarounds.

Single Processor Execution of Query: (Prefered method is to use OUTPUT clause mentioned below)
In short if have multiprocessor and your SQL Server is set to use multiprocessor to execute queries, it is all good till it encounters SCOPE_IDENTITY. You can use (MAX_DOP=1) option to execute query using single processor. As using single CPU can hurt the performance the better solution is to use OUTPUT clause as mentioned below.

USE AdventureWorks
GO
SELECT *
FROM HumanResources.Department
OPTION (MAXDOP 1)
GO

Whole server can be also set to run parallelism in serialization.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Using OUTPUT clause: (Preferred Method)
Refer my previous article for how to use OUTPUT clause information : SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE.I have included the same example along with this article for quick reference.

USE AdventureWorks;
GO
/* Creating the table which will store permanent table */
CREATE TABLE TestTable (ID INT IDENTITY(1,1), TEXTVal VARCHAR(100))
/* Creating temp table to store ovalues of OUTPUT clause */
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
/* Insert values in real table as well use OUTPUT clause to insert
values in the temp table. */
INSERT TestTable (TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES ('FirstVal')
INSERT TestTable (TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES ('SecondVal')
/* Check the values in the temp table and real table
The values in both the tables will be same */
SELECT * FROM @TmpTable
SELECT * FROM TestTable
/* Clean up */
DROP TABLE TestTable
GO

While executing above code we will get following result. It is very clear from result that Identity which is inserted and which is retrieved using OUTPUT clause is same.

If have you any problem, question about using OUTPUT clause or MAXDOP, please feel free to leave comment here and send me email. I want to make sure that community is aware of this issue and have solution ready. Microsoft will take necessary steps to fix the issue but till then as community members it is our responsibility that we help each other and make sure our applications are running smoothly. Spread the word for this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

24 thoughts on “SQL SERVER – 2008 – SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution

  1. Hey Pinal

    I wonder why I did not know it earlier. I have read this earlier on MS connect but I did not understand it there. Thanks for putting the same in simple words.

    No wonder you are MVP. Do you work for Microsoft too.

  2. Hello, Dave! As I can see there are situations when identity_scope should return valid values:

    a) insert … values
    b) insert … select (no from clause, an invariant for statement above)
    c) insert … select … with MAX_DOP=1 – as noted at feedback comments forces nonparallel query plans

    but I’m not sure that
    insert … select top 1 … –complex query goes here
    will not result in parallel execution, as query optimiser could decide to make parallel index scan, for example.

    So there are only two ways to insert single row & return generated id: insert…maxdop=1 and insert…output.

    The last one requires table variable, the first one supresses parallel plans. Which one will be more efficient? — I perfectly understand there are no universal recomendations:)

    Thanks!

  3. I have tried all your work arounds to no avail. Our program is written in C++ and uses ODBC to connect to the SQL DB. All SQL works great with all versions of SQL Server and ODBC drivers (7.0 and later) except 2008 using the SQL Server native Client 10 ODBC driver. SQL Server 2008 with Native Client 2005 ODBC driver also works fine. We are having a similair Scope_Identity issue but I believe it ‘s different than outlined above. We even have issues using SQL 2008 Express (no parrallism used) using the SQL Server Native Client 10.

    SQL Server ODBC Native Client 10 just can’t grab the right Scope_Identity but the 2005 Native Client is fine connecting to a SQL Server 2008 database with our application.

    so we are successful in getting the correct Scope_Identity while using the application with ODBC driver SQL Server Native Client from 2005… the following commands were issued:

    INSERT INTO BinaryImage (“ImageSourceType”,”RetentionType”,”ImageSize”,”ImageDataCRC”…

    SELECT SCOPE_IDENTITY()
    SELECT …. FROM “BinaryImage” WHERE BinaryImageNo=2514628

    But with ODBC SQL Server Native Client 2008 the same insert looks like:

    exec sp_executesql N’ INSERT INTO BinaryImage (“ImageSourceType”,”RetentionType”,”ImageSize”,”ImageDataCRC”…

    SELECT SCOPE_IDENTITY()
    SELECT …. FROM “BinaryImage” WHERE BinaryImageNo=158731

    Note: The BinaryImageNo should have been greater than 2,204,700

    Thanks…

    Phil S

  4. Could you please post some more details on when this bug might occur? A sample script?

    The scripts posted on the connect site to reproduce the problem are not downloadable.

    Thanks.

  5. What about deployed solutions, we can’t just call each customer and say ‘hay a bug was found in sql server which we don’t know when it will be fixed (surely not soon) and we will change few lines of the code’ and I think every one knows What a big mess the customer will do when I answer to his question which is (what is that bug? and how it may effect in my business?)

    and no one has a time to change the code and deploy it again to the customers we have other things to do.

    work around for the new projects but what about the one in production. Need a solution not a work around.

    The bad thing is not the bug (every one make mistake). but action that was taken to fix it is not acceptable. it is almost 2 years since the bug was issued and no serious action was taken to solve it.

    This is not the expected support that is supposed to be good.

    I started to reconsider in other DBMS.

  6. Hi Pinal,

    Thanks for this wonderful peice of information.

    But one thing I would like to get clarification from you here ,

    as Output clause use inserted table
    and also triggers written for insert and update also using inserted table.

    I guess trigger on the same table will result in abnormal behaviour of output clause.

    Pls. clarify this.

    Kind Regards
    Kuldeep

  7. Hi Kuldeep,

    How the trigger could affect the behaviour of OUTPUT clause?
    The data in INSERTED table is affected nither by trigger nor
    by OUTPUT clause. Both are just reading this table.

    Regards,
    Pinal Dave

  8. Saw this on 2005 while trying to INSERT SELECT using the cross-database query. IDENT_CURRENT() showed correct seed. Can this be used as a permanent work-around?

  9. IDENT_CURRENT() may return incorrect identity when multiple insertions performed by multiple connections at same time. Because its returns the last identity value for a table not the value that was generated by your session.

    Regards,
    Pinal Dave

  10. Is it possible to continue to use scope_identity() if you set the SQL Server to use only one (1) processor? Would I get the right value every time?

    I know this is not a long term solution. But it would allow me to move from SQL 2000 to SQL 2008 right away. Then go back and update the database along with web pages.

  11. Hi Penal,

    I suspect that we are running into this problem. But the stored proc that uses SCOPE_IDENTIFY is a very simple insert statement. We do get an invalid ID a couple of times a day.

    How do I confirm that this is caused by this known bug? I am not sure when SQL server 2008 will choose generate a parallel plan.

    Hope to hear from you. Thanks!

  12. Hi guys,
    here i am trying to assing the last inserted identity value to the variable named @ID and I am getting an error but this is the requirement for me. Is there any solution for this ?
    here is the same kind of script….

    begin tran
    declare @ID int
    set @ID = 0
    insert into T1 values(‘me’)
    output @ID = Inserted.id
    print @ID
    rollback tran

    thanks.

  13. Hey Dave, I encountered the problem with scope_identity() before, and changed all single row INSERT INTO statements to use OUTPUT INTO clause. However I am still seeing incorrect ID being returned from time to time (rarely, yet still…) and I am confused now. Is it possible that incorrect ID is returned by OUTPUT INTO clause, when multiple calls to the procedure using it are made at the same type? Above INSERT INTO is run in TRANSACTION, and since large amount of interactions with DB lead to many DEADLOCKS, our DB is setup with SET READ_COMMITTED_SNAPSHOT ON; and SET ALLOW_SNAPSHOT_ISOLATION ON;. However the records getting incorrect ID are several hours apart from each other, which would suggest it’s not a matter of using SNAPSHOT. Any ideas?

    • Small update, it is not wrong ID being returned, it is function run from transaction in procedure returning wrong ID. I now moved that simple select out of function back to procedure (directly), and I hope this won’t reoccur.

  14. According to SQL 2008 SP3 “list of bugs fixed” this issue is addressed. but i think i am experiencing the issue with sql 2008 sp3. any idea?

Leave a Reply

Fill in your details below or click an icon to log in:

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