March 30, 2009 at 8:45 am
Its pretty simple to decide if the object i have is new or not.
If they clicked on something existing i have its id if no id then its new.
March 30, 2009 at 8:58 am
longobardia (3/30/2009)
To prevent dupes also you could wrap your insert in "if not exists" t-sql statement with Begin/End.ie:
if not exists ( select * from xxxx where stageid = @stageID and .... )
Begin
insert ...
End
You would have to wrap that in a transaction for it to work, and I think raise its Isolation Level to something higher than Read Committed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 9:00 am
I have gone down that path of having the application take care of deciding if the record exists or not.
But now, I am much wiser and leave that to the stored procedure to decide. In my case, I don't mind the read hit to check if the record exists in the table before I do an insert. This of course, might not be your situation.
That's my opinion and like Forest Gump used to say..."That's all I have to say about that".
AL
March 30, 2009 at 9:03 am
Yes, of course. I didn't elaborate on a the whole solution.
Sorry.
AL
March 30, 2009 at 9:47 am
Well to get into an argument with you but...
It really is the sql server that decides if the record exists or not. The webapplication displays all the ids it received from sql server + one (-1 for a new one) Then i put that id into a parameter and the stored procedure on sql server decides what to do with it.
March 30, 2009 at 4:20 pm
Martin Sujkowski (3/30/2009)
Well to get into an argument with you but...It really is the sql server that decides if the record exists or not. The webapplication displays all the ids it received from sql server + one (-1 for a new one) Then i put that id into a parameter and the stored procedure on sql server decides what to do with it.
This sort of implementation gives the application a lot of leeway to make mistakes.
The webapplication 'caches' the list of ids in the browser - but the rows with those ids could be added, modified or deleted in the database without its knowledge. A malicious user (or buggy code) could also generate spurious ids & inserts.
As far as I can tell from your initial description, the proc is called three times: one is a duplicate call, and the next is a different call. This looks like a 'webapplication' bug to me.
There are two strategies for testing this:
1. Add unique constraints in your test environment, and find the code in the webapplication that fails against these constraints. For example, a unique constraint on RepairID and StartDate would pick up the issue you described in your first post.
2. Count the number of times the stored proc is called, compare it to the number of rows inserted, and the number of times the webapplication changes a record. Any mismatch will identify the source of your problem.
March 31, 2009 at 7:28 am
Journeyman,
I totally concur with your suggestions. To add to your suggestion, to verify that the application is calling the proc more than once, I would fire up the SQL Profiler and keep an eye at the time you hit the submit button.
That will confirm the case.
Al
March 31, 2009 at 7:42 am
Well Gentlemen, those are very sound suggestions... however.
I've been down this road countless times. Stepped through my code,ran the profiler,had other eyes look at my code etc.
No matter what i try i cannot duplicate the error in my dev environment.
As i said in my original post the behevior is very sporadic. Also if you look at my original post again and pay close attention to the StartDate column entries you will notice that
those entries are more or less identical. Since the value in that column is assigned by sql server (defaulted to getdate()) i would say there is no possible way that the code is called multiple times from the web app. It has to be something else.
March 31, 2009 at 7:48 am
Have you discounted the possibility of 2 users entering the same information at the same time or maybe someone trying to break your software and didn't tell you about it?
try doing the test yourself with two sessions or to computers.
AL
March 31, 2009 at 7:50 am
Based on the original code snippet you provided, it looks to me like the .Net application is ALTERING the stored procedure, not calling it. (I believe this might be the case because of the HTML tags you show in the snippet at the very beginning and very end.)
If that is the case, where is the code snippet from .Net that actually calls the stored procedure?
March 31, 2009 at 8:12 am
In my testing i recruited another guy to help me and we synchronized our clicks (the best we could) and still saw nothing.
This is an intranet application and people that use it. Them trying to break it on purpose while not impossible is highly unlikely. They mostly just don't care.
Let me tell you a little about the preventive measures i've taken in my web app to prevent what i'm seeing:
First off all I elminated all buttons - they use scanners to scan a serial number. Each user deals with a different serial number - that's guaranteed. There are no duplicate serial numbers allowed anywhere nor do they exist.
When i get the serial number i either retrieve the object with that serial from a session variable or get it from db (and insert into session) Then i pass the object to my business class which locks it (making sure only one thread at one time can modify it) before attempting to do anything with it. The business class has many rules one of which is "allow only 1 active stage" (that rule was violated in my example)
If all rules are successfully evaluated then the business class calls another class in the data layer to do what needs to be done in the database.
After all is finished the object is unlocked.
That is why i do not believe that my web app is involved.
March 31, 2009 at 8:35 am
Looing at the sample data you provided,
StageID RepairID StageType Result StartDate UserID EndDate Comments LastTestStageID Station
1051324 305259 1 0 2009-03-24 10:31:29.720 phanyv 2009-03-24 13:51:02.400 no video NULL CR-TEST1
1051331 305259 1 0 2009-03-24 10:31:29.783 phanyv 2009-03-24 13:51:19.307 no video NULL CR-TEST1
1051332 305259 1 1 2009-03-24 10:31:29.783 phanyv NULL NULL NULL CR-TEST1
It looks like there is some kind of automation or testing happening and the application is calling the DB at a very fast pace. Reason being that the StageID was bummped by one. The startDate is being passed as a parameter. The procedure should perform a GetDate(). At least I don't see that in the sample proc provided.
Modify the proc so that the startDate is populated by the getdate() SQL function. You also need to determine wether you want to store the time from the client side or the db side, unless the app and the sql server are in the same OS. If it is realtime data being captured for the startDate then I guess it is OK. But the evidence points to your application which is causing the records to be added to the DB.
Al
March 31, 2009 at 8:45 am
Al, the StartDate column i populated by getdate() function. GetDate() is set as default value on the column itself that is why you dont see it in the stored proc.
What i did notice recently however that these "doubles" seem to happen more frequently around obvious network slowdowns.
I don't know .NET deep enough to know if how it handles this sort of situation.
March 31, 2009 at 8:59 am
Sorry about the getdate() before, I guess I didn't read all the posts after a while.
Anyhow, funny you should mention that it happens when there is a network slowdown. I actually had the same situation happen in one of my apps. However, it was related to a submit button not being disabled after it was pressed or clicked on and during a slow network, we would see a lot of these dupes happening. In part, cause the db tables were not properlly designed (someone else did that). After I added the code, to disable it hasn't happened again. Your situation is different though.
Maybe you 'll need to modify the code to see if you can catch the dupe there instead. Keep the last transaction's data around and compare to a new one. If it's the same, discard. But this kinda logic is really not the way to go. However, you might be able to isolate where it is coming from and write to a log file instead.
AL
March 31, 2009 at 9:41 am
It really does sound like a problem with the application, not the database or the procedure. Profiler will let you know if this is happening. Especially if you're using session variables to determine Primary Key or New Record (-1), you could easily run into something like:
{Psuedo Code}
New record created:
Session["PrimaryKey"] = -1
Submit Code:
Session["PrimaryKey"] = ReturnValueFromStoredProc()
Submit 1: Starts procedure with Primary Key = -1
Submit 2: Starts procedure with Primary Key = -1
Submit 1: Inserts record and returns new PK and sets session variable.
Submit 2: Inserts record and returns second new PK and sets session variable overwriting first.
This would be more prevalent in cases where you have network lag as the procedure might not start as quick and the user is more likely to double click. The back button is also an evil that could cause this (though the inserts would be slightly staggered) as you can quickly submit the same form twice with a back button press.
A solution is to make sure you're only using your 'new pk' session variable once. When you first set your PK to -1, also set a flag:
New record created:
Session["PrimaryKey"] = -1
Session["IsNewRecord"] = true
On submit, do something like:
if (not Session["IsNewRecord"])
{
throw error
}
Session["IsNewRecord"] = false
Session["PrimaryKey"] = ReturnValueFromStoredProc()
Now if the user clicks twice the first form will process and insert a record. The second submit will throw an error.
You would still want to stop the user from double posting in the first place by disabling the button (or in your case the scanner as you said you don't have a button?) Otherwise they will think their record was not created as their most recent submit will fail.
The last thing to consider when using session variables is how those variables are shared accross multiple tabs or windows opened by the same browser. Are you sure people are not opening multiple tabs or windows? If they are, it's easy for session variables to get out of synch with what the user is actually doing. If possible I'd use querystring, form or viewstate variables instead (validating them as input of course). I don't think this is your problem here, but since you're using session variables, it is something you have to keep in mind.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply