0

Let's say the transaction in connection 1 tried to modify record A and then commit.Because the commit only flush log record in buffer to disk, it's checkpoint's job to flush all dirty page to disk (mdf). And there is a connection B tried to read record A after the connection 1's commit.(since A has committed, there is no lock thing here)

so my questions are:

Q1- if connection B read record A before checkpoint, does it mean it will read the old value?

Q2- if connection B read record A after checkpoint, does it mean it will read the new value?

Q3- isn't that weird that sometimes connection B get new value and sometimes it get old value, is the way to fix this problem is A needs to manually trigger a checkpoint?

5

To answer your specific questions about this scenario:

if connection B read record A before checkpoint, does it mean it will read the old value?

No.

if connection B read record A after checkpoint, does it mean it will read the new value?

It will read the new, committed value regardless of the CHECKPOINT.

isn't that weird that sometimes connection B get new value and sometimes it get old value, is the way to fix this problem is A needs to manually trigger a checkpoint?

No, issuing a manual CHECKPOINT is not necessary.


When a query needs to read a particular data page, it first tries to read that page from the buffer pool (RAM). This is called a "logical read."

If that page is not in the buffer pool, it will be read from disk, placed into the buffer pool, and then that in-memory value will be used by the query. This is called a "physical read."

The point being that rows / data pages that have been updated in RAM (so-called "dirty pages") can be read from memory by multiple queries, and by multiple connections, without first needing to be written to the data file on disk.

In fact, a value in the buffer pool can be updated multiple times before being written to disk.

The CHECKPOINT process is not really related to isolation levels, locking, or correctness of query results. It's mainly a performance optimization (to prevent lots of small writes from happening all the time) and a recoverability feature (to reduce the time it takes to recover the database to a consistent state from the log).


You can read a lot of in-depth information about these topics in the following Microsoft Docs articles:

  • @Josh Darnell Thanks for your detailed answer. but isn't that buffer pool the same thing as cache?so for different users, their cache on their computers are different, or the SQL Server has a dedicated centralized buffer pool to cache each record for different users so that all users from different machines read the same buffer pool? – slowjams Jul 4 at 3:46
  • The BP is the RAM (possibly extended to a file on disk, BPE), on the server. Not the client. A page is never cached twice. So, all users are reading the same (copy of the) page in RAM. To protect from concurrency issues, SQL Server uses locking and versioning, depending on isolation level and database configuration. – Tibor Karaszi Jul 4 at 6:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.