Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I ran the following code past our architect as I thought there may have been a tautology between the isolation level and table hint:

SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT  p.Forename ,
        g.Descriptor AS PersonGender
FROM    dbo.Person p WITH ( NOLOCK ) ,
        dbo.Gender g WITH ( NOLOCK )
WHERE   p.GenderId = g.Id;

I am assured by the architect (and I have no reason to doubt him) that this is for high volume environments and helps the optimiser better decide about how to improve the performance of the query.

So, some questions:

  • How does it do this?
  • Which versions of SQL Server support this?
  • Is this a documented or undocumented feature?
share|improve this question
    
I never use NOLOCK query hint or READ UNCOMMITTED isolation level in environment which is critical because of mainly dirty reads with other potential drawabacks. And yes both NOLOCK and read uncommitted isolation levels are supported –  Shanky Jul 23 at 12:25
    
Yes, the use of dirty reads is fully understood and can be achieved by one method or the other. My question is what using both methods at the same time brings to the party? –  Robbie Dee Jul 23 at 12:30
    
AFAIK nothing the transaction will start reading all the tables with read uncommitted isolation level. NOLOCK is basically a query hint and aplied only to particular table when used. Isolation level is specific with connection/query and applies to whole query. In your case you can remove NOLOCK query hints –  Shanky Jul 23 at 12:37
    
Setting the TRANSACTION ISOLATION LEVEL READ UNCOMMITTED affects all the tables in the query, NOLOCK affects only the one table with the hint. In this case the NOLOCK adds nothing further. –  RLF Jul 23 at 12:39

2 Answers 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at the transaction level or at the session level while NOLOCK is a query hint.

You mentioned that you fully understand dirty reads, so using at the transaction level is what I would recommend.

if you want dirty reads on some tables only, then NOLOCK hint will help you.

SQL server 2005 and up allows you to use SNAPSHOT isolation where readers don't block writers and it uses row versioning with some penalty on tempdb.

essentially SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and NOLOCK are same thing but the use depends on the context of what you are using.

share|improve this answer

WITH (NOLOCK) is the equivalent of using READ UNCOMMITTED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In any application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it. SQL Server 2005,2008 and 2008 R2 will support Nolock.

Pl look on the below link

http://www.mssqltips.com/sqlservertip/3172/avoid-using-nolock-on-sql-server-update-and-delete-statements/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.