the main problem that can occur is maybe two or more users can request the procedure from my front end application at exact same time, so it may be a possibility that both of them can get the same uid, i could have used primarykey to stop same data to insert but that will throw an exception.

Is there some kind of lock which can be implemented in my stored procedure so that only a single user is given right to access the procedure and rest of requests are kept in a queue.