January 18, 2012 at 9:41 am
We have a large database (c. 11,000,000 records) which has been working fine for many years, handling 10's of thousands of requests per day.
In the last few weeks, however, something really strange has started happening and, try as we might, we can't figure out why it's doing what it's doing.
What it's doing is this:
When a request comes into our web service, it is inserted into the database and an identity is returned however at random times and for no apparent reason it starts returning the identity of the previous record, putting all requests handled during this period out of sync. This can happen at any time, day or night, and will stop and right itself, sometimes after a few seconds, sometimes after a few minutes.
We're aware of the known issue with using @@IDENTITY on 2005 and have implemented the suggested workarounds to no avail.
This is really starting to cause us headaches and sleepless nights now so any help/advice would be gratefully received.
ps, please excuse the rather layman-like explanation. This is a little (well, quite a bit) above my level of expertise and this is the best way I can describe what's happening.
January 18, 2012 at 9:48 am
Can you post the code that does the inert and the retrieval of the identity?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 9:50 am
Any reason to still be using @@Identity instead of an Output clause?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2012 at 2:53 pm
are there any other inserts in to other tables with a similar identity? try using scope_identity over @@identity
***The first step is always the hardest *******
January 18, 2012 at 3:32 pm
You should check to see if there are any triggers on the table, and if those triggers are new or have been changed.
And as has been suggested, get the identity value from the OUTPUT clause, instead of @@identity.
January 20, 2012 at 8:18 am
Hi everyone
The issue has now been resolved. It appears that there was an issue using a static method in the DAL. We had already changed the stored procedure to use scope_identity instead of @@Identity without any joy.
I'm afraid that's as technical a solution as I'm able to give you but I hope this might help if anyone experiences a similar issue.
Thanks for your input everyone.
January 20, 2012 at 8:21 am
Makes sense. Thanks for updating this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply