Does my approach create race condition for serial no generation

Question

User-466819921 posted

create procedure test

as

declare @lastval varchar(10)
    set @lastval = right('000000000' + convert(varchar(10),(select IsNull(max(Serialno),0)+1 from MyTable)),10)
    return @lastval
end

if i generate serial no following above approach then does it create race condition ?

because from multiple session the above SP will be access may at same time and try to generate serial no which will be inserted in table as PK value. so is there any chance to generate same serial no for two session....if yes then how to avoid it just guide me. thanks

Answer 1

User475983607 posted

Yes, you must worry about concurrency.

I've already explained how to do this without worrying about possible conflicts using a standard Identity column in SQL  Why are you making such a simple process so complicated?