patternsqlMinor
Question about logging architecture
Viewed 0 times
loggingquestionaboutarchitecture
Problem
I understand that when (for example) an update is issued, the data pages are first read into the buffer pool, locks are acquired and changes are written out to the log file in the buffer, then the log file is written to the disk and later at a checkpoint dirty pages in the buffer pool are flushed out and gets hardened on the drive.
What I don't understand is, doesn't this mean the data in the data pages on the disk is stale until a checkpoint? Where does SQL Server read the data from when a select query is issued between "commit transaction" and the checkpoint flush?
Thanks.
What I don't understand is, doesn't this mean the data in the data pages on the disk is stale until a checkpoint? Where does SQL Server read the data from when a select query is issued between "commit transaction" and the checkpoint flush?
Thanks.
Solution
What I don't understand is, doesn't this mean the data in the data
pages on the disk is stale until a checkpoint?
On Disk - yes.
Where does SQL Server read the data from when a select query is issued
between "commit transaction" and the checkpoint flush? Thanks.
Memory. Let me explain a little more...
When a the update statement runs (say update mytable set columnA = 1 where columnA = 2) the change is logged and also made on the data page in memory. You've already said that the logging happens and is hardened (flushed to stable media) but the change is also made on the data page in memory. Thus, when the page is "read" by another transaction, the updated values are there.
There is something else that comes into play which are called latches. Locks keep logical consistency but latches keep physical consistency. The data page is latched, locks are taken, updates happen, logs are written and eventually flushed. The changes are always first made, in memory.
You might be thinking... "hey wait, if the changes are made to the data pages but logging isn't complete, the database is inconsistent!" That's close! The changes made to the data pages aren't reflected on disk yet - so if a crash were to happen, the data would be as it was pre-update which is how it should be. When the checkpoint occurs, WAL (write ahead logging) must still take place, so all LSNs up to the most recent reflected on data pages part of the checkpoint must first be flushed to disk. Thus the consistency is maintained and all is well in the world of SQL Server :)
pages on the disk is stale until a checkpoint?
On Disk - yes.
Where does SQL Server read the data from when a select query is issued
between "commit transaction" and the checkpoint flush? Thanks.
Memory. Let me explain a little more...
When a the update statement runs (say update mytable set columnA = 1 where columnA = 2) the change is logged and also made on the data page in memory. You've already said that the logging happens and is hardened (flushed to stable media) but the change is also made on the data page in memory. Thus, when the page is "read" by another transaction, the updated values are there.
There is something else that comes into play which are called latches. Locks keep logical consistency but latches keep physical consistency. The data page is latched, locks are taken, updates happen, logs are written and eventually flushed. The changes are always first made, in memory.
You might be thinking... "hey wait, if the changes are made to the data pages but logging isn't complete, the database is inconsistent!" That's close! The changes made to the data pages aren't reflected on disk yet - so if a crash were to happen, the data would be as it was pre-update which is how it should be. When the checkpoint occurs, WAL (write ahead logging) must still take place, so all LSNs up to the most recent reflected on data pages part of the checkpoint must first be flushed to disk. Thus the consistency is maintained and all is well in the world of SQL Server :)
Context
StackExchange Database Administrators Q#82718, answer score: 4
Revisions (0)
No revisions yet.