Hi
two questions on SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED when in a stored procedure
Thanks
Yes it matters. Put it before the statements you want it to apply to. It applies to all of the statements which follow it in the proc, unless you issue another command to change it.
There are some other questions you should ask yourself, before using it:
3) Am I happy for my SELECT queries to occasionally return data which has not yet been, and may never be, committed? So-called 'ghost data'.
4) Am I happy for my SELECT queries to occasionally return multiple versions of the same data row?
5) Am I happy for my SELECT queries to occasionally fail to return rows which should be in the resultset?
As long as you understand that any of the above may happen, under certain circumstances, and accept that, you're all good.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 1, 2021 at 5:21 pm
Thanks for getting back Phil.
So a select within a select is covered as long as it's before...
I get the drawbacks but it's for a table I build for a report with data at least 3 days old.
Maybe you can point me where I'm having an issue.
This SP(nightly job) used to run off a DB that was from a backup restore. Took a bit to run 45minutes
We switched to a transactional Replicated DB, I create a backup and restore this a DB(the replicated DB) nightly to another DB (call it DB_Backup) for my own jobs. The job now never finishes... at least 6+ hours into it.
I guess my question is why so slow with replication and can I make the settings for my"DB_Backup" so its quicker.
Thanks...
July 1, 2021 at 5:27 pm
OK, it's good that you understand the implications.
My experience with replication is limited, and therefore so is my advice! I think that you need to be monitoring what is going on on the server while your job is running in order to start tracking this down. Unless the TRANSACTION ISOLATION LEVEL change works, of course.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 1, 2021 at 5:32 pm
Thanks... ll' mess around in my sandbox 🙂
July 1, 2021 at 5:49 pm
But note that you can get phantom reads and nonrepeatable reads in the default READ COMMITTED mode as well.
And that the majority of issues with NOLOCK are caused by SQL using allocations scans, which you can prevent.
So dirty reads are the real issue with UNCOMMITTED. And, in a properly designed system that doesn't do many rollbacks, they are often not that big of a problem for most reports.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply