Lifecycle of a single row update
end-to-end write path.
buffer manager faults the page in, the row is modified in memory marking the page dirty, a WAL record is written, and commit fsyncs the log while the dirty page is flushed later by a checkpoint.
WHAT THIS TESTS The interviewer wants a precise, correctly ordered walkthrough showing you understand that commit durability comes from the log, not from flushing data pages.
A GOOD ANSWER COVERS The transaction targets a row whose page is not cached. First the buffer manager looks up the page in the buffer pool and misses, so it allocates a frame, possibly evicting a victim page and flushing it first if that victim is dirty, then issues a disk read to load the target page into memory. With the page resident, the engine acquires the needed locks and latches and applies the change to the row in place in the buffer, marking the page dirty and stamping it with the log sequence number of the change. Before or as the change is made, it writes a WAL record describing the modification into the log buffer, honoring write-ahead ordering so the log record's durability precedes the page's. At commit, the engine writes a commit record and fsyncs all of the transaction's WAL records to disk, and only then acknowledges the commit to the client. The dirty data page itself stays in memory and is written back to the data file later, asynchronously, by the background writer or at the next checkpoint.
COMMON WRONG ANSWERS Writing the data page to disk at commit; only the WAL is forced. Forgetting the page fault and possible dirty-victim eviction. Omitting the write-ahead ordering between log and page. Skipping locks and latches.
LIKELY FOLLOW-UPS What happens if the victim page is dirty; why must the log record precede the page write; when does the dirty page actually reach disk; what role does the LSN play in recovery.
ONE CONCRETE EXAMPLE Updating a customer's balance: the buffer pool misses, evicts a clean page, and reads the customer's page from disk. The engine changes the balance in memory, marks the page dirty, and logs the before and after image to the WAL. On COMMIT it fsyncs the WAL and returns success in microseconds. Ten seconds later a checkpoint finally writes that dirty page to the data file.
Read the original → learn.microsoft.com
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.