Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concurrency handling Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 7:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:24 AM
Points: 380, Visits: 893
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
Post #1400245
Posted Wednesday, December 26, 2012 11:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1400315
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse