SQLServerCentral / Discuss Content Posted by Wayne Fillis / Article Discussions / Article Discussions by Author / The Effect of NOLOCK on Performance / Latest Posts

RE: The Effect of NOLOCK on Performance

Brian Munier — Wed, 17 Jun 2009 07:00:18 GMT

Without reading all of the past comments, I am going to post what I believe is a valid reason to use NOLOCK.We have multiple database servers, support one database per customer, approximately 15 customers per SQL Server (2000, 2005, enterprise edition on clustered servers).We have data that is customer independant, and environment independant, for example system tables with lists of valid file types. Fortunately none of our columns are text, ntext, varbinary, etc... as pointed out earlier as being an issue. (Excellent point however) Basically we have int, varchar, nvarchar, and datetimes. We use views in the customer databases to look at this data. The reason I feel safe using it ONLY for this customer independant, evnvironment data views is that the data is very static, not very large, and basically used for data integrity.The reason we implemented the with NOLOCK hint, was that we had hundreds of thousands of connections and locks in CommonData on our production systems which presented a significant overhead. We attempted making the institution independant data database read-only, but the delays in changing it to/from read only in order to perform maintenance were very significant and could shut down our production customers. In a general on-line transaction processing database, I would not recommend the NOLOCK hint, but getting static data from a common database, seems safe to me. Just be sure to keep your common database defragmented to prevent the duplicates.Brian Munier Brian

RE: The Effect of NOLOCK on Performance

Grant Fritchey — Thu, 23 Apr 2009 05:14:18 GMT

If the data is being pulled from a covering non-clustered index, absolutely. I'm not so sure it would happen during a key lookup.

RE: The Effect of NOLOCK on Performance

rubes — Wed, 22 Apr 2009 14:59:16 GMT

I am a big fan of nolock and have used it very successfully (and prudently) over the years. I must admit that this whole thread has made me reconsider how I use it. There is one thing that has not been clarified. In the case of duplicate rows (which causes me the most concern and is something that we have seen a few times in our environment), does this behavior only occur when 1) a clustered index is updated and 2) a non-sequential clustered index is inserted against? I noticed that Tony Rogerson's example of this involved updating a clustered index, causing the row(s) to physically move because of a page split. Could this behavior occur with non-clustered index modifications as well?

RE: The Effect of NOLOCK on Performance

Sorin Petcu — Mon, 17 Dec 2007 09:49:08 GMT

I knew there are dirty reads and assumed the responsability. But from my ERP system point of view, there is no constraint to view dirty data.

RE: The Effect of NOLOCK on Performance

timothyawiseman — Mon, 17 Dec 2007 09:20:23 GMT

This is an excellent article with the author's notes, though the line warning about only using it where a dirty read is acceptable cannot be overemphasized. I have a couple of particularly heavily queried tables that I have a procedure automatically generate reports on. Due to their heavy use, there are often more locks on. A couple of weeks ago, I added a no lock clause and saw the average running time drop from over an hour to under twenty minutes. The key though was that the report was returning summarized information and the management really needed an estimate more than an exact number. I could accept the risk of dirty reads and even duplicate row returns. Nolock should be used with greatest caution when the integrity of the results returned is of importance.

RE: The Effect of NOLOCK on Performance

Sorin Petcu — Mon, 17 Dec 2007 01:55:00 GMT

Hi,Using the LOCK hint to SELECT query it is a very good issue. I have tested it in my production environment and it is very useful. Before that it was a lot of lock waits and deadlocks. There is an ERP application which is running here and the isolation level of transactions was set in the code of this application. So, I have used this hint. I want to point the author of this article to make tests of this hint on concurrential systems. There is a huge difference between using and not using LOCK hint.

RE: The Effect of NOLOCK on Performance

Hugo Shebbeare — Mon, 05 Nov 2007 11:24:25 GMT

Hello, just wanted to add my two cents, now that the Canadian is worth more and has been for longer than most have expected :)Please don't use nolock on tables that have text/ntext/image data types, it causes excessive cpu useage according to Idera's tools, plus gives 7105 errors frequently in sql server logs, which cannot be good for any instance in production.Huggy Bear, Huggy ji, Pugo, Huge...token mcdba since 2001.

RE: The Effect of NOLOCK on Performance

Wayne-153714 — Wed, 28 Mar 2007 14:12:00 GMT

