• @sgtwilko

    I've implemented updlock as you suggest in your comment and I encountered duplicates that occur very rarely, and not every time when I run tests with many concurrent clients. I can not explain duplicates and believed it was a sure way to protect the concurrent reads of i_nextUID. But I gave up and tested sp_getapplock, which seems to work flawlessly. There will also be better concurrency if you have a WHERE clause.

    SELECT @i_uid = i_nextUID FROM t_table

    WHERE rowID=@rowID

    Updlock escalates to table lock in my tests and only one client may update t_table at a time and everything is going relatively slowly. Instead I put @rowID as suffixes in the @Resource variable of sp_getapplock and simulate rowlock that does not escalate.