I don’t see how a table variable would make any difference assuming the whole thing is wrapped in a transaction. Shared locks will still be acquired and held as part of the select.
I really think the update lock is the most appropriate fix here. I think it is wrong to use application server locks to fix problems with your DBMS locks. Not to mention a CF lock will only work if you only have one web server. If your app is spread across multiple app servers, the only place you can correctly enforce ACID is at the DB level.
Since the update lock will block all other processes wanting to update that table, it won’t necessarily perform faster, but I think you’re trying to bypass a necessary evil. You can’t both maintain consistency/isolation while avoiding dirty-reads AND not deal with process A blocking process B on some occasions. If the update can happen correctly regardless of what the select returns, then select with nolock or lower your isolation level (or avoid the transaction entirely for that matter), but I suspect you really do want process B to have to wait until process A is finished before it starts reading from the table.
I am just thinking that if you did something like this, you could do away with the lock
declare @table table (....)
insert into @table
now do data manipulation on table
from table inner join (select * from @table) on id=id
this flow prevents the need for the lock as sql treats each @table as a scoped thread so it can keep changing and you don’t have to do the lock. Should solve the issue with having to lock. I am pretty good at Sql, if you want to shoot me a query, I can try to make a sample you can test.
Problem with this is though Steve, is the @table will not hold the correct values for the next request.
See a previous post on what this does.
but the table data is something like this
id 1, value Test 1, 1
id 2, value Test 2, 2
id 3, value Test 3, 3
id 4, value Test 4, 4
After it retrieved, it is then modified to look like this with an update.
id 1, value Test 1, 4
id 2, value Test 2, 1
id 3, value Test 3, 2
id 4, value Test 4, 3
Where the last value in the list is the weight, this is updated on every request. And the data is rotated down and the last in the weighted list is the reset to 1. Does this make sense?