December 26, 2012 at 7:35 am
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?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
December 26, 2012 at 11:31 am
S_Kumar_S (12/26/2012)
Hi AllI 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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply