March 30, 2016 at 1:21 pm
If I have a data table containing data for 3 items (say people), and I know that no one is modifying data for person 1, and I can reasonably believe that there are changes for the other 2 people, is it safe to use read-uncommitted if I'm explicitly retrieving data for person 1?
Thanks
ST
March 30, 2016 at 1:34 pm
No.
Data is being changed, therefore you may still get missing rows and duplicate rows (depends on how SQL accesses the table and where the other data modifications are and whether they are causing page splits). You won't get dirty reads, but you'll still get the other side effects.
Why not read_committed_snapshot or snapshot isolation levels if you don't want to risk blocking data modifications?
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
March 31, 2016 at 4:26 am
I'll look into those other options. I'm trying to avoid some deadlock errors in a heavy-reporting process.
Thanks, as always, for the help
ST
March 31, 2016 at 4:30 am
Fix the problem, not the symptoms. Deadlocks are mostly a case of poor indexing and/or inefficient queries..
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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
March 31, 2016 at 4:59 am
Interesting read. I may be experiencing heavy blocking and misspoke by saying deadlock. Basically I have a fairly high transaction system where I'm also running a heavy reporting process. I'm looking for a way to improve the reporting process. As I said earlier, the data being reported on is no longer moving so I was hoping that there was a safer way to retrieve this data. I'm not in a position to implement snapshot as there is a lot of testing that needs to be done.
I'll keep looking at other programming options as well.
ST
March 31, 2016 at 9:26 am
souLTower (3/30/2016)
If I have a data table containing data for 3 items (say people), and I know that no one is modifying data for person 1, and I can reasonably believe that there are changes for the other 2 people, is it safe to use read-uncommitted if I'm explicitly retrieving data for person 1?Thanks
ST
Yes, it's safe in the sense that person 1's data will be available.
But, unless person# is the (lead) clustering key (at least), if you use read committed or higher, you might have to wait to read it until the other UPDATE(s) have finished.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply