| Author |
 Topic  |
|
|
mhsn1985
Starting Member
2 Posts |
 Posted - 11/12/2012 : 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 solution
Here is the scenario
I have 3 master tables T1, T2, T3
I have a table T4 which has 3 fields which point to the primary keys in T1, T2 and T3
There'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 T4
The 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 relationship
Several users use the application simultaneously and they all call that SP
How 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 used
If neither of the keys were in T4, then insert a row into T4 with the values passed as parameters
If any one of the values exist in T4, then don't insert anything and prompt the user
Unlock all locked rows and commit all changes
Thanks in advance for your help |
Edited by - mhsn1985 on 11/12/2012 13:40:29
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 11/12/2012 : 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 Optimizer
TG |
 |
|
|
LoztInSpace
Aged Yak Warrior
878 Posts |
Posted - 11/13/2012 : 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 - 11/18/2012 : 13:33:09
|
The unique constraints are a good idea. I hadn't thought of that. thanks
But 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)? |
|
|
| |
Topic |
|
|
|
concurrency handling
