SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 concurrency handling
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mhsn1985
Starting Member

2 Posts

Posted - 11/12/2012 :  13:30:41  Show Profile  Reply with Quote
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
6059 Posts

Posted - 11/12/2012 :  14:14:47  Show Profile  Reply with Quote
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
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 11/13/2012 :  01:10:48  Show Profile  Reply with Quote
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.
Go to Top of Page

mhsn1985
Starting Member

2 Posts

Posted - 11/18/2012 :  13:33:09  Show Profile  Reply with Quote
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)?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000