Handling concurrency issues while inserting a record in to the sqlserver DB

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,
venkataramanaiahsrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
There are different options, my basic question is why don't you use an identity column for
patientno
venkataramanaiahsrAuthor Commented:
The patient no is ten digit no which gets reset every calender year. for eg for year 2011 , it starts from 11-0000001 to ...... . for 2012 it starts from 12-0000001 to ...... .

before inserting the next number i have incorporate the logic to have the number converted to this format.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

lcohanDatabase AnalystCommented:
Just be extremely cautios with explicit transactions that can introduce other issues as locking/blocking and the worst of all - deadlocks. Keep the explicit transaciotn (betwwen begin and commit) to the minimum or...use try/catch instead:

http://msdn.microsoft.com/en-us/library/ms175976(v=sql.105).aspx
venkataramanaiahsrAuthor Commented:
i would like your  advide on another point.  Which is better of the  following two options

1. using connection object begintran, committrans and rollback trans and just using simple insert statment in storedprocedure and using this proc in connection/recordset objects execute trans  beween its begin and committrans.

2. using explicit trans in stored procedure  and just executing the sp inside the code using connection object or recordset object.
BuggyCoderCommented:
here us a result of discussion:-

If you need to update one record or need to execute single SQL statement, you do not need to open transaction at all, since single operations are always atomic. To open transaction from the code or in stored procedure will have same result. Everything depends on what you need to achieve from your code. If you need to call multiple stored procedures from your code inside of single transaction then you would open transaction from inside of your code, so you maintain and control it from central point. If the whole transaction "fits" into functionality of single stored procedure then you could use both ways

at
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/dfaba053-132d-4bcc-9828-45697b0a9319/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.