SQL SERVER – 2008 – SCOPE_IDENTITY() and @@IDENTITY Bug with Multi Processor Parallel Plan

When a parallel plan is executed SCOPE_IDENTITY or IDENTITY may produce inconsistent results. The bug is active in SQL Server 2008 and any earlier version. See http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

 

N.B. : The specific circumstances on the occurrence of the bug are still unknown to me. Pinal Dave has written a blog about it earlier, so he might know some more details: http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/.

 

Anyway, Microsoft recommends to use the OUTPUT clause to read the identity instead of @@identity or SCOPE_IDENTITY() :

The following code snippet shows more detail on how to use the OUTPUT statement to return the Identity column of the newly inserted record. When a trigger is used on insert the OUTPUT value cannot just be returned, it must be used in conjunction with the INTO statement.
ID is the identity column which is not inserted but is retrieved using the OUTPUT statement

    Declare @InsertedData table (ID int)
    INSERT INTO Orders (VendorOrderID, StoreID, DateCreated)
        OUTPUT Inserted.ID
        INTO @InsertedData
    VALUES (@VendorOrderID, @StoreID, @DateCreated)
    SELECT ID FROM @InsertedData

 

Full reply from Microsoft (quote from connect site)

Yes, it’s a bug – whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistenly and can’t be relied upon. The few workarounds I can offer you for now:
1. Use MAX_DOP=1 as you are already using. This may hurt performance of the SELECT part of your query.
2. Read the value from SELECT part into a set of variables (or single tabel variable) and then insert into the target table with MAX_DOP=1. Since the INSERT plan will not be parallel you will get the right semantic, yet your SELECT will be parallel to achieve performance there if you really need it.
3. Use OUTPUT clause of INSERT to get the value you were looking for, as in the example I give further below. In fact I highly recomend using OUTPUT instead of @@IDENTITY in all cases. It’s just the best way there is to read identity and timestamp.
4. Changing autostas is NOT a good workaround. It may hide the problem for a while but a prallel plan will get produced eventually.
5. Force serial plans for entire server via sp_configure ‘max degree of parallelism’ option.
Now about life beyond workaround. We will fix it in SQL 2008. If you need this to be fixed also in SQL 2005 you will have to request a QFE through official support channels. I can’t promise anything either, as usual it’s a factor of complexity of request, resource availability, quality of workarounds etc.
In the end, thank you once again for reporting this. You have saved plenty of headaches for many people.
Denis Altudov, Microsoft.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
             Example as promised:
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
DECLARE @MyNewIdentityValues table(myidvalues int)
declare @A table (ID int primary key)
insert into @A values (1)
declare @B table (ID int primary key identity(1,1), B int not null)
insert into @B values (1)
select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()
set statistics profile on
insert into _ddr_T
output inserted.ID into @MyNewIdentityValues
    select
            b.ID
        from @A a
            left join @B b on b.ID = 1
            left join @B b2 on b2.B = -1
            left join _ddr_T t on t.T = -1
        where not exists (select * from _ddr_T t2 where t2.ID = -1)
–option (maxdop 1) –!!! If you uncomment this line, it works
set statistics profile off
select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY(),
    [IDENT_CURRENT] = IDENT_CURRENT(‘_ddr_T’)
select * from @MyNewIdentityValues
go
=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/

Posted by Microsoft on 6/18/2008 at 11:55 AM

Unfortunately, after evaluating our fix options we have come to conclusion that we can’t fix this for SQL 2008. We’re not doing this lightly, I have spent close to a month trying to make a robust fix for this, but amount of required work did not fit into this release.
If you come across this issue you will have to resort to one of the workarounds I have put in my previous post.

Share this