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 |
|
mhsn1985
Starting Member
2 Posts |
 Posted - 2012-11-12 : 13:30:41
|
| Hi, I've been dealing with a concurrency issue for a couple of days. I've done some research, got a few ideas, but I'd like to ask some experienced members how to approach this problem.I found some topics on using timestamps to handle the situation, but it really didn't seem like an efficient solutionHere is the scenarioI have 3 master tables T1, T2, T3I have a table T4 which has 3 fields which point to the primary keys in T1, T2 and T3There's a procedure (called from some .NET app) which takes 3 primary keys from T1, T2 and T3 as parameters and inserts those values into T4The problem...Once any one key from T1, T2 or T3 has been used in T4, it can no longer be inserted T4, kind of a 1 to 1 relationshipSeveral users use the application simultaneously and they all call that SPHow would I go about writing the SP that will look something like this?Lock the rows in T1, T2 and T3 which the primary keys were paased as paramters to the SP (serializable lock so no one else can read/write anything to those rows)Check T4 to see if any of the parameters have been usedIf neither of the keys were in T4, then insert a row into T4 with the values passed as parametersIf any one of the values exist in T4, then don't insert anything and prompt the userUnlock all locked rows and commit all changesThanks in advance for your help |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-12 : 14:14:47
|
| If the user transaction is only inserting to T4 (and not also to T1-3) then let sql server alone handle currency. You don't need to manage locking and/or explicit transactions. just insert rows that don't exist. You can use a return code, or output variable, or raiserror, or result set to indicate to the application what the user should see.Be One with the OptimizerTG |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-11-13 : 01:10:48
|
| As far as I can tell, you need 3 unique constraints on T4. One for each FK to T1, T2 & T3.If you get a unique constraint violation then you can't do it and you need to "prompt the user" or whatever it is you do to resolve it.I could be missing something though. |
|
|
|
mhsn1985
Starting Member
2 Posts |
Posted - 2012-11-18 : 13:33:09
|
| The unique constraints are a good idea. I hadn't thought of that. thanksBut let's say I wanted to do the job without unique and use row locks instead. How would I go about locking the rows I need in the procedure and they doing the rest of the job?If the procedure is run by 3 users and they all want the same data and they each get just 1 of the rows (User 1 gets the row in T1, user 2 gets the row in T2, user 3 gets the row in T3 - a deadlock situation) how does SQL server handle this dead lock? can I force it to act they way that I want in case of a deadlock (like define the waiting time before kicking someone from the wait cycle)? |
|
|
|
|
|
|
|
|
concurrency handling