SEO

I has a strange question.

My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @@ERROR = 0 and retrieve the @@IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @@ERROR is equals to 0 and I can get a value from
@@IDENTITY but when I fetch the record by the primary key (the value of
@@IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @ERROR = 0 and the stored procedure can return
value from @@IDENTITY?? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.

"salamol" <salamol@hotmail.com> wrote in message
news:chm1a4$bb2@imsp212.netvigator.com...
> I has a strange question.
>
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @@ERROR = 0 and retrieve the @@IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @@ERROR is equals to 0 and I can get a value from
> @@IDENTITY but when I fetch the record by the primary key (the value of
> @@IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @ERROR = 0 and the stored procedure can return
> value from @@IDENTITY?? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.

My guess is something is causing the transaction to rollback. Best bet is
to post the code, DDL and repro script if at all possible.

Remember, @error gets set after every statement, not just at the end.


>
>

On Wed, 8 Sep 2004 12:20:12 +0800, salamol wrote:

>I has a strange question.
>
>My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
>The system is busy and handle a lot of SELECTs and INSERTs all the time.
>Sometimes, some transactions are blocked by some other transactions.
>For those INSERT transactions, we usually call a stored procedure and pass
>parameters in to do the INSERT, at the end of the INSERT stored procedure,
>we always check @@ERROR = 0 and retrieve the @@IDENTITY (it is usually the
>auto number primary key) to confirm the INSERT is success. But the strange
>things is, sometimes, @@ERROR is equals to 0 and I can get a value from
>@@IDENTITY but when I fetch the record by the primary key (the value of
>@@IDENTITY) it returns nothing!! The record is disappear and the primary
>key is skipped! I found that this happens usually when the INSERT execute
>at the time when some other transactions are blocking. Anyone knows why the
>record is disappear while @ERROR = 0 and the stored procedure can return
>value from @@IDENTITY?? Anyone has such case happen in their server as
>well? Please tell me some solutions on how to solve this, thank you x
>10000000 times.
>

Hi salamol,

If you were using SQL Server 2000, I'd advise you to use SCOPE_IDENTITY
instead. Unfortunately, @@IDENTITY gives you the last identity value used,
not limited to the current scope. I guess the scenario is something like
this:

* Connection 1 starts an insert.
* While connection 1 is processing, connection 2 initiates an insert as
well. This one is blocked.
* When connection 1's insert is finished, connection 2's insert starts.
* When processing of connection 1 reaches the statement where @@IDENTITY
is retrieved, the insertion of connection 2 is already finished and the
@@IDENTITY value returned to connection 1 will be for conenction 2's row.
* After that, connection 2 has to do a rollback. There now no longer is a
row with the @@IDENTITY value saved by conneciton 1 (that actually
belonged to connection 2).

Since you don't have SQL Server 2000, you can't use SCOPE_IDENTITY
instead. Another alternative is to read back the data. Your table should
have a column (or combination of columns) that form the natural key for
the data in the table. Use this in a WHERE clause to SELECT the identity
value assigned to the row just inserted.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> * Connection 1 starts an insert.
> * While connection 1 is processing, connection 2 initiates an insert as
> well. This one is blocked.
> * When connection 1's insert is finished, connection 2's insert starts.
> * When processing of connection 1 reaches the statement where @@IDENTITY
> is retrieved, the insertion of connection 2 is already finished and the
> @@IDENTITY value returned to connection 1 will be for conenction 2's row.

Sorry Hugo, but this is plain wrong. @@identity is global to the connection,
not global to the server. This cannot happen.

What can happen is that you insert a row into a table and that table has
a trigger which also inserts a row into a table with the IDENTITY property.
In this case @@identity will return the value of the row into the second
table, and you have no good way of getting the value for the first table.

In SQL7 RTM there was also a bug which, if memory serves, caused @@identity
to be NULL, if your trigger inserted into a non-identity table.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

salamol (salamol@hotmail.com) writes:
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions. For
> those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored
> procedure, we always check @@ERROR = 0 and retrieve the @@IDENTITY (it
> is usually the auto number primary key) to confirm the INSERT is
> success. But the strange things is, sometimes, @@ERROR is equals to 0
> and I can get a value from @@IDENTITY but when I fetch the record by the
> primary key (the value of @@IDENTITY) it returns nothing!! The record
> is disappear and the primary key is skipped! I found that this happens
> usually when the INSERT execute at the time when some other transactions
> are blocking. Anyone knows why the record is disappear while @ERROR = 0
> and the stored procedure can return value from @@IDENTITY?? Anyone has
> such case happen in their server as well? Please tell me some solutions
> on how to solve this, thank you x 10000000 times.

There is just far too little information to say something really useful.

But just checking @@error and @@identity is not enough. If you have
transactions they can be rolled back, for instance because of deadlock.

Also, a nasty thing which may happen in a busy system which is not properly
implemented is that blocking leads to clients timing out. Now, assume that
a client calls a stored procedure, which starts a transaction, and after
30 seconds the default timeout of the client sets in, so the query is
cancelled. This does *not* rollback the transaction! This means that the
client may job along with the transaction and everything looks fine -
and then, there is a deadlock or some other error which cancels the batch,
and everything since 10 o'clock this morning is rolled back.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

The problem I am facing is neither cannot get an identity (identity not
null) nor getting a wrong identity.
the server returns a correct identity value which should belongs to the
record that I just insert, but when I fetch the record by that identity
value, the record is missing
i.e. the identity for the last record is 100, and I do an insert, the system
return 101 as @@identity and 0 as @@Error, however if i select the record
from 95 - 105 it will returns records 95, 96, 97, 98, 99, 100, 102, 103,
104, 105 (i.e. 102-105 is other successfully insert after 101)

"Erland Sommarskog" <esquel@sommarskog.se> ???
news:Xns955F4D9B18C7Yazorman@127.0.0.1 ???...
> Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> > * Connection 1 starts an insert.
> > * While connection 1 is processing, connection 2 initiates an insert as
> > well. This one is blocked.
> > * When connection 1's insert is finished, connection 2's insert starts.
> > * When processing of connection 1 reaches the statement where @@IDENTITY
> > is retrieved, the insertion of connection 2 is already finished and the
> > @@IDENTITY value returned to connection 1 will be for conenction 2's
row.
>
> Sorry Hugo, but this is plain wrong. @@identity is global to the
connection,
> not global to the server. This cannot happen.
>
> What can happen is that you insert a row into a table and that table has
> a trigger which also inserts a row into a table with the IDENTITY
property.
> In this case @@identity will return the value of the row into the second
> table, and you have no good way of getting the value for the first table.
>
> In SQL7 RTM there was also a bug which, if memory serves, caused
@@identity
> to be NULL, if your trigger inserted into a non-identity table.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

On Wed, 8 Sep 2004 22:30:07 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
>> * Connection 1 starts an insert.
>> * While connection 1 is processing, connection 2 initiates an insert as
>> well. This one is blocked.
>> * When connection 1's insert is finished, connection 2's insert starts.
>> * When processing of connection 1 reaches the statement where @@IDENTITY
>> is retrieved, the insertion of connection 2 is already finished and the
>> @@IDENTITY value returned to connection 1 will be for conenction 2's row.
>
>Sorry Hugo, but this is plain wrong. @@identity is global to the connection,
>not global to the server. This cannot happen.
>
>What can happen is that you insert a row into a table and that table has
>a trigger which also inserts a row into a table with the IDENTITY property.
>In this case @@identity will return the value of the row into the second
>table, and you have no good way of getting the value for the first table.
>
>In SQL7 RTM there was also a bug which, if memory serves, caused @@identity
>to be NULL, if your trigger inserted into a non-identity table.

Hi Erland,

Thanks, Erland, for putting me straight. And apologies to the OP for the
confusion I may have caused. I guess it shows that I hardly use IDENTITY
in my own code....

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

salamol (salamol@hotmail.com) writes:
> The problem I am facing is neither cannot get an identity (identity not
> null) nor getting a wrong identity. the server returns a correct
> identity value which should belongs to the record that I just insert,
> but when I fetch the record by that identity value, the record is
> missing i.e. the identity for the last record is 100, and I do an
> insert, the system return 101 as @@identity and 0 as @@Error, however if
> i select the record from 95 - 105 it will returns records 95, 96, 97,
> 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> insert after 101)

If you have received 101 in @@identity for an inserted row, and the row
is later nowhere to be found, the insertion of the row has been rolled
back, as I discussed in my other post.

While you may already be aware of this, I like to point out that it lies
in the concept of the IDENTITY property that you never can trust the numbers
to be contiguous. If you attempt to insert a row into a table, and that
insertion fails for whatever reason, you have still consumed a number.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

OK, so how can I check if the insertion is success or not?
should I fetch for that record again immediate after the insert?


"Erland Sommarskog" <esquel@sommarskog.se> ???
news:Xns955F72B0A42D0Yazorman@127.0.0.1 ???...
> salamol (salamol@hotmail.com) writes:
> > The problem I am facing is neither cannot get an identity (identity not
> > null) nor getting a wrong identity. the server returns a correct
> > identity value which should belongs to the record that I just insert,
> > but when I fetch the record by that identity value, the record is
> > missing i.e. the identity for the last record is 100, and I do an
> > insert, the system return 101 as @@identity and 0 as @@Error, however if
> > i select the record from 95 - 105 it will returns records 95, 96, 97,
> > 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> > insert after 101)
>
> If you have received 101 in @@identity for an inserted row, and the row
> is later nowhere to be found, the insertion of the row has been rolled
> back, as I discussed in my other post.
>
> While you may already be aware of this, I like to point out that it lies
> in the concept of the IDENTITY property that you never can trust the
numbers
> to be contiguous. If you attempt to insert a row into a table, and that
> insertion fails for whatever reason, you have still consumed a number.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

salamol (salamol@hotmail.com) writes:
> OK, so how can I check if the insertion is success or not?
> should I fetch for that record again immediate after the insert?

I have to ask a question: have you read my discussion about transaction
being rolled back? You seem to be obsessed with the problem that the
insertions may fail. But if insertion is successful does not help, if
the transaction is rolled back later.

It is difficult to add more to the subject, unless you show us some of the
code you are having problems with.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp