Solomon Rutzky (2/26/2011)
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_". 😉
Thanks for your feedback Solomon.
Even with the primary key in place, if two user sessions would attempt to increment the same key as part of their own transactions, one session would end up blocking the other.
Ideally, incrementing the KVP value should be done outside transactions, but applications developers seldom adhered to this principle. As I've said in previous posts, the main goals for me were to eliminate this blocking, and also make the incrementing process independent of transactions.
By making the single horizontal record for a given key vertical, transactions could increment the KVP and obtain appropriate values without blocking each other.
Ajit Ananthram
Blog - http://ajitananthram.wordpress.com