Unanswered: READ COMMITTED vs. READ UNCOMMITTED - educate me
At my last job we ended up prefixing just about every single stored procedure with:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
to prevent locking/blocking chains, improve performance.
Our reasoning loosely translated was that:
A) we were not a bank and dirty reads should be ok
B) data can change 1 second after we complete reading the values so the locking/blocking and poor performance were not worth the trade off
Q1) Do you see any problems with this logic?
Q2) I assume setting isolation level to r.u. is fine even if there is a transaction block in the proc, correct?
Q3) Is this a common practice for the sql in your databases? Any specific examples you can give for using other isolation levels?
I am just trying to cover all of my bases when I get questioned about implementing this system wide change since mgmt. often asks for a "risk analysis".
It's not a common practice as far as I know. You may want to do a few benchmark tests to see what sort of performance gain you get. For a lot of systems that are predictable, and use proper indexes, the benefit should be small. If you have things that end up taking thousands and thousands of locks per query, then you may see a bigger benefit. You already know the downside, so as long as you are willing to get the occasional call about a report that shows the "wrong" amount now and again, you should be OK.
I am afraid I can not speak to the interaction of a transaction and read uncommitted, but that should be an easy enough test to pull off. The WAITFOR DELAY command will come in handy for that.