March 28, 2012 at 10:55 am
We have VB .NET applications that use web services to insert records into several somewhat high activity tables. There are also a lot of lookups on those tables as well and nightly maintenance deletions. Our reservation table would have say 25,000 records in it with about 12,000 added thru the day and 12,000 deleted at night. About 10 times per day co-inciding with our peak time activity, the inserted reservation record disappears after insert. We added code to our VB software to insert a record, sleep for 750 milliseconds, go read the record using the identity column returned on insert and if it is not there we log the error and recover the information from other sources (logs etc.). Did anybody ever experience anything like this and what is the best place to analyze this situation? This is VB.NET 2010, SQL Server 2008 R2, there is plenty of memory and plenty of idle CPU power based on performance monitor analysis.
March 28, 2012 at 10:58 am
Rows do not just "disappear," they have to be deleted. OR they are inserted in a transaction and not committed. Can you please post the script you are using here according to the article found below my signature?
Jared
CE - Microsoft
March 28, 2012 at 11:19 am
The insert is done with an INSERT statement immediately followed by SELECT @@IDENTITY. Since it is a rare occurence (about 10 times per day) our theory has been that SQL server is rolling the transaction back for one reason or another. We tried to catch the rollbacks in SQL Profiler by setting up a ROLLBACK and DEADLOCK trace but got absolutely no results over a 3 day period even though we know we have issues every day (according to our logs). The "insert/select @@ identity" is done through a webservice call which executes the passed in SQL statement.
Can somebody suggest a good way to monitor rollbacks, deadlocks etc. in SQL Profiler? We also checked event logs and there is nothing in them either.
March 28, 2012 at 11:19 am
You should be able to get both ROLLBACK and DEADLOCK from SQL Profiler, you'll need to capture for ROLLBACK:
> Event: Transaction:Transaction Log you'll find a Rollback or Commit in the EventSubClass
And for DEADLOCK:
> Events:
Lock: Deadlock
Lock: Deadlock Chain
Deadlock graph
/>L
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
March 28, 2012 at 11:26 am
Because you are wanting to capture this after a statement, you may want to use scope_identity() instead of @@identity.
Not entirely sure, but that could be 1 problem in your logging.
Jared
CE - Microsoft
March 28, 2012 at 11:27 am
eshulman (3/28/2012)
The insert is done with an INSERT statement immediately followed by SELECT @@IDENTITY. Since it is a rare occurence (about 10 times per day) our theory has been that SQL server is rolling the transaction back for one reason or another. We tried to catch the rollbacks in SQL Profiler by setting up a ROLLBACK and DEADLOCK trace but got absolutely no results over a 3 day period even though we know we have issues every day (according to our logs). The "insert/select @@ identity" is done through a webservice call which executes the passed in SQL statement.Can somebody suggest a good way to monitor rollbacks, deadlocks etc. in SQL Profiler? We also checked event logs and there is nothing in them either.
Part of your problem may be the select @@identity. I would bet dollars to doughnuts that the insert ran just fine, but that another insert from another session ran between the insert and select, possibly on another table returning a a value that did not exist in the table that the previous insert ran.
From Books Online:
After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
March 28, 2012 at 11:31 am
I would bet dollars to doughnuts that the insert ran just fine, but that another insert from another session ran between the insert and select, possibly on another table returning a a value that did not exist in the table that the previous insert ran.
It is my understanding that identity from ANOTHER session is not picked up. It must be the same session, however a trigger or other operation could generate an identity in the same session, but outside the scope of the statement that you are trying to get the identity from.
Jared
CE - Microsoft
March 28, 2012 at 11:32 am
another issue to consider: is there a trigger on the table in quesiton? if the trigger fails, the insert fails, and the data "disappears" like you seem to be identifying.
could that be an issue?
Lowell
March 28, 2012 at 11:34 am
SQLKnowItAll (3/28/2012)
I would bet dollars to doughnuts that the insert ran just fine, but that another insert from another session ran between the insert and select, possibly on another table returning a a value that did not exist in the table that the previous insert ran.
It is my understanding that identity from ANOTHER session is not picked up. It must be the same session, however a trigger or other operation could generate an identity in the same session, but outside the scope of the statement that you are trying to get the identity from.
Again, from BOL: @@IDENTITY is not limited to a specific scope.
Also, Lowell is correct. If there is a trigger on the table and it fails the transaction rollsback. But if that is the case, i would expect @@IDENTITY to return null. How does the application handle a null value if it is returned? And, of course, I could be wrong in this assumption. Really should test it.
March 28, 2012 at 11:37 am
Lynn Pettis (3/28/2012)
SQLKnowItAll (3/28/2012)
I would bet dollars to doughnuts that the insert ran just fine, but that another insert from another session ran between the insert and select, possibly on another table returning a a value that did not exist in the table that the previous insert ran.
It is my understanding that identity from ANOTHER session is not picked up. It must be the same session, however a trigger or other operation could generate an identity in the same session, but outside the scope of the statement that you are trying to get the identity from.
Again, from BOL: @@IDENTITY is not limited to a specific scope.
Exactly, I was just correcting "that another insert from another session ran between the insert and select." That is irrelevant... What is relevant is any other identities generated in the current session outside of the current scope (for @@identity, not scope_identity()).
Jared
CE - Microsoft
March 28, 2012 at 11:37 am
We do not have any triggers on the table that we are having issues with. We also have researched the logged identity for "disappearing" records and they are in correct sequence for the identity column but as another poster stated the identity column value is not rolled back so we have a gap.
March 28, 2012 at 11:43 am
eshulman (3/28/2012)
We do not have any triggers on the table that we are having issues with. We also have researched the logged identity for "disappearing" records and they are in correct sequence for the identity column but as another poster stated the identity column value is not rolled back so we have a gap.
Ok, so you ARE seeing gaps. So that means that the transaction is being rolled back (I think you gathered this by now ;). So... Why? Any thoughts based on information that you have not given us; i.e. scripts and DDL for the tables in question? **HINT** **HINT**
Jared
CE - Microsoft
March 28, 2012 at 11:49 am
SQLKnowItAll (3/28/2012)
Lynn Pettis (3/28/2012)
SQLKnowItAll (3/28/2012)
I would bet dollars to doughnuts that the insert ran just fine, but that another insert from another session ran between the insert and select, possibly on another table returning a a value that did not exist in the table that the previous insert ran.
It is my understanding that identity from ANOTHER session is not picked up. It must be the same session, however a trigger or other operation could generate an identity in the same session, but outside the scope of the statement that you are trying to get the identity from.
Again, from BOL: @@IDENTITY is not limited to a specific scope.
Exactly, I was just correcting "that another insert from another session ran between the insert and select." That is irrelevant... What is relevant is any other identities generated in the current session outside of the current scope (for @@identity, not scope_identity()).
Okay, did some testing and I stand corrected. And, if there is a rollback you still get the identity value that would have been inserted.
March 28, 2012 at 11:53 am
SQLKnowItAll (3/28/2012)
eshulman (3/28/2012)
We do not have any triggers on the table that we are having issues with. We also have researched the logged identity for "disappearing" records and they are in correct sequence for the identity column but as another poster stated the identity column value is not rolled back so we have a gap.Ok, so you ARE seeing gaps. So that means that the transaction is being rolled back (I think you gathered this by now ;). So... Why? Any thoughts based on information that you have not given us; i.e. scripts and DDL for the tables in question? **HINT** **HINT**
So, question. Why didn't the trace capture the rollback, if one occurred? The OP did indicate that they ran a trace looking for rollbacks and deadlocks.
March 28, 2012 at 11:56 am
Lynn Pettis (3/28/2012)
SQLKnowItAll (3/28/2012)
eshulman (3/28/2012)
We do not have any triggers on the table that we are having issues with. We also have researched the logged identity for "disappearing" records and they are in correct sequence for the identity column but as another poster stated the identity column value is not rolled back so we have a gap.Ok, so you ARE seeing gaps. So that means that the transaction is being rolled back (I think you gathered this by now ;). So... Why? Any thoughts based on information that you have not given us; i.e. scripts and DDL for the tables in question? **HINT** **HINT**
So, question. Why didn't the trace capture the rollback, if one occurred? The OP did indicate that they ran a trace looking for rollbacks and deadlocks.
I was hoping you could answer that! 🙂 I don't have as much experience with traces...
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply