March 21, 2002 at 11:53 am
I have a system that updates adds a new row to a table and updates an existing row in a seperate table for an in house auction system.
Basically I have an items table which contains the items and the last bid and the last bidder. I then have a bids table which holds the list of all bids.
What is the best method to allow both of these actions to occur without the posibility of another read or write occurring.
Should I be using locking hints?
If I place my code in a stored procedure will that give me any automatic locking? or do I need to do something specifc?
The in house system uses a web server to provide the interface to the system. It chekcs just before it posts the update to see if the value has changed and if so tells the user their bid is not large enough.
Should I actually do this in my sproc and just change the return value to indicate there was a higher bid made.
Also we have people who just hit F5 in their browser right before the auction closes - will the sproc with maybe locking hints still allow this to work?
Chris
March 22, 2002 at 3:50 am
The first thing you should do is put your two database writes into a transaction. This will ensure that they will either both get committed or that both will be aborted if there's a problem, I.E. you won't be left with only one of the two updates being written.
Now to locking, what you are implying is that you want to prevent any other bids being written until your two postings have been committed. This implies full table locking I.E. preventing any other posting to either table until the current one completes. Whoops I can feel another of my hairs turning grey as the prospect of a totally locked application looms!!!!!
If your raison d'etre is to tell the user they've underbid, why not just check immediately after the posting as well as before. You could achieve this in the SP that does the posting by returning the result in an output parameter which in turn gets fed back to the calling app. OK it's another read but in my opinion that's infinetly more desirable than locking the table.
if your user bombs out of IE that shouldn't affect the database, might affect the web-app though
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply