数据库记录消失 [英] Database record disappear
问题描述
我有一个奇怪的问题。
我的公司正在使用Win NT 4.0 Server + MS SQL 7.0的旧系统。
系统正忙并且一直处理很多SELECT和INSERT。
有时，某些事务会被其他一些事务阻塞。
对于那些INSERT事务，我们通常会调用一个存储过程并在INSERT存储过程结束时传入
参数进行INSERT，
我们总是检查@@ ERROR = 0并检索@@ IDENTITY（它通常是
自动编号主键）来确认INSERT是否成功。但奇怪的是
的东西，有时，@ @ ERROR等于0，我可以从
@@ IDENTITY获得一个值，但当我从主要文件中获取记录时键（
@@ IDENTITY的值）它什么都不返回!!记录消失，主要的
键被跳过！我发现这通常发生在INSERT在其他一些事务阻塞时执行
时。任何人都知道为什么
记录在@ERROR = 0时消失，并且存储过程可以从@@ IDENTITY返回
值？任何人都有这样的情况发生在他们的服务器上
好吗？请告诉我一些如何解决这个问题的解决方案，谢谢x
10000000次。
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" < SA ***** @ hotmail.com>在消息中写道
news：ch ******** @ imsp212.netvigator.com ...
"salamol" <sa*****@hotmail.com> wrote in message
news:ch********@imsp212.netvigator.com...
我有一个奇怪的问题。
<我的公司正在使用一个带有Win NT 4.0 Server + MS SQL 7.0的旧系统。
系统很忙并且一直处理很多SELECT和INSERT。
有时，某些事务被阻止对于那些INSERT事务，我们通常调用一个存储过程并传入
参数来执行INSERT，在INSERT存储过程结束时，我们总是检查@ @ERROR = 0并检索@@ IDENTITY（通常是
自动编号主键）以确认INSERT是否成功。但是
奇怪的事情，有时，@@ ERROR等于0，我可以从
@@ IDENTITY获得一个值，但是当我通过主键获取记录时（
@@ IDENTITY）它什么都不返！记录消失，主要
键被跳过！我发现这通常发生在INSERT执行
时，其他一些事务阻塞。任何人都知道为什么
记录在@ERROR = 0时消失，并且存储过程可以从@@ IDENTITY返回
值？任何人都有这种情况发生在他们的服务器上好吗？请告诉我一些如何解决这个问题的解决方案，谢谢x / 10000000次。
我的猜测是导致事务回滚的原因。最好的选择是
如果可能的话发布代码，DDL和repro脚本。
记住，@ error在每个语句后都设置好，而不仅仅是在结束。
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.
2004年9月8日星期三12:20:12 +0800，salamol写道：
On Wed, 8 Sep 2004 12:20:12 +0800, salamol wrote:
我有一个奇怪的问题。
我的公司正在使用Win NT 4.0 Server + MS SQL 7.0的旧系统。
系统正忙着处理一直有很多SELECT和INSERT。
有时，某些事务会被其他一些事务阻塞。
对于那些INSERT事务，我们通常会调用一个存储过程并传递
参数来做INSERT，在INSERT存储过程结束时，我们总是检查@@ ERROR = 0并检索@@ IDENTITY（它通常是
自动编号主键）以确认INSERT是否成功。但奇怪的是，有时，@@ ERROR等于0，我可以从
@@ IDENTITY获得一个值但是当我通过主键获取记录时（
@@ IDENTITY）它什么都不返！记录消失，主要
键被跳过！我发现这通常发生在INSERT执行
时，其他一些事务阻塞。任何人都知道为什么
记录在@ERROR = 0时消失，并且存储过程可以从@@ IDENTITY返回
值？任何人都有这种情况发生在他们的服务器上好吗？请告诉我一些如何解决这个问题的解决方案，谢谢x / / 10000000次。
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，
如果您使用的是SQL Server 2000，我建议您使用SCOPE_IDENTITY
代替。不幸的是，@@ IDENTITY为您提供了最后使用的身份值，
不限于当前范围。我猜这个场景类似于
这个：
*连接1开始插入。
*连接1处理时，连接2启动插入
。这个被阻止。
*当连接1'的插入完成时，连接2'的插入开始。
*当连接1的处理到达声明时@@ IDENTITY
被检索，连接2的插入已经完成，返回连接1的
@@ IDENTITY值将用于连接2'的行。
*之后，连接2必须进行回滚。现在不再有连接1保存的@@ IDENTITY值的
行（实际上
属于连接2）。
由于你没有SQL Server 2000，你不能使用SCOPE_IDENTITY
。另一种方法是回读数据。你的表应该
有一列（或列的组合），它们构成了表中数据的自然键。在WHERE子句中使用它来选择分配给刚插入的行的身份
值。
Best，Hugo
- -
（删除_NO_和_SPAM_以获取我的电子邮件地址）
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）写道：
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
*连接1开始插入。
*当连接1处理时，连接2启动插入
。这个被阻止。
*连接1'的插入完成后，连接2'的插入开始。
*当连接1的处理到达@@ IDENTITY
的声明时检索后，连接2的插入已经完成，返回连接1的
@@ IDENTITY值将用于连接2'的行。
* 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.
对不起雨果，但这是完全错误的。 @@ identity对于连接是全局的，
不是服务器的全局。这不可能发生。
可能发生的是你在一个表中插入一行并且该表有一个
a触发器，它也会在一个表中插入一行IDENTITY属性。
在这种情况下，@@ identity会将行的值返回到第二个
表中，并且您没有很好的方法来获取该值第一个表。
在SQL7 RTM中还有一个错误，如果内存服务，导致@@ identity
为NULL，如果您的触发器插入一个非身份表。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books .asp
这篇关于数据库记录消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
