Handling concurrency issues while inserting a record in to the sqlserver DB
Posted on 2012-03-28
My db is sql2008r2
I have an issue that the same stored procedure is being invoked at exactly the same time with exactly the same paramenters.
The purpose of the stored procedure is fetch a last patientno and increment it by one and insert the new record
The problem is that if user A is initiated the insert operation and fetched the last patientno (say 001) and incremented it by one and by the time user A completes the insert and the new record is inserted (id 002), another user has completed the insert operation. now if the user A succeds in insert oprations with its incremented value of id , it will create duplicate record .
How to avoid this scenario
Any help would be appreciated,