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 07, 2013 9:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 8:17 AM
Points: 317, Visits: 751
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 07, 2013 9:37 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
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 07, 2013 12:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1428210
Posted Thursday, March 07, 2013 1:08 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
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 07, 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: Today @ 1:11 PM
Points: 3,081, Visits: 11,230
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