Good one, Jeff - you got me there! While loop indeed. Tsk, Tsk :-).CROSS JOIN would have perhaps been much better, or perhaps you can recommend something better?And the bit about a date field varchar - eh, you got me there - I missed that one! Remember this was just a quick and messy script set up to create test data, but I'll try to be more careful next time. :-)Cheers,Wayne

RE: The Effect of NOLOCK on Performance

Jeff Moden — Sat, 24 Mar 2007 10:45:00 GMT

I think we've pretty much beat the subject of when and when not to use NOLOCK to death and the author pretty much summed up all that in his new introduction to the article. I give him credit for having the hair to come back with that summary after everyone got through with the previous article on the same subject.

At this point, I'd be more concerned with asking why someone is storing a date as VARCHAR or using a WHILE loop to create simple test data!!!!

RE: The Effect of NOLOCK on Performance

noeld — Fri, 23 Mar 2007 12:29:00 GMT

If you SET the DB to read-only all locking mechanisms will be bypassed!

RE: The Effect of NOLOCK on Performance

Steve Jones - SSC Editor — Fri, 23 Mar 2007 11:26:00 GMT

I think there is good reason to warn people of dangers, but I've always been a practical DBA, using whatever is in my toolbox and works. If inconsistent results are a problem, then certainly re-examine your use of NOLOCK (or any other tool).I hate that so often we don't get details or good information on the various tools available to us, like undocumented tools. I think we'd be better off if everything was very well documented, especially the dangers, and let people use it when it works in their environment.

RE: The Effect of NOLOCK on Performance

Alexander Kuznetsov — Fri, 23 Mar 2007 11:05:00 GMT

Be very careful: even with READ COMMITTED isolation level you can get inconsistent results in your reports:

http://www.devx.com/dbzone/Article/32957/0/page/2

RE: The Effect of NOLOCK on Performance

SuperDBA-207096 — Fri, 23 Mar 2007 06:36:00 GMT

I wonder if maybe it could be a good idea on a read-only database. Many situations I've worked with in the past had a 'readonly' db used as a datamart for reporting.

There would be little contention for records on that database, and little chance of a record being changed unless the read-only db was being repopulated... Something to think about anyway.

Mark

RE: The Effect of NOLOCK on Performance

colin.Leversuch-Roberts — Fri, 23 Mar 2007 04:48:00 GMT

I maintain that presenting the use of nolock as a tuning tool is both dangerous and missleading.

That there is a use for read uncommitted I will agree in certain circumstances, and lets use isolation levels here to make sure everyone understands exactly what we're talking about, although I'd always prefer to lean towards read only databases or filegroups. There's always going to be some gain if you tell sql server not to issue shared locks but compared to normal tuning and optimisation I think the risk outweighs any possible gains.

I also take exception to the phrase "Using hints in a query is something that most DBAs don't ever seem to bother with" - there's a REALLY REALLY good reason we generally don't - telling the optimiser that you know better is not recomended. I'd also be more interested if the tests had been run on a server rather than a laptop, I also note that your plans show parallelism, I suggest you try running your tests using profiler, IO stats often do not show additional io generated by parallel plans .. as I remarked first time around I tried a couple of simple tests on a server and could not see any difference.

I'd also question testing against table scans, this is something your average DBA tries to avoid.

RE: The Effect of NOLOCK on Performance

Richard Williams-416852 — Fri, 23 Mar 2007 04:04:00 GMT

By "up to the second" I just meant that the reports are almost always based on data just saved - i.e. the transaction is complete before the report is run so uncommitted data is irrelevant to these outputs.

I began the arduous task of applying the WITH (READUNCOMMITTED) hint to all the views used by reports in the system (all 2500+ of them) and started seeing immediate performance gains. Overall the servers cache seems to be coping better and the CPU and IO have dropped considerably. The system also has an OLE component that talks to and populates word documents which we took another look at, changed the connection it uses to read uncommitted and explicitly made the recordsets read-only and it's now running five times faster and taking up a half of the server resources it used to. We also reviewed a lot of the indexes and found a few missing based on the current usage. The application as a whole uses transactions sparingly but we also discovered a few that had been misplaced around batches of work and changed their granularity - it's been a hell of a week.

Overall things are back to being operational performant but it's still driving me crazy that I can't tell Crippled Reports to configure it's connections as NOLOCK or READUNCOMMITTED - how hard can it be

