Why some records could not be deleted

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

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:

SQL

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

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?

Posted 3-Mar-13 4:01am

daiwuju

Add a Solution

Comments

Sandeep Mewara 3-Mar-13 10:16am

Do you see any error for the not-deleted rows? Any log about it? What happen when you try to run the query to delete them. Tracked anything?

Ankit_Sharma1987 3-Mar-13 10:17am

Have you used Primary Key RelationShip?

daiwuju 4-Mar-13 4:39am

Yes, id is the Primary Key.

RDBurmon 3-Mar-13 10:48am

Did you setup ROWCOUNT before ran this procedure ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Answer 1 · 2013-03-03T07:20:00

Solution 1

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

SQL

-- 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;

See BEGIN TRANSACTION (Transact-SQL)[^]

Posted 3-Mar-13 7:20am

Mike Meinz

Updated 4-Mar-13 2:05am

v3

Comments

Maciej Los 3-Mar-13 14:02pm

This is it!
+5!

daiwuju 4-Mar-13 7:46am

Thank your very much, Mike Meinz. I have tried your code, and failed. Now, I plan to add some code to check whether the SMS_sumit is empty, that is if not empty, do the procession again.

Mike Meinz 4-Mar-13 8:03am

How did my code fail? Do I have a syntax error? Which statement?

You did not answer my question. Is it possible for another process to insert one or more rows into SMS_Submit while your stored procedure is running?

daiwuju 4-Mar-13 20:52pm

First, I am very sorry for the use of word "fail". The records left in the table has been reduced greatly to about 0.4% when the record count is more than 1200 if your code runs. I think your last question must be the answer to my problem - there must an other stored procedure that does something to the table. I will check the whole project now.

Thanks!

Mike Meinz 5-Mar-13 7:49am

Thanks for your response. Please reply here to let us know the root cause.

daiwuju 6-Mar-13 9:16am

It is a stupid mistake. The records, which were not be successfully by another application program until 300 seconds after all the records in SGIP_Submit have been sent to the application program, should be put into another table SGIP_Plan. If the resent choice is made, the records in SGIP_Plan should be inserted back to SGIP_Submit and sent again to the application program. But in the code, the records in the SGIP_Plan is inserted back to SGIP_Submit after 300 second when it was sent from SGIP_Submit for the first time. So for a large number records which causes a long sending time, there would be some unsuccessfully processed records which is inserted back after it has been deleted and before all the records have been sent.

Mike Meinz 6-Mar-13 11:05am

Thank you for the follow-up. I hope you continue to use the complete set of SQL statements that I provided in Solution 1. This is the proper way to move rows from one table to another and be assured that none are lost. Both the setting and use of ToBeTransferred and the Begin Transaction and Commit Tranaction are important to this process.