SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concurrency handling


Concurrency handling

Author
Message
S_Kumar_S
S_Kumar_S
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 1062
Hi All
I have a table with identity column as PK.It is accessed by millions of users at a given time. Now there are 2 columns for which if there is already a value, then same combination should not be entered. So in the insert proc, i am using NOT EXISTS. But the problem I foresee is that suppose 50 users simultanously tries to enter the same combinations, what will happen? There is a unique index on these 2 columns. So I think 1 row will be inserted and error will be thrown for remaining 49 users. What can be the best way to handle this error? How about try catch?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14873 Visits: 14396
S_Kumar_S (12/26/2012)
Hi All
I have a table with identity column as PK.It is accessed by millions of users at a given time. Now there are 2 columns for which if there is already a value, then same combination should not be entered. So in the insert proc, i am using NOT EXISTS. But the problem I foresee is that suppose 50 users simultanously tries to enter the same combinations, what will happen? There is a unique index on these 2 columns. So I think 1 row will be inserted and error will be thrown for remaining 49 users. What can be the best way to handle this error? How about try catch?

The storage engine will ensure that only one session can insert a particular value-pair into the unique index. You could just attempt the insert and catch the error using a TRY/CATCH structure in T-SQL if you get a key violation error, i.e. do away with the IF EXISTS. Or, if you really need to know ahead of time if the value-pair is in use and if so do something else you can keep the IF EXISTS and INSERT but issue them inside an explicit transaction and add a HOLDLOCK hint to the SELECT statement that checks for existence. This will make sure that if no row exists when you check that no row will exist when you attempt the insert...just be very careful to commit or rollback the explicit transaction as quickly as possible, i.e. you only want to keep the lock opened by the SELECT with HOLDLOCK open for as short of a time as possible.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search