read uncomitted doesn't work

Question

read uncomitted doesn't work

MattTheDBA

SSC Eights!

Points: 960
More actions
September 2, 2011 at 3:26 am

#247234

I have a stored procedure which does a select to return data for a report. The sp has set transaction isolation level read uncommitted but it's blocking a load of other processes. Any ideas why?
Cheers

Viewing 10 posts - 1 through 9 (of 9 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 doesn't mean No locks at all. It means no shared locks taken by reads. It still takes update and exclusive locks, it still takes schema locks.

To answer your question you're going to have to look at what the blocking resource is, what locks are held and what locks are requested.

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

MattTheDBA SSC Eights! Points: 960 More actions · Answer 2

The sp only does a select, no updates, inserts, deletes or schema modifications. Would you still expect a lock?

Thanks

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

Every single query takes a schema lock to ensure that the schema doesn't change while it's running.

Other than that, my previous statement holds, not much more can be said without some info on what locks are held and requested.

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

Just understand, it's a common misconception, but putting in READ UNCOMITTED or NOLOCK is not a "run faster" switch for SQL Server. It can increase some performance in some areas, but it can result in duplicate or missing rows. So you need to know what you're doing with these things before you use them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MattTheDBA SSC Eights! Points: 960 More actions · Answer 5

Duplicate or missing rows is not a concern in this instance but blocking other processes is.

Many thanks

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

Considered snapshot isolation?

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

GilaMonster (9/2/2011)
Considered snapshot isolation?

+1

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

I have not seen blocking caused by a select when doing uncommitted reads unless the other stored procs are doing something with the Schema like Gail mentioned. You can still see heavy waits even if you are using uncommitted reads.

Just my 2 cents.

-Roy

MattTheDBA SSC Eights! Points: 960 More actions · Answer 9

I think I need to do some more investigation to see what locks are being held.

Thanks everyone for your help. I may be back!