Assuming SQL 2005+

It is theoretically possible that you could into trouble if you use the READ UNCOMMITTED transaction isolation level. You could potentially get dirty reads in the SELECT. That isn't a fault in the implementation of the locks, but rather that you have explicitly set options that do not honour locks properly.

If you are interested in having a unique numeric ID on a record, I would suggest that defining the column with IDENTITY and then using either an OUTPUT on your INSERT statement or SCOPE_IDENTITY to see the values.

As my original article outlined (at least I hope it did!) this technique is more about ensuring that task A and task B are aware of each other and don't interfere with each other. I'm sure there are examples of it in most businesses.