SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)

Question

Post reply

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)

DBA-640728

SSChampion

Points: 12898
More actions
October 27, 2008 at 3:00 pm

#192773

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)
hi guys i have found several stored procedures in which i see both of this, for what i know only one is needed to read dirty reads and don't cause blocks is that correct? can i eliminate one? if so which one is faster for performance? thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please don't put questions into the thread title. It gets cut off.

What's the question?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

sorry here it is

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

DBA (10/27/2008)
hi guys i have found several stored procedures in which i see both of this, for what i know only one is needed to read dirty reads and don't cause blocks is that correct? can i eliminate one? if so which one is faster for performance? thanks in advance.

Yup. They are identical in meaning and in effect.

Better yet, remove both and remove the chance for dirty reads, dupe reads, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBA-640728 SSChampion Points: 12898 More actions · Answer 4

DBA-640728

SSChampion

Points: 12898

October 28, 2008 at 7:10 am

#890594

thank you!

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 5

out of curiosity, from a performance point of view, which is better:

setting WITH(NoLock) on every table or setting the isolation level (if different from Read Uncommited) at the start of each session?

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Swirl80 (12/19/2008)
out of curiosity, from a performance point of view, which is better:

As I said above, they are identical in meaning and in effect.

Neither is desired for performance reasons. If there's blocking problems the best thing to do is fix the cause, not hide they symptoms.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

GilaMonster (12/19/2008)[hr...Neither is desired for performance reasons. If there's blocking problems the best thing to do is fix the cause, not hide they symptoms.

Actually, because of the architecture of SQL Server 2000 it was very common for people to use one of those two methods for performance reasons. One of those two ways doesn't perform better than the other. In SQL Server 2000 SELECT statements blocked UPDATE statements untill the entire resultset had been read by the client program, and was worse if the transaction isolation level is Serializable or Repeatable Read it would hold on to the shared lock even longer.

With SQL Server 2005, a new architecture which included multi-version concurrency was introduced. This can be utilized with the following settings:

-- enable row versioning for the default Read Committed isolation level

ALTER DATABASE DevSchoolOneLMS

SET READ_COMMITTED_SNAPSHOT ON;

-- enable row versioning as a separate Snapshot isolation level

ALTER DATABASE DevSchoolOneLMS

SET ALLOW_SNAPSHOT_ISOLATION ON;

Enabling row versioning allows the default READ COMMITTED isolation level to see a consistant version of the row without resorting to SHARE locks that block UPDATE statements. The only cost to this is a little more space used in TempDB.