SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ???

Question

Post reply

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ???

jbalbo

SSCertifiable

Points: 7583
More actions
October 14, 2021 at 6:55 pm
Go to Answer
#3940647

Hi,
If I use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in a stored procedure and that procedure uses select statements within select statements will the "...read uncommitted" apply to the selects within the main select , or do I need to use a (Nolock) on those ?

Thanks
ScottPletcher

SSC Guru

Points: 101105
More actions
October 14, 2021 at 7:43 pm
Answer
#3940657

The SET applies to all SELECTs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 7 posts - 1 through 6 (of 6 total)

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

jbalbo SSCertifiable Points: 7583 More actions · Answer 1

Thanks... I guess my issue is something else.... 🙂

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

I hope you also realize the downsides of using that isolation level !

Why the Query Hint NOLOCK is a bad idea

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Michael L John One Orange Chip Points: 27056 More actions · Answer 3

jbalbo wrote:

Thanks... I guess my issue is something else.... 🙂

What specifically is your issue?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Thanks for getting back,

I have this SP that runs on a reporting services application where the DB used to be Backup and restore.

Although it has a bunch of select statements within select statements it ran fine under Backup and restore.

They switched to replication and it slowed down significantly and I used SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid record locks

Michael L John One Orange Chip Points: 27056 More actions · Answer 5

jbalbo wrote:

Thanks for getting back,
I have this SP that runs on a reporting services application where the DB used to be Backup and restore.
Although it has a bunch of select statements within select statements it ran fine under Backup and restore.
They switched to replication and it slowed down significantly and I used SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid record locks

Can you elaborate on the proc "running fine"?? Does it run in an acceptable manner on the publisher? Can you provide details on the differences between before and after?

The main difference in architecture is that the only transactions that were occurring on the backup/restore would have been the reports. Now, with replication, there can be a much higher level of transactions occurring depending upon how busy the system is.

Setting the isolation level to read uncommitted does not avoid locks. It removes shared locks. Nolock and read uncommitted are not the "go fast" button.

This is one of many links on the subject. https://tenbulls.co.uk/2011/09/14/when-should-you-use-nolock/

Have you taken a look at the execution plan? Can you share the execution plan on this thread?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/