Help with SQL Server 2005 Isolation Hint Info
-
Rumor has it that you will no longer be able to use just "(NOLOCK)" as an isolation hint in queries in SQL Server 2005...
that "WITH (NOLOCK)" must be used (the WITH will be required). Is there any truth in that rumor? Have any of you had personal experience in that are on SQL Server 2005? Do any of you know of a Microsoft web site that will confirm or deny that rumor?It's important to me because the company I work for may make the "leap of faith" as soon as it comes out. We have hundreds of sprocs that use (NOLOCK) without the "WITH". It would be handy if we knew the truth about this rumor about isolation hints now so we know whether we should start changing everything. And, Yes, you're right... they should have done it the right way to begin with but they didn't.
Thanks for the help. Looking forward to any advice on this nasty rumor especially from people who have first hand experience on 2005 with (NOLOCK).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I have also heard that, or at least something similar. However in September CTP it is possible to specify NOLOCK without WITH, although you must use parentheses. Like so:
SELECT foo, bar FROM foobar (NOLOCK)BOL says (on the topic Table Hints) this:
In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.
And also, not to go off topic here, but why are you using NOLOCK in hundreds of stored procedures?
-
Thanks for the reply, Chris. Your experience seems to contradict what BOL says. Now, THAT's never happened before, eh?
Kinda why I asked if anyone had experience with it in 2005. Why am I using NOLOCK in hundreds of stored procedures? Because of dozens and dozens of articles on the web like the following (and it does seem to work, they should make it a default and make reading committed data the option)...
http://qa.sqlservercentral.com/columnists/rmarda/performanceaddinghints.asp
...and the didn't want to use SET TRANSACTION ISOLATION LEVEL because it carries through on procedures sometimes causing a lock conflict.
Thanks again, Chris...
Has anyone else had real life experience with the rumor (apparently document in 2005 BOL) that (NOLOCK) will also require the word WITH when 2005 hit's the street? Thanks folks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
BOL does refer to "some exceptions", so it is not quite contradictory. I had not heard specifically that NOLOCK would need to be specified using WITH, just that table hints "in general" (though he did not say that) would. So I guess this might be the exception.
Wow, interesting to recommend using dirty reads. I had not seen that article before. I can only say that I would not do that.
-
>I can only say that I would not do that.
Why not, Chris?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Because allowing dirty reads means the data integrity might be compromised. What if someone makes a decision based on incorrect data? Databases are used not for performance, but for protecting the integrity of the data they store.
-
If you are using sql sever 2005 then you should consider eliminating the read with (nolock) and start using Read committed snapshot.
Read committed snapshot will give you transactional consistency (no dirty reads) and will not be blocked by rows being updated.
This article may be helpful:
http://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx
-ron
-
Chris,
Yup... I agree on the data integrity thing but considering that a ROLLBACK is supposed to be (and, fortunately, is) an extremely rare occurance (and then usually only on the GUI side, is much more rare on the batch side), I'll take the performance and reduction of deadlocks (on very poorly written code they won't fund a correct fix for) as a pretty good trade. I do appreciate you posting that caution though... lot's of other folks just aren't aware of that particular tradeoff and, therefore, don't write new code to correctly handle rollbacks and the possible data integrity issues that may arise from the dirty reads.
Ron, thanks... I was just skimming some info about that and I'm jealous of anyone who's had the opportunity to work with and study 2005 at any level.
Considering the company I work for just upgraded from
SQL Standard Edition to the Enterprise Edition for their world-wide enterprise, I'd say I stand a snowball's chance at the hub of hates trying to get them to update to 2005 anytime soon. 
Thank you both for your replies... all information is good information.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff, yes that is exactly what I meant with being surprised to find an author more or less recommending people to use NOLOCK in all reads. I hope to cover snapshot isolation (and row-versioning in general) in future articles, and am sure others will as well, so look for that.
-
Chris,
Likewise it's interesting to think that databases are not used for performance... whatever. The real key is that the inclusion of the NOLOCKs greatly reduced the number of deadlocks in otherwise poor code... that's an inexpensive improvement in the data integrity you were talking about.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
What I meant was not that you are not looking to get the best performance from a database. Naturally we choose the product and techniques that gives us the best performance, but it is not the first priority and definitely not why we use relational databases. If performance where the #1 prio then why use a database at all, why not just use a file? Why use locking? I know it is not this black-and-white, but it is a way to view the priorities. For the same reason I am not using MySQL, although I should note that I have not tried it for a long time. I am sure they have added some of the must-have integrity features by now, but a data management system built for performance, not data integrity, from the start does not feel right for me.
Deadlocks are like you say a problem of poor code, not the DBMS. In fact they are an essential key to protecting data integrity. And I still say that dirty reads are in general bad and unwanted. All hints should be used with care and in special situations, recommending general usage of any of them is just plain wrong.
-
I also have several servers that are used almost entirely for large single-user batch jobs, and we use a lot of locking hints. Other servers (and other databases on the same servers) have transactional activity and rarely see a locking hint. I certainly wouldn't recommend making WITH(NOLOCK) the default, but I find it very useful. It is not merely a compensation for bad code.
Our QC guy has to run a lot of large SELECT queries against tables that might be in the process of being updated by someone else. We know there is no conflict because the two jobs are using different JobIDs (leading part of primary key), but SQL Server doesn't know this and has to create a lot of locks to deal with it. So we have a choice, either the QC guy uses queries WITH(NOLOCK) and gets his work done, or he only gets to run two or three queries a day. We know there are no dirty reads because his job's 15 million rows are static while somebody else's 15 million rows are not.
WITH(NOLOCK) is also perfectly safe for lookup (or reference, or whatever you want call them) tables. We know these tables will only be updated in maintenance windows when no batch processes are running.
Sometimes we do large updates of all our core tables, and the scripts are full of WITH(NOLOCK) and WITH(TABLOCKX). I've even been known to put ALTER DATABASE ... SET SINGLE_USER in the scripts for good measure. It would be pointless for anyone to even think of being in the database at that time, so why not?
It would be a great idea to move the large batch processes to MySQL, the flat file engine would be great. The reasons it hasn't happened run along the lines of 1) no in-house MySQL experience, and 2) politics (either "If it's free it's no good" or "If it's free why are we paying for all these SQL Server licenses?"). I whine and plead for a spare box to set up SQL 2005, the additional spare box (and time) to set up MySQL is not on my horizon.
Just because we're bypassing transactional support doesn't mean we're not using foreign keys, triggers, and other database features. We're trying to get the most out of our servers, but I don't feel we're ignoring data integrity. Granted you don't use WITH(NOLOCK) because you thing there PROBABLY won't be any transaction rollbacks that hurt you, you use it when you KNOW the data and the processess will allow it. For us it isn't a special situation, it's every day.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply