I think your solution is really good. One more time DBA has to solve issues caused by developers that do not know that database calls are expensive and locking is not so difficult to be implemented on application side. It would be better if the developer creates a call to the database and gets 100 keys at one time that he stores in memory in his application and uses the keys from there. Now instead of 100 calls to the database for each record he makes only one for each 100 records.
I am afraid this approach is even worse. What if for each of those keys the developer has to insert some records in a related table, then use those keys to insert related records in a third table? and what if those keys are actually autoincrement values and can't be obtained beforehand? and what if the values have to be generated taking in account the other possible users working in the same time? (such as secvential invoice numbers?)
Locking must be implemented on database side, period.
Microsoft MVP 2006-2010
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_".
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Ironically a new version of SQL Server would have an Oracle-style SEQUENCE identity capability, so hopefully there will not be need to "re-invent the wheel"
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply