Database record disappear

[salamol]

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.

[Greg D. Moore \(Strider\)]

"salamol" <sa*****@hotmail.com> wrote in message
news:ch********@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.

[Hugo Kornelis]

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)

[Erland Sommarskog]

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, es****@sommarskog.se

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

[Erland Sommarskog]

salamol (sa*****@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, es****@sommarskog.se

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

[salamol]

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" <es****@sommarskog.se> ???
news:Xn*********************@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, es****@sommarskog.se

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

[Hugo Kornelis]

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)

[Erland Sommarskog]

salamol (sa*****@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, es****@sommarskog.se

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

[salamol]

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" <es****@sommarskog.se> ???
news:Xn**********************@127.0.0.1 ???...

salamol (sa*****@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, es****@sommarskog.se

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

[Erland Sommarskog]

salamol (sa*****@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, es****@sommarskog.se

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

[Jean Bulinckx]

Hi,

I experienced data disappear. Four tables with a 1-n relationship are
heavily updated and are target of some big queries.

Cenario:
Table A with ~3.000 rows sometimes rollback updates
Table B with ~5.000 rows sometimes rollback updates and inserts
Table C with ~60.000 rows have several rollbacked inserts
Table D with ~2.500.000 rows is havely rollbacked

The workflow:
1) Users inserts a row at table A
2) Users inserts one or more rows at B
3) Users inserts lots of rows at C
4) Users inserts lots of rows at D
5) Users updates D
6) Users updates B and C
7) Users updates A and prints a report with all content from the four
tables concerning with the row from A
8) Minutes or hours later users checks for that report and updates A
9) Days later users print a big report from all month data

Problem:
At steps 8) and 9) users tell some rows from some tables are vanished!

Details:
All update, delete and insert are performed by Stored Procedures.
Some selects are performed by no indexed views.
Some selects use more than 12 joins!
Reports are printed before the data vanished.
Time to data disapear (at users discretion) varies from minutes to days.
Each insert, each update, each delete for EACH ROW, is a explicit
transaction.
I´m using MSSQLServer 2000 last sp and Windows Server 2000.
The server and users lies at the same network, the users uses a client
application (not web).
The user´s app connects with BDE but i coded a newer version with ADO
but this not worked.
All worked fine for sometime but sundenly this problem appears.
Users tell the loss of data is harder now the loss is growing.
The LAN posses 2 hub is slowdown from 100MB to 10MB all network (the
number of users is constantly growing).
All server memory is used, some locks take too long some deadlocks
errors arise.
I put a trigger to make sure any insert/update at the problematic tables
inputs a row at a dummy table (since i have the report i can search for
the row) no missing row are found at the dummy table.
Some triggers make a complex validations at some inserts/tables and
roolbacks the transaction, but when tested these triggers works fine.
I run checkdb, rebuild indexes checked realtionships, stored procedures,
application, triggers, etc.

Conclusions:
We need a better server with a lot more memory and a rebuild at the
network. ;)
This problem can be from a deadlock for sources like
memory+network+diskIO and the SQLServer cannot dont handle it.
The locks for sources are handled by windows but maybe the OS failed to
comunicate deadlocks problems to SQLserver.

Workaround:
I activated de server log to write any deadlock, timeout is -1(infinite)
a new server (and switches) are arriving shortly.
I hope this solves the problem. Someone experienced data loss like it?

Jean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!