I have in recent months been trying to improve the performance of a Navision style posting routine using normal SQL techniques. That is by reducing the lock footprint of various tasks so that it's possible to run more of them at the same time. I've had quite a bit of success.
Some things are easy, some things are impossible. For example, number series. By definition these are actually impossible to multi-thread as the number series "allocate next number" is a serialisation point. So the only solution is to open a distinct connection to the database so that the 'next' number can be allocated to the user to use when they're ready. This can be done in a tiny transaction with a minor cleanup if there's a rollback. (BTW: There is an outstanding SQL feature request to be able to do this in a single connection to the database)
But what want to ask about is something that should be simple, updating things in the database with the minimum of locks. Navision uses the READUNCOMMITTED table hint, so at first glance this looks very easy, just tell Navision "UpdateNoLocks". Except, it switches to SERIALIZABLE as soon as you do a single write to the table and there's NO CONTROL it even overrides FINDSET.
So my question is simple, is there an outstanding request with Microsoft that I can attach to (vote for, whatever) for a UpdateNoLocksAndIReallyMeanNoLocks Navision transaction type. Or UNLOCKTABLE command or LOCKTABLE(FALSE) or whatever is the best solution to this.
Of course I could be wrong, is there a way to do only 'Manual locking' that I've missed?