Are there plans to move optionally to full manual connection control and is it possible to do so already with a hack in 5.0?
I Regularly do applications that are high performance / high concurrent. The logic is not necessarily complex, but it is tedious to run into full "retry" mode. We talk of items such as queue control for a HPC system etc. - it is important that when
asking for a "not assigned" job, no other query gets the same data. The logic normally looks "read, update, write". I don't want to move that to SP's "just because".
For a scenario like this, full connection control (also in regards to isolation levels) plus possibly even lock / lock ignore hints are critical. For example, I have a web portal that also shows the queue. I really don't need "committed only" there
- I can totally live with (a) reading uncommitted and (b) even skipping rocked rows. This is informational info anyway only.
What I can not have though is having 100 agents pull the same row and "fighting out who gets it" (and then retry), and that is where we currently are going to.
Any work being done? I can see an ConnectionScope being done at v4 times by a third party (http://www.codeproject.com/Articles/71030/Connection-Scope-for-the-Entity-Framework
and that concept looked quite well... Sadly, I am unable to keep a connection open in v5 at all.
TransactionScope is NOT a solution - that forces a DTC involvement the moment the 2nd operation happens, and that has a COST.