SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)

Author
Message
DBA-640728
DBA-640728
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3067 Visits: 2000
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227659 Visits: 46339
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
DBA-640728
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3067 Visits: 2000
sorry here it is

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227659 Visits: 46339
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
DBA-640728
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3067 Visits: 2000
thank you!
Chris Taylor
Chris Taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1314 Visits: 1918
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?

_________________________________________________________________________________

SQLGeordie

Web:- Jarrin Consultancy
Blog:- www.chrisjarrintaylor.co.uk
Twitter:- @SQLGeordie
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227659 Visits: 46339
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
Chris Harshman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11001 Visits: 4677
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search