Ajit, this is an interesting approach to the problem, but most likely it is just adding complication via over-engineering when a much simpler solution appears to be available.

1) The most important and easiest thing to do is, as "dvdwouwe 72544" mentioned, create a PK on the "key" column. There is really no reason to have this table without a PK defined. This will also ensure that a duplicate "key" doesn't accidentally get added to the table.

2) 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.

3) You can always tell the system to only lock a single row (although given the singular nature of the UPDATE statement I am not sure why it would ever do anything else, but still) by using the WITH (ROWLOCK) hint in the UPDATE as "SanDroid" mentioned.

Minor note: I find it interesting / amusing that whoever created this table (a developer I think you said) prefixed the column names with "column_". 😉