December 17, 2010 at 5:31 pm
Paul White NZ (12/17/2010)
Read Committed (the default) isn't much better. You won't lose scan position, but skipping records and double-reads are perfectly possible. Using a row-versioning isolation level (most usually read committed snapshot) is a huge win for this, and many other things too.
Alright, to borrow phrasing from Gail from here...
Possible in read committed because locks are released as soon as the read is done. Not possible in repeatable-read because in that isolation level the locks are held until the end of the transaction.
Yeah, momentary mental malfunction if you saw my first response. :Whistling:
I agree that it's possible, but less likely. Snapshot can cause other issues if you're doing updates with criteria based on selects and other fun things, if they happen concurrently. Meh, why is almost every satisfactory answer when dealing with databases "It Depends."?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 18, 2010 at 1:04 am
The isolation levels are trade-offs. Isolation and consistency vs concurrency. Pick the one that you need for your system. Read committed is usually a decent default, but it does depend.
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
December 18, 2010 at 2:48 am
Gail was just giving a brief high-level overview in that link you posted.
Missed reads are possible at REPEATABLE READ, and a scan at READ UNCOMMITTED does not always use an allocation-order (IAM) scan. Missed reads are possible because even though shared locks are held to end-of-transaction, rows in front of the scan can still move behind it (REPEATABLE READ allows phantoms). A key update is required, but it is not at all unusual to update non-clustered index keys 😉
See http://blogs.msdn.com/b/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx for a great visualisation of the issue, and a great introduction to the topic.
I agree that it's possible, but less likely.
Agreed, but beware of escalating 'less likely' so 'safe to ignore'. Here are two examples:
The first link shows COUNT(*) returning surprising results even at REPEATABLE READ, and the second shows three other cases, including one where it appears as if a check constraint has been violated.
Snapshot can cause other issues if you're doing updates with criteria based on selects and other fun things, if they happen concurrently.
Don't confuse full snapshot isolation (SI) with the read committed snapshot isolation (RCSI) I was talking about. I would be interested to hear more about what you mean by your statement here, to make sure neither of us are misunderstanding the other.
Gail and I are unlikely to disagree on many facts here, though we may place a different emphasis on them 🙂
She is quite right to say that each isolation level offers a trade-off between consistency and concurrency. I am NOT promoting NOLOCK (aka READUNCOMMITTED). My biggest concerns with the default READ COMMITTED isolation level are (a) few people understand what it does and does not guarantee; and (b) it leads people to resort to NOLOCK when they encounter excessive deadlocking due to reader/writer conflicts.
That's a purely practical observation, by the way. Most people will not spend the time to understand the reasons for deadlocks, and how to design and code to minimize them. Slapping a NOLOCK hint on every table in the query is an all-too-common 'fix', which just exasperates me.
RCSI does come with its own set of issues, but for many systems it offers some very big wins.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 61 through 62 (of 62 total)
You must be logged in to reply to this topic. Login to reply