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

ISOLATION LEVEL READ UNCOMMITTED Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 9:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
Hi friends,

Our 3rd party vendor provided us a patch with several stored procedures that has SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement in them.. I believe this statement heavily uses dirty reads so wondering if there is a possibility of data impact in our application due to this? They seem to be using it in every simple procedure.. Do you recommend using this statement for any specific type of DML's (insert,delete etc) in the procedure?

Would like to hear from you experts.. Any help is appreciated.. Thanks a lot
Post #1428042
Posted Thursday, March 7, 2013 9:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:05 AM
Points: 39,952, Visits: 36,305
Read uncommitted, the isolation level that should be called 'Potentially inconsistent data'

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx



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 #1428066
Posted Thursday, March 7, 2013 12:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
newbieuser (3/7/2013)
Do you recommend using this statement for any specific type of DML's (insert,delete etc) in the procedure?

For most DML operations it will be ignored since exclusive locks will always be required when modifying the data. Where you get into trouble is from doing INSERT...SELECT with a NOLOCK hint or READ_UNCOMMITTED iso level.

I would suggest you read the article Gail posted and try pushing back on these changes, i.e. remove all instances from the code where the iso level is being set to READ_UNCOMMITTED and remove all NOLOCK hints. Instead, have them look into enabling READ_COMMITTED_SNAPSHOT mode at the database level.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1428210
Posted Thursday, March 7, 2013 1:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:05 AM
Points: 39,952, Visits: 36,305
opc.three (3/7/2013)
Instead, have them look into enabling READ_COMMITTED_SNAPSHOT mode at the database level.


Or ALLOW_SNAPSHOT_ISOLATION and SET TRANSACTION ISOLATION LEVEL SNAPSHOT



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 #1428232
Posted Thursday, March 7, 2013 3:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502
Or find a vendor that knows what they are doing with SQL Server.

If this is an application that is connection pooling, then that can cause other problems in unrelated procedures, because the API sp_reset_connection stored procedure does not reset the transaction isolation level to the default. The means that the next call that uses that connection will also be using read uncommmitted, unless you explicitly set the isolation level.

I consider that a bug, but Microsoft seems to think it is just "working as designed". It's a very hard problem to debug, which I found out the hard way.

Post #1428291
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse