Solomon Rutzky (2/26/2011)
...You don't need to worry about the SELECT of the new value getting something updated from another session if you use the OUTPUT clause in the UPDATE as follows:
UPDATE tbl_kvp
SET column_value += 1
OUTPUT inserted.column_value
WHERE column_key = @key
This avoids the need for additional locking as "tech.dbmeyer" had suggested.
Yes, but the problem is that the key-allocation procedure might be called within a transaction. The exclusive row lock needed by the statement above will be held to the end of that transaction. An application that starts a transaction, allocates a key from this procedure, and then sits around waiting for something, will block anyone else needing the next key from the same sequence.
I wrote about this issue at length (including robust ways to pre-allocate a range of keys without blocking) in:
http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi