为什么有些记录无法删除 [英] Why some records could not be deleted

问题描述

我在SQL Server 2008中有两个表，SMS_bill和SMS_submit。 SMS_submit中的记录逐个读取并插入到SMS_bill中，然后发送到另一个应用程序进行处理，并在读取后删除。代码是：

I have two table in SQL Server 2008, SMS_bill and SMS_submit. Records in SMS_submit is read one by one and inserted into SMS_bill, and then sent to another application program to be processed, and deleted after reading. The code is:

CREATE PROCEDURE [sp_sms_submit] 
	@id int,
	@result int 
AS
BEGIN
	SET NOCOUNT ON;
	insert into SMS_bill (UserNumber, MessageCoding, MessageContent)
	select UserNumber, MessageCoding, MessageContent 
	from SMS_submit 
	where id = @id
	delete from SMS_submit 
	where id=@id
END
GO

如果记录数不大，例如，小于20，代码每次都运行Ok。但是，如果记录计数很大，例如超过100，则将有大约2％的记录未被删除并保留在SMS_submit中。任何人都可以给我一些提示吗？

If the record count is not large, for example less than 20, the code runs Ok everytime. But if the record count is large, for example more than 100, there will be about 2% records which were not deleted and left in SMS_submit. Could anyone give me some tips?

推荐答案

当这个存储过程运行时，是否有可能另一个进程在SMS_Submit中插入一行或多行？如果是这样，那将解释为什么某些行不会被删除。这也意味着你将有一些行在插入SMS_Bill之前被删除，这意味着你将丢失数据。



确保只有那些行已被插入SMS_Bill从SMS_Submit中删除，如果有问题，数据库将保持一致，您应该执行以下操作：

1.添加名为ToBeTransferred的列到SMS_Submit表

2.使用此代码将行从SMS_Submit传输到SMS_Bill

Is it possible for another process to insert one or more rows into SMS_Submit while this stored procedure is running? If so, that would explain why some rows are not deleted. It would also mean that you will have some rows that get deleted before they are inserted into SMS_Bill which means you will lose data.

To ensure that only those rows that have been inserted into SMS_Bill are deleted from SMS_Submit and that if there is a problem the database will remain consistent, you should do the following:
1. Add a column named ToBeTransferred to SMS_Submit table
2. Use this code to do the transfer of rows from SMS_Submit to SMS_Bill

-- Marks the starting point of a transaction
BEGIN TRANSACTION MOVESMSROWS WITH MARK;
-- Mark rows that will be moved
Update SMS_Submit Set ToBeTransferred=1 Where id=@id;
-- Move the rows that have been marked for movement
Insert into SMS_Bill (UserNumber, MessageCoding, MessageContent)
Select UserNumber, MessageCoding, MessageContent from SMS_Submit 
    Where id = @id AND ToBeTransferred=1;
-- Delete the rows that have been moved
Delete from SMS_Submit where id=@id AND ToBeTransferred=1;
--Commit changes
COMMIT TRANSACTION MOVESMSROWS;

参见BEGIN TRANSACTION（Transact-SQL） [ ^ ]

这篇关于为什么有些记录无法删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！