March 31, 2009 at 9:49 am
What you described cannot happen because i do this before changing anything (pseudo code):
Item item=Session["item"];
lock(item) // item==Session["item"] so any subsequent hit has to wait to get access to the object in Session["item"]
{
//do my database changes
item.ID=database.getID()
}
March 31, 2009 at 2:41 pm
I'm not familiar with a 'lock' verb where you say you are locking the Session variable from reading by other threads... but two things: In my understanding of asp.net, there is no 'lock' for a Session variable like there is for an Application variable simply because no other user will be using the same Session.
Secondly, you appear to be 'locking' the instance of the 'item' derived from the Session variable, not the Session variable itself.
So I conclude you indeed have no lock.
Looks to me like you should store the info in the Application variable and lock it using Application.lock until you return from the db update (which is a poor design choice).
Best design choice, as others have said, is to let the stored proc decide whether it is an update or an add, and handle it accordingly, since it all runs at once inside an implied transaction, an thus there is no chance that another user can get the false impression that the id already about to be used is available.
March 31, 2009 at 2:45 pm
well you concluded wrong becuase in my example:
Item item=Session["item"] this statement gives me the reference to the acutal object not a copy of it.
so then when do
lock(item)
{
}
it effectively locks the object in the session variable.
March 31, 2009 at 2:49 pm
Without seeing some sample asp.net code, it is hard to conclude or make a good assesment of the problem at hand.
Post some of the actual code and maybe we can look at and decide.
We are here to help. So give us some code to look at.
However, don't know if this would be the appropriate website to be
posting ASP.NET code though.
Al;-)
March 31, 2009 at 2:55 pm
This post has gone off track because we were discussing where the problem might lie. I believe it lies with sql server while others believe it may be the web app. I know the .Net code does what i said it does because i tested it.
March 31, 2009 at 2:57 pm
Oh, right. You have locked the Session variable for the current user.
Does your code for lock(item) have some way of locking all Session variables of the same name inside the app? As I said, my understanding is that all Session variables are independent of each other, even ones of the same name but running in a different session.
What is this code for 'lock'? Or do you have some way of guaranteeing that there is indeed only one concurrent session? Say your scanner is stuttering and tries to send the same data twice, that would likely start a second session which would not see your lock in the first, and would run mere instants behind the other.
March 31, 2009 at 2:59 pm
Martin Sujkowski (3/31/2009)
This post has gone off track because we were discussing where the problem might lie. I believe it lies with sql server while others believe it may be the web app. I know the .Net code does what i said it does because i tested it.
This question is answered quickly with profiler. If the stored procedure is getting called multiple times, it's the application. If it's called once, something else is going on. But looking at the procedure code, I don't see how that could happen.
March 31, 2009 at 3:00 pm
Well one browser window==one session right?
As long as everything happens in one browser window i'm alright i think
March 31, 2009 at 3:09 pm
In a previous post, I asked if you tested this scenario and you said you said, "Yes", with another person and hitting the submit key or whatever you are using to key in the data to the field with the scanner.
I have suggested using the profiler a few times already as others have too. You really need to QA you App with a lot of different scenarios.
That is the only way you'll catch the problem. Of course keep the profiler recording while that is happening.
Semphore coding in my Unix days was really hairy stuff. With .Net is pretty simple to accomplish such locking. However, why even bother with that stuff. Leave it to the SQL Server to handle the requests. Move some of tha code you are utlizing in the lock event back to the procedure. That is if you are bumping counts for a data key or generating the referential integrity ID yourself.
AL
March 31, 2009 at 3:36 pm
Martin Sujkowski (3/31/2009)
...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.
...
Martin Sujkowski (3/31/2009)
... I believe it lies with sql server while others believe it may be the web app. I know the .Net code does what i said it does because i tested it.
Martin, you need to be honest with yourself...
You have obviously put a lot of effort into testing your .Net Code and Proc under a range of circumstances.
But if you can't duplicate an issue that occurs in production, then you haven't tested your application thoroughly enough.
(Potential testing methods have been covered by multiple previous posters.)
Here is a summary of the advice we've given about the Stored Proc:
1. Use SCOPE_IDENTITY() rather than IDENT_CURRENT
2. Allow the Stored Proc to determine whether the record needs to be inerted or updated
3. Use a transaction within your stored proc to guarantee atomic inserts/updates
4. Create a unique constraint that preserves the integrity of your data
These changes will take advantage of the documented concurrency support of SQL Server to preserve the integrity of your data.
However, they may return errors to your webapplication that will need to be handled gracefully.
If SQL Server still allows bad data in your table once these changes have been made, then we may be able to be more helpful.
But the last page of posts is about .Net, which means you're moving out of our area of collective expertise.
March 31, 2009 at 3:38 pm
As someone else mentioned, using IDENT_CURRENT('SystemRepairStage') is bad. It might not be related to your current problem. It's possible depending on how your code is written and how you handle creating new or existing records. Even if it's not part of your problem, it will (has?) likely cause you a problem down the line. Switch this to scope_identity().
What you're asking for is 'the last ID created in the SystemRepairStage table regardless of who or what process made the record'. If multiple people are creating records at the same time, this will be a problem.
Joe has a new record (-1)
Bob has a new record (-1)
Joe inserts his record (ID created: 10)
Bob inserts his record (ID created: 11)
Joe gets back an Ident_Current of 11.
Bob get back an Ident_Current of 11.
From here you could:
a) Put the retrieved ID on your record in memory and keep using it. That means Joe has Bob's ID on Joe's data. If he does an update he will ruin record #11.
b) Refresh data in the application based on the retrieved ID. Now Joe is looking at Bob's record. He should notice something is up right away. At least he can't save his #10 data over record #11, though.
April 1, 2009 at 6:25 am
Thank you everyone for the advise.
I changed all my stored procedures to use Scope_Identity. I do believe that majority of my problems were a direct result of the situation described by JiBlack and manifested themselves as multiple inserts. I will keep an on it for a while and reply back in a 2-3 weeks (before i was seeing extra insterts with about that frequency)
April 7, 2009 at 11:51 am
Well, its been a week and i have not seen a single duplicate. I think its fairly safe to say this has been resolved by changing ident_Current
to scope_identity
Thank You all for your help!
April 7, 2009 at 6:42 pm
Glad we could help!
[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]
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply