James Stephens (6/18/2011)
Hi all,I have a related question that I think was somehow answered in the above posts but I just wanted clarification for academic purposes:
Suppose I change a field of data from Table-1 from "Some Data Before" to "Some Data After".
Now suppose that this is not committed but due to memory pressure or some other reason there's a checkpoint
Memory pressure doesn't enter into when a checkpoint is done. It's happens automatically only when the Checkpoint Handler estimates that there is too much uncheckpointed information in the log, and recovering it would take longer than recovery_interval minutes.
and it's written from the log cache to the disk (.Ldf),
No, this happens far more often than at checkpoint time. The article asserts that the .LDF is written to at commit time, but my question about the million rows shows that it happens before then.
and also from data cache to the disk (.mdf).
This is correct. Dirty pages in the data cache, committed or not, are written to the .MDF
Now I want to do a Select with "read uncommitted" (I'm not fully sure but I think this is done using "With (NoLock)"??).
No, read uncommitted means "I will ignore the exclusive locks of other users as I do my reads, and read the uncommitted changes they have made to the row". Also known as a Dirty Read, there is the risk of reading something that will soon be rolled back by the other user.
You can enable it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Not that I'm saying this is a good idea.
From where does my Select statement fetch the "Some Data Before"? Does it, seeing the With(Nolock), divert the request to the .ldf data and step back to the last committed value of that particular row?
Quick rule: Unless you are in the middle of recovery, or some other unusual process, the log is not read. In particular, a select statement will never read the log.
Your select statement, like any other, will first look for the rows you want in cache. If it finds it there, all's good - if not, the page of rows is read from disk.
Notice that you doing a select is very different from someone else doing a select on the changes you've made. Your connection can see all the changes you have made, committed or not. Other users can [ordinarily] see only the changes you have committed. Unless, of course, they have turned on READ UNCOMMITTED, which they shouldn't do.
For more complexity, look in Books Online for Snapshot Isolation.