RE: The Effect of NOLOCK on Performance

Malli-336657 — Fri, 23 Mar 2007 00:50:00 GMT

Iam working on 8 terabytes of data, where i found the vital role of NOLOCK.Definetly it improves the performance

RE: The Effect of NOLOCK on Performance

xyvyx — Sun, 11 Mar 2007 15:14:00 GMT

no idea if the situation would be similar, but...I recently ran into some problems with an app that used parameterized queries generated by some OO middleware. This middleware was pretty flexible, but it was creating the parameters as nvarchars while all the fields we use were just varchars. SQL appeared to be creating/holding a bunch of locks while it converted every value in the table or index to unicode, THEN do the comparison. So basically it was having to scan through & process every row vs. doing a simple index seek.I wonder if you could feed some of those dropdowns via. separate views or indexed views.

RE: The Effect of NOLOCK on Performance

Jeff Moden — Sun, 11 Mar 2007 12:55:00 GMT

Ok... confusion has set in, Richard... in your latest post, you say "A large number of the reports do have to be bang up to date" and in a previous post, you say "There are a large number of reasons why we don't care if the data in these reports is correct to the second"

Which is it, really?

RE: The Effect of NOLOCK on Performance

Richard Williams-416852 — Sun, 11 Mar 2007 08:54:00 GMT

About read-only report database - already underway - as you say very obvious - just thought there might be a quick interim fix. A large number of the reports do have to be bang up to date (about 40%) and due to the nature of the business they do not restict themselves to three table joins with two records - plus the client insists on using CR parameters which seem to have the effect of doing the report query at least one for each dropdown etc

It looks like somewhere under the hood of CR it should be possible buy version 10 is the nearest I can find where it is possible.

I'll let you know when we find a definitive way around this one.

RE: The Effect of NOLOCK on Performance

colin.Leversuch-Roberts — Sun, 11 Mar 2007 07:29:00 GMT

Richard - sounds like you have parallelism issues, something discussed many times on this site. The most obvious answer to reporting issues is to report from a read only database - turns all locking off and solves most problems. A log shipped database can provide this function unless you really, really , really must have up to the last second data - then a combination of read only and selective replication may be the answer.

When I read so many of the posts about reports causing blocking I just wonder why it doesn't seem obvious to deploy to a reporting server ?

As to the snapshot isolation level - it was introduced to enable easy migration from Oracle, at most of the events I went to during Beta 2005 it was stated that this was the main reason and as it wasn't really ansi you should stick to sql isolation levels - you also need to be aware of the overhead of running snapshot, which can be quite high - just think of how your memory pools and cache handle the snapshots for instance.

RE: The Effect of NOLOCK on Performance

DCPeterson — Fri, 09 Mar 2007 21:43:00 GMT

Jeff is correct, but doing this carries the same risks as using nolock.

If you are using SQL 2005 then Read Committed Snapshot isolation level might be an appropriate choice.

RE: The Effect of NOLOCK on Performance

Jeff Moden — Fri, 09 Mar 2007 21:26:00 GMT

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

RE: The Effect of NOLOCK on Performance

Richard Williams-416852 — Fri, 09 Mar 2007 04:33:00 GMT

DCPeterson,

I'm having a shedload of issues around locking up of the database when large reports are running on the system - they seem to be causing processes to wait for transactions and they themselves lock up when transactions are active. These reports (CR XI R2) cover the entire DB's tables - are you aware of a way of setting the isolation level of CR low enough so that it never waits or causes to wait for anything. There are a large number of reasons why we don't care if the data in these reports is correct to the second.

Thanks

Richard

RE: The Effect of NOLOCK on Performance

DCPeterson — Tue, 06 Feb 2007 12:44:00 GMT

Sorry for the delayed response, I haven't been getting my email notifications for a few days...

Anyway, that is a great link thanks.

I think you are correct in that the SQL Standard calls for pessimistic locking. However your concerns about inconsistency, I don't think are really founded. Using the Read Committed Snapshot isolation each transaction is presented with its own "picture" of the datbase as it was when the transaction was started. That "picture" lasts only as long as the transaction does. So it is possible for transaction A to undo the changes made by transaction B etc... However, this same situation exists in the Read Committed model, except that transaction A will be put into a waiting state until B finishes.

If you need the more isolated levels such as Repeatable Read or Serializable, then for any transactions that use them, the snapshots are not available.

