Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sengoku
Starting Member
29 Posts |
 Posted - 2003-09-04 : 12:04:05
|
| hey there peepsi've found a lot of answers on this board before, so please forgive me for another (probably simple) question, but you guys = solutions in my mind :Danyway, on to the problem.we have a procedure (in an ASP page) that deals with someone taking ownership of an item. it reads the details out of the 'open transfers' table, then processes the taking of ownership, and finally deletes the row from the 'open transfers' table.however, the trouble is, if 2 people click the button to take ownership at the same time, it runs twice! (which we absolutely do NOT want to happen, it should be first come, first get)here's the paraphrased code :connobj.begintransrsRecordset.Source="SELECT * from data_opentransfers where trans_au=" & idrsRecordset.Openif not rsRecordset.EOF then ' code to check whether they can take ownership hereend if if can_transfer then ' transfer the item to the new owner (it still needs to read some fields from rsRecordset here for processing) connobj.Execute("delete from data_opentransfers where trans_au=" & id)end ifrsRecordset.Closeconnobj.committransi was thinking that because it's all encapsulated in a transaction, shouldn't SQL lock the tables (or at least rows) involved until it's dealt with 1 person fully? or am i barking up the wrong tree?i hope i've explained this properly, just ask if you need any further info...thanks in advance! |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-04 : 12:16:52
|
quote:
however, the trouble is, if 2 people click the button to take ownership at the same time, it runs twice! (which we absolutely do NOT want to happen, it should be first come, first get)
Which is what's happening...but one of your deletes fail, right?I guess you could use HOLDLOCK, or update the row with an indicator saying that the row is checked out...but in either case you'll need to error handle each caseclose?Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
sengoku
Starting Member
29 Posts |
Posted - 2003-09-04 : 12:27:52
|
quote:
Originally posted by X002548
quote:
however, the trouble is, if 2 people click the button to take ownership at the same time, it runs twice! (which we absolutely do NOT want to happen, it should be first come, first get)
Which is what's happening...but one of your deletes fail, right?I guess you could use HOLDLOCK, or update the row with an indicator saying that the row is checked out...but in either case you'll need to error handle each caseclose?
i don't think the delete fails per se, the entire proc seems to finish correctly (the information it stores in each person's 'diary' is updated, and if the transaction failed, it should back the entire thing out, shouldn't it?) - i think it's more a synchronicity thing...but i'll definitely check out the HOLDLOCK thing, thanks for that! :)[edit : PERFECT! thanks a lot! :)] |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-04 : 12:54:50
|
quote:
i don't think the delete fails per se, the entire proc seems to finish correctly (the information it stores in each person's 'diary' is updated, and if the transaction failed, it should back the entire thing out, shouldn't it?) - i think it's more a synchronicity thing...
yeah, it doesn't fail...when you try to delete something that isn't there you still get 0 for @@ERROR...If you check out @@ROWCOUNT you'll see you didn't delete anything...which to me would be an error in that case..Why don't you do this in a stored procedure?Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
|
|
|
|
|
synchronicity... :)