I would be very interested to have a little more information such as, SQL server version and SP, number of concurrent connections (also how many uids would be requested simultaneously) and how clients connect and execute the code (e.g., Clasic asp running a Stored proc via ADODB.command object, .net running via adhoc record set, etc).
If you still have it, I would also be interested to see the code that failed (PM if you don't want/can not publish it)
Please don't misunderstand my intentions; I'm not saying you've implemented it badly, nor am I contradicting your statement, but I am concerned for the applications that we have using this code as we've been using two versions of this code for many years (one version very similar to the example that generates the next unique number for the entire DB and the other that generates the next unique number for a user). I would like to know how it can fail so that we can adapt.
It may just be a matter of scale, or perhaps in my testing I never managed to get an escalation to table lock. I will have to go back and rebuild the testing code and see if I can locate the point of failure.