That seems great.

I tried the same but with default read committed isolation and ended up with duplicates.

I am quite unable to understand why I am running into duplicates even though I am doing everything in an atomic transaction:

Code I already tried is:

Begin Tran

UPDATE table_num_scheme SET @nextvalue = next_value = (next_value + 1) WHERE name = 'Ship ID'

Commit tran

Begin Tran

UPDATE table_num_scheme with(rowlock, updlock) SET @nextvalue = next_value = (next_value + 1) WHERE name = 'Ship ID'

Commit tran

I haven't tried with serialization though.