Primary Key Exception

Posted by Niranjan_13 under Sql Server on 6/10/2015 | Points: 10 | Views : 272 | Status : [Member] | Replies : 2
My table is Beneficiary(benid ,Name) in which benid is primary key.

suppose in our table records are
benid Name
1 mohan
2 shyam
3 radhe

if n number of user insert the record simultaneously. with retrieve max (benid )+1
than benid 4 will for all users
so only one user can insert
than there is error benid already exists for users.
how we resolved it.

Niranjan Pandey


Responses

Posted by: Bandi on: 6/12/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
1.Define IDENTITY property on benid column..

2.If you are using SQL Server 2012 , define sequence object and then pass NEXTVAL of sequence as value to benid


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Niranjan_13, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 6/12/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If you don't want to alter/modify table structure, you'll have to set SERIALIZABLE isolation level (SET ISOLATION LEVEL SERIALIZABLE) before BEGIN TRANSACTION.

SET ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- Do youe insert logic here
COMMIT TRANSACTION


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Niranjan_13, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response