Avatar of socom1985
socom1985 asked on

c# MSSQL next ID

I have a web service which enters data in an SQL table. Website generates a request for the web service and gets an ID back immediately. Then the web service performs some actions and at last it enters the data with this id in the table. So I have to get the next possible id in the table, retur it to the website, perform some actions then insert the record with this id in the table.

Can you please tell me how I can get sure, that this ID will not be used by another thread? It seams insecure just to get the ID and then insert the record, because the actions which the service performs in between take quite some time. Can I look the table for this time? Will this influence the performance?
C#Microsoft SQL Server

Avatar of undefined
Last Comment
Meir Rivkin

8/22/2022 - Mon
Meir Rivkin

rather locking the table you can Mutex as synchronization mechanism to ensure that only one thread at a time calls the method which returns next available ID.releasing the Mutex should take place only after the ID was used when inserting the record.then when the next thread asks for next available ID, the previous ID won't be returned cause it was always inserted with the record by the last thread.
Meir Rivkin

from http://msdn.microsoft.com/en-us/library/ms173179%28VS.80%29.aspx:Although a mutex can be used for intra-process thread synchronization, using Monitor is generally preferred, because monitors were designed specifically for the .NET Framework and therefore make better use of resources. In contrast, the Mutex class is a wrapper to a Win32 construct. While it is more powerful than a monitor, a mutex requires interop transitions that are more computationally expensive than those required by the Monitor class.
ASKER CERTIFIED SOLUTION
Meir Rivkin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck