i am creating a procedure which will generate a unique id of a visitor, and will be insterted in a table.
Table : details
Field : Uid
create procedure p1
declare @id varchar(100)
declare @tot int
select @tot=count(*) from details
set @id='P0'+cast((@tot+1) as varchar(100))
set @id='P'+cast((@tot+1) as varchar(100))
insert into details values (@id)
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.