Insert failing silently, sometimes 2

[sheila11]

Hi all,

I have a very simple stored proc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure MyProc
as
Begin Tran
Insert into myTbl(....) Values(....)
Commit Tran

This is called repeatedly from a multi-user application to insert over a thousand rows at one go. Sometimes (once in a few months) it silently fails. I checked the data when it failed, and the data seems to have had nothing to cause the failing.

Could the explicit transaction be contributing to the failures when the multiple users are calling it simultaneously?

Is there anything I can investigate to find the cause?

TIA,
Sheila

 

[gmmastros]

Do you have any triggers on the table? Is it possible that the trigger(s) could be causing your insert to fail?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sheila11]

No, there are no triggers in the database.

I also ran a profiler while the users were using the application, and saw the calls being made. The data being sent to the proc seemed perfectly normal and well formatted.

Thanks,
Sheila

 

[markros]

If there is another transaction opened and that transaction is rollbacked, then the whole transaction is rallbacked.

Also, if this is just a single insert, why do you need to wrap it in transaction?

PluralSight Learning Library

 

[sheila11]

Hi Markos,

>>If there is another transaction opened and that transaction is rollbacked...

I didn't understand your comment. Do you mean some other stored proc is affecting this proc?

>>Also,...why do you need to wrap it in transaction?
I don't think I need to. This is old code, with this strange problem, given to me to resolve the problem. I have removed those explicit transactions. The Autotansaction is on, and should work.

What I am wondering is: Could the explicit transaction have been the cause of the problem? I don't want to wait for a month to find that the problem still exists.

Thanks,
Sheila

 

[markros]

I meant, if this SP is called from another SP, for example, and that SP has its own transaction opened, then if that outer SP does a rollback, the record will not be inserted.

PluralSight Learning Library

 

[sheila11]

No, there is no nested transaction involved in this case.

The C# code calling this proc uses this call:
command.ExecuteNonQuery();

Thanks again,
Sheila

 

[SimonSellick]

I also ran a profiler while the users were using the application, and saw the calls being made.

Did you catch a call that failed?

 

[sheila11]

SimonSellick,

No Unfortunately the failure occurs silently, and seldom.

I made sure that the parameters being passed are formatted properly so the DB won't reject them. e.g. the float and date params are in format that the DB expects.

Thanks,
Sheila

 

[SimonSellick]

How sure are you that the call is really happening? Silent failure doesn't sound like Microsoft, unless you have specifically told the db to ignore particular types of error.

Does your call depend on any implicit data type conversions? They sometimes have unexpected results. Are null parameters correctly catered for?

Can you add some code around the insert to check that it is actually being called when it fails? Ideally, a log of the call parameters, written by the C# caller; and a corresponding one written by SQL Server before and after the insert, so that you could wait for a failure then see the actual call made in the C# log and what happened to it in the SQL Server log. There would obviously be a performance hit.

Simon

 

[markros]

Just came across this article

http://www.simple-talk.com/sql/t-sql-programming/developing-modifications-that-survive-concurrency/

that may shed some light on this problem (I only glanced through, but the top portion seems to address this problem).

PluralSight Learning Library

 

[sheila11]

Thanks, markros. This article is really good at making me think.

Thanks, Simon. I have now added C# code to log the state, and am waiting for the culprit.

I'll update this thread when I settle this issue.

Thanks again,
Sheila

 

[markros]

What is the status of this problem?

BTW, I'm re-reading this article now and I see that Alex talks about silent lost updates under high concurrency here

(close to the end of the article):

Listing 23: A loop that invokes UpdateWebPageStats to increment NumVisits for one and the same row 10,000 times in a loop

PluralSight Learning Library