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.
Viewing 2 posts - 61 through 63 (of 63 total)
You must be logged in to reply to this topic. Login to reply