I have a table "Mytable" containing a list of IDs.
There is a web application and the user logging into the web application is assigned an ID from the table mentioned above. So if 2 users are logging in concurrently, an ID from this table is given to each of the user. The ID and the UserID is then inserted into another table. However care should be taken that the same ID is not assigned to more than one user
I have used the statement "select ID from mytable with (updlock, holdlock)". This ensures that the ID assigned to the user is unique and that the same ID is not assigned to more than one user.
However this table has records in millions and since there is locks used, the select statement becomes too slow resulting in slowness of the application.
Is there a better way to handle this and avoid using the locking hints as mentioned above?