ISOLATION LEVEL READ UNCOMMITTED

Question

Post reply

ISOLATION LEVEL READ UNCOMMITTED

newbieuser

SSCrazy Eights

Points: 9423
More actions
March 7, 2013 at 9:00 am

#269386

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, 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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, 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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

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.