Identity column SQL Server 2005 inserting same value twice

views:

109

answers:

Identity column SQL Server 2005 inserting same value twice

I have a stored procedure that inserts into a table (where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value).

We then use SCOPEIDENTITY() to get the value inserted, then there is some logic to generate the primary key field value based on this value, which is then updated back to the table.

In some situations the stored procedure is called simultaneously by more than one process, resulting in "Violation of PRIMARY KEY constraint..." errors.

This would seem to indicate that the identity column is allowing the same number to be inserted for more than one record.

First question- how is this possible?

Second question- how to stop it...there's no error handling currently so I'm going to add some try/ catch logic- but would like to understand the problem fully to deal with properly

+1 A:

I think, you should use IDENT_CURRENT(‘tablename’) instead.

SCOPE_IDENTITY(), will return the last identity value created in the current session.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Here you can read about your issue

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

and another useful article on parallelism

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

Good Luck!

hgulyan 2010-05-17 09:15:04

thanks for the links. Seems to me that using IDENT_CURRENT would ensure the problem happens more often, not fixes it- it would get the last insert to my table regardless of the scope of the current running stored procedure- but that's what I'm trying to avoid.Reading the links, I suppose it is possible that the problem is not the identity column insert, but the read back out using SCOPE_IDENTITY(). I need to understand the environment fully where the problem is occuring to see if parallelism is the cause. Thanks for your help

DannykPowell 2010-05-17 10:40:25

this looking likely at present- going to try using one of the suggested workarounds on one of those links of using OUTPUT clause in the insert instead of SCOPE_IDENTITY(). Will let you know how it goes/ thanks again

DannykPowell 2010-05-17 11:08:25

Ok, I'll update my answer too if I'll find out anything else. Good Luck!

hgulyan 2010-05-17 11:16:06

We turn parallelism off for our SQL servers: http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspxand: http://feodorgeorgiev.com/blog/2010/03/how-to-disable-parallelism-on-your-sql-server/

Junto 2010-06-14 17:01:31

+2 A:

Look at the error message more closely:

Violation of PRIMARY KEY constraint...

And given that you said:

where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value

It looks to me like the problem is nothing to do with the identity column, as it isn't the primary key, but more likely the date/time approach is not generating a unique value and failing when you try to insert it.

Is there some reason you're not just using an identity column as the primary key?

Greg Beech 2010-05-17 09:16:35

It is possible that the initial primary key generation is the problem yes (time used to the millisecond, so would have to occur at the exact same millisecond). I considered it more likely that the second generation of the primary key value was the problem (which would then mean that the identity column value, which is used for this generation,was the problem)DB schema is from an existing application, possibly this part of the application you would consider legacy!- do not know reasons for original decisions

DannykPowell 2010-05-17 10:34:25

The time returned by the system clock is typically only accurate to within approx. 15 milliseconds on most computers, so the insertions would only have to happen in the same 15ms period not the same millisecond; if it's possible that two insertions could happen within this margin (that's only about 65 inserts per second) then I'd expect to see the error from the initial key generation.

Greg Beech 2010-05-17 17:33:33

that's really interesting, thanks Greg...in which case the route I've suggested to the dev assigned to work on this may not be sufficient. Perhaps generating the initial key would be better to use a guid instead of time...

DannykPowell 2010-05-18 20:49:15

PK is inserted initially using the date/time to generate a unique value

Are you sure that it's the identity that's causing problems? If you insert the current date/time value as an initial value for the PK, and it executes simultaneously by 2 different threads/processes at the same time chances are you get the same value.

liggett78 2010-05-17 09:17:48

yes that is a possibility. The date/time is used to the millisecond so the 2 stored procedure executions would have to happen at the exact same millisecond

DannykPowell 2010-05-17 10:30:21

ansaurus

tags:

views:

answers:

Identity column SQL Server 2005 inserting same value twice

related questions