I have never heard that MS recommended against deploying Snapshot isolation. That would be interesting and puzzling too, do you have any references? There is no reason that I can think of to not use Snapshot isolation where it is appropriate. But like always, you should always understand where the rocks and rapids are before jumping into the river...

RE: The Effect of NOLOCK on Performance

colin.Leversuch-Roberts — Fri, 02 Feb 2007 04:39:00 GMT

The SQLCAT team have also joined in the debate upon the use of NOLOCK

http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

Makes interesting reading and adds some more information.

My view of the "Oracle" locking is that it is not ansi ( I understand ) and can give different users different views of the data, e.g. an inconsistant database view. It was explained that the new locking modes in SQL2005 were to enable the transation from Oracle to SQL Server not encourage the deployment of these modes. But still, you take your pick and live with it I guess!

RE: The Effect of NOLOCK on Performance

DCPeterson — Tue, 30 Jan 2007 16:02:00 GMT

Ummm... did you read the discussion threads? There has been quite a bit of discussion of the Read Committed Snapshot (RCS) isolation level that is new with 2005. This new isolation level allows SQL Server to act in much the same way Oracle does.

Now that said, Oracle's locking model, despite the marketing hype, is not the "best" and is not THE reason that "Oracle does the really big 'uns." If you don't believe that there is significant overhead involved in providing that kind of transaction isolation mechanism, think again. However, it does allow for greater concurrency in many cases. Historically both IBM and MS have adopted the more pessimistic locking model, while Oracle's is more optimistic.

"Hence, unlike SS2K, a long running transaction (one that is not implicit but has a BEGIN TRANSACTION and is yet to get to an implicit END TRANSACTION via a COMMIT) does not require extending the Transaction log file." True, but it may require extending the undo segment, either that or you get the famous stale undo segment error. There is, or at least was (I haven't worked extensively with 10g), quite a bit of administratve overhead involved in managing rollback segments. The point being there are tradeoffs involved...

Now MS gives you the choice (something which neither Oracle nor IBM provide). That has been my recommendation to those who want to use NOLOCK, investigate the RCS isolation level and see if that doesn't do what you need.

RE: The Effect of NOLOCK on Performance

Simon Sabin — Tue, 30 Jan 2007 15:42:00 GMT

Thats why SQL 2005 has 2 new isolation levels to allow for readers to read consistent committed data unblocked.

RE: The Effect of NOLOCK on Performance

John Kanagaraj — Tue, 30 Jan 2007 15:22:00 GMT

I am surprised no one mentioned Oracle's locking model. Oracle, from its very inception, prevented 'dirty reads'. Simply stated, all (and I mean all) Oracle versions were able to do that by separating "Undo" from "Redo". (Undo is the data required to undo changes to a data block or page. Redo is the data required to protect changes - your change log)

There is a major difference in the way Redo and Undo is managed in Oracle as compared to SS2K. Redo information is continually written out to the current online redo log file in a cyclic fashion, with a minimum of at least two redo log files that serve this function for the entire database as compared to the single log file required per database in SS2K. The Undo information, on the other hand is written to structures internal to the database called Undo segments (previously known as Rollback segments). The writes to these segments is again protected via redo, so instance recovery is not an issue. Hence, unlike SS2K, a long running transaction (one that is not implicit but has a BEGIN TRANSACTION and is yet to get to an implicit END TRANSACTION via a COMMIT) does not require extending the Transaction log file. The Undo segment is retained until a COMMIT is performed by the requesting section. This architecture lends itself to providing highly concurrent online system without having to consider the possibility of deadlocks or artificially forcing short transactions.

Another major difference is the way locking is done, again due to the separation of Redo and Undo data. Although both RDBMS lock at the lowest level possible (i.e. row level) and support shared and exclusive locks, Oracle reads are not blocked by writes (and vice versa). In SS2K, writes can block reads unless 'dirty reads' that allow uncommitted data to be read by another process is allowed. In Oracle based applications, the designer does not need to be catered for this problem, thus allowing Oracle to supports a higher concurrency level for online applications.

Whether we like it or not, I believe this is one major reason Oracle does the really big 'uns.

RE: The Effect of NOLOCK on Performance

Simon Sabin — Tue, 30 Jan 2007 10:37:00 GMT

I agree this is a great discussion. I never said I was against using NOLOCK rather the article does make the reader aware enough about what NOLOCK actually means to you application.

The posts here have been great in that they highlight users that are using NOLOCK but know what the implications are.

ps We use NOLOCK all over our read only DB layer that provides the search facility to the web sites. This is generally due to the amount of data searching processes (full text is really bad) and so the impact on concurrency.

RE: The Effect of NOLOCK on Performance

xyvyx — Tue, 30 Jan 2007 10:04:00 GMT

no problem Colin, I didn't give a whole lot of detail in that post. If each webpage had just a single database query behind it, that would have been nice. I believe the average was closer to 6/page, and our users certainly weren't kind enough to spread their activity evenly across a 24-hour day.

The application involving 150 million rows actually performed quite well all things considered: 2 primary databases totaling about 50GB in size sharing an instance of SQL Standard edition (probably the most significant limitation IMO) on a dual 2.4ghz xeon server. Having the extra memory support of Enterprise edition would have been ideal, but it was just too expensive. This was one reason I was anxious to give the 64bit flavor of SQL2005 Standard a shot... I was just happy I was able to spread the data, logs, indexes and tempdb across seperate physical volumes.

Partitioning the data wasn't really a good option in this case... it's not like I had "old" records that I could segment off into their own tables/filegroups. Generally, a given user had between one and several dozen groups of items, each group typically consisting of 200 to 5000 records. This large table was also joined against a smaller 4 mil. record table of static data (well, it only changed once/day). The records in this smaller table were common between many users, so there was quite a bit of overlap in the selects. This is one case where NOLOCKs makes quite a bit of sense, and there were many other similar scenarios on the website where a given table was 99.9% read-only. Of course where we could, we cached data directly in the web application itself.

Personally, I never observed a significant difference in performance on a single query with a NOLOCK, it was just the cases with many concurrent ones.

RE: The Effect of NOLOCK on Performance

DCPeterson — Tue, 30 Jan 2007 08:58:00 GMT

Agreed, this is the kind of conversation that is really great to have at the PASS Summit...

I'll also agree with the basic premise of your post, we should get the job done with the fewest possible resources required to do it properly. "As simple as possible, but not simpler." However, based on some of the responses here, and my personal experience, it is apparent that many, if not most people who discover the wonders of NOLOCK don't really have a clue about it's ramifications, beyond "Hey! This is great! It makes my queries run faster!"

I have used NOLOCK before, I'm not saying, and I don't believe anyone else is saying that NOLOCK is evil. It is a tool in my toolbox, but like any other tool, it can be misused. Herein lies the danger that I see. A little knowledge is a dangerous thing. Why? Because if you give someone the NOLOCK hammer without the proper knowledge of both the benefits and costs, pretty soon all the world looks like a nail... I've seen it many times (not just with NOLOCK) and I've been guilty of it too.

RE: The Effect of NOLOCK on Performance

Charles Kincaid — Tue, 30 Jan 2007 08:52:00 GMT

I could not agree with you more, Colin. Forums (like this one) and newsgroups have been of great benefit to me over the years. I have a few more years than most. Not only is it good for learning but for some of us old geezers it gives us a chance to give back to the industry.

RE: The Effect of NOLOCK on Performance

colin.Leversuch-Roberts — Tue, 30 Jan 2007 08:46:00 GMT

I have to say I've greatly enjoyed a number of the discussions of late .. the subject matter may be diverse but it's certainly been exercising the old brain!! This is one of the best points of SQL Central, in my opinion, that the discussions are of such high quality. It's not something I've particularly found elsewhere.

It's my best learning platform, face to face would be good too - but written gives the chance to take it in and you have to think ( hopefully ) about what you write before you post it. < grin >

RE: The Effect of NOLOCK on Performance

kevin mann — Tue, 30 Jan 2007 08:42:00 GMT

WINNER! WINNER! CHICKEN DINNER!

RE: The Effect of NOLOCK on Performance

Andy Warren — Tue, 30 Jan 2007 08:30:00 GMT

This would be such a great discussion to have in person;-)

I think locking should be considered on a statement by statement (or least proc) basis with a good understanding of the risks and benefits. Even with the BEST table design, shouldn't it be part of our mandate to use the fewest number of resources that will get the job done to the standard that is required? If locking is good, shouldnt we be all using serializable so that absolutely nothing can go wrong? Read committed really is a pretty good default setting, but I wonder if it doesn't make us lazy - we don't have to push the developer into having the conversation about the appropriate locking level, or to have the conversation with the real stakeholders about the advantages/disadvantages of not locking.

The second part, is let's say Chris really does have a bad table design that hurts his scalability. When you look at the options (NOLOCK, table redesign, more hardware) and combine with a risk analysis, I think NOLOCK seems pretty pragmatic. Should you fix the table design? That's entirely a business decision, not a how can I make my tables better question.

RE: The Effect of NOLOCK on Performance

kevin mann — Tue, 30 Jan 2007 08:12:00 GMT

Of course I dont bind myself to 1, 2, 3. If I did, I'd be out a job. If you're really concerned about data integrity, use server-side cursor recordsets bound to the table. Between the time a user does a select, edits the data, and then sends the update, a batch process or another user may have edited the data. Like I implied before, think it through before you design a process. What are the trade-offs? What are the benefits?

RE: The Effect of NOLOCK on Performance

colin.Leversuch-Roberts — Tue, 30 Jan 2007 03:23:00 GMT

chris - I'm not having a go here but -- I sometimes tire a little of posts that attempt to "throw" large numbers about - assuming your million page hits each required a transaction that's an average of 11 transactions/sec which is chicken feed to a sql database - at that level there shouldn't be issues with locking due to transactions ( really )

Problems with 150 million rows ? then partition, sorry but design and architecture are a critical part of operations - my view , in a general sort of sense, is that the underlying design/architecture is probably wrong which is why the need to use nolock - I will test more some time but I have not been able to see any time difference in running a query ( as a solitary operation ) with or without a nolock hint.

RE: The Effect of NOLOCK on Performance

xyvyx — Mon, 29 Jan 2007 17:45:00 GMT

as the former DBA (admin/architect) for a fairly high-traffic website, I can say that the NOLOCK / ...READ UNCOMMITTED hints proved to be invaluable. By high-traffic, I mean > 1 million pageviews/day with many users hitting tables as large as 150,000,000 rows.

Even with indexes created & tuned to maximize response times, NOLOCKs still made a huge impact. I was always a bit frustrated at the fact that I needed to add these hints to so many statements... and I remember going on many a witch-hunt looking for the source of blocking. To the guy that says "BLOCKING is GOOD"... no, blocking is a necessary side-effect caused by performance limitations and data integrity requirements.

Granted, we had to handle financial reporting differently, but for generic content that was stored in SQL tables, the benefits outweighed the problems.

RE: The Effect of NOLOCK on Performance

Simon Sabin — Mon, 29 Jan 2007 16:47:00 GMT

If you use NOLOCK in the first one, the form is filled with uncommitted data, however if this is edited and sent back to the database you are commiting data that might have been rolled back. In addition you might have overwritten data that had previously been written. A similar mechanism applies to 2. how do you know what has changed, if you reread the data you should make sure the data is consistent and not use NOLOCK.

With point 3, using nolock on batch reads you can easily read the same data twice and thus send emails etc twice, how does that look to your customer. If this is an order confirmation, does the user then think the order has been placed twice. More importantly, you are reading dirty data, what happens if in the order example an order has been partly saved and then failed. If your process uses NOLOCK then you could end up sending an email even though the order failed.

Too many people use NOLOCK without understanding what the impact can be on the business. Its not just financial systems that need to consider transactionality.

You can use NOLOCK but your are bypassing all the built in transactionality of SQL and so to maintain consistency you will have to do something your self, which makes it more complex.

RE: The Effect of NOLOCK on Performance

kevin mann — Mon, 29 Jan 2007 07:52:00 GMT

Is this a test?1. NOLOCK - I know there may debate to this, but most the time populating forms or reports is not that sensitive to completing transactions. This is an executive decision and basis-to-basis situation.2. I am unsure the effect of specifying locking levels on the actual UPDATE statement, but when there is business logic involved and/or triggered processes i.e. UPDATING using other tables, you want to use ROWLOCK as much as possible. I tend to use NOLOCK on large semi-static tables, or if I am simply searching an index for existing record(s) I didn't previously insert in that transaction.3. Almost always NOLOCK. Actually, this reminds me of something I caught a colleague on. He was polling our main table for items to send to a clearing house. He did this every .5 seconds. I was deadlocking all over the place. Granted, some of this had to do with the fact he was searching an un-indexed status field, but this was a perfect application for NOLOCK.