ISOLATION LEVEL READ UNCOMMITTED

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » ISOLATION LEVEL READ UNCOMMITTED

ISOLATION LEVEL READ UNCOMMITTED

Rate Topic

Display Mode

Topic Options

Author

Message

newbieuser

Posted Thursday, March 07, 2013 9:00 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 306, Visits: 722

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

GilaMonster

Posted Thursday, March 07, 2013 9:37 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 37,639, Visits: 29,893

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

opc.three

Posted Thursday, March 07, 2013 12:52 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693, Visits: 11,707

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

GilaMonster

Posted Thursday, March 07, 2013 1:08 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 37,639, Visits: 29,893

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

Michael Valentine Jones

Posted Thursday, March 07, 2013 3:23 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481

Or find a vendor that knows what they are doing with SQL Server. Crying

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 »

Permissions