Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK) Expand / Collapse
Author
Message
Posted Monday, October 27, 2008 3:00 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
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.
Post #592463
Posted Monday, October 27, 2008 3:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
Please don't put questions into the thread title. It gets cut off.

What's the question?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #592468
Posted Monday, October 27, 2008 3:33 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
sorry here it is

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED VS WITH(NOLOCK)
Post #592486
Posted Monday, October 27, 2008 3:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #592492
Posted Tuesday, October 28, 2008 7:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
thank you!
Post #592853
Posted Friday, December 19, 2008 4:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:12 PM
Points: 398, Visits: 1,720
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?


Chris

Blog:- chrisjarrintaylor.co.uk
Twitter:- @SQLGeordie
Post #622765
Posted Friday, December 19, 2008 6:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
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

Post #622884
Posted Friday, December 19, 2008 8:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:48 AM
Points: 1,854, Visits: 2,011
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.
Post #623060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse