# In database buffering why is it important : no writes to a block (which is to be moved from the disk buffer section of main memory) be in progress?

I was going through the concept of database buffering from the Database System Concepts text by Korth et. al where I came across the excerpt below.

The rules for the output of log records limit the freedom of the system to output blocks of data. If the input of block $$B_2$$ causes block $$B_1$$ to be chosen for output, all log records pertaining to data in $$B_1$$ must be output to stable storage before $$B_1$$ is output. Thus, the sequence of actions by the system would be:

1. Output log records to stable storage until all log records pertaining to block B\ have been output.
2. Output block $$B_1$$ to disk.
3. Input block $$B_2$$ from disk to main memory.

It is important that no writes to the block $$B_1$$ be in progress while the system carries out this sequence of actions. We can ensure that there are no writes in progress by using a special means of locking.

What I do not get is, why it is the case that: no writes to a block (which is to be moved from the disk buffer section of the main memory) be in progress while the system carries out this sequence of actions?

Since there is a need to remove block $$B_1$$ from memory, it might be the case that the CPU is with some other transaction $$T_j$$ (with is working concurrently with the transaction $$T_i$$ using block $$B_1$$). Assuming a single processor system, the transaction $$T_i$$ must have been pre-empted. So why do we need to check whether some writing is in progress in $$B_1$$ with respect to $$T_i$$. Just as it happens in OS paging concept, we could remove $$B_1$$ and invalidate all the table entries for each transactions using $$B_1$$ and bring $$B_2$$ in memory. [Just like replacing a page in memory, and setting the valid/invalid bit to invalid to the page table of the process].

• Concurrency management in DBMS is quite a different beast from concurrency management in Operating Systems, and the buffering of database pages is completely different from the virtual memory page management. Jun 1 '21 at 7:52
• @Renzo Thanks for the help. I am new to the subject DBMS, so I am yet to explore many things, and it seems that I was falsely trying to co-relate this topic with that of OS. Jun 1 '21 at 20:58

First off, let's remember the ACID of transactions:

• Atomicity means that either the transaction happens or it does not happen. The transaction never partly happens.
• Consistency means that a transaction transforms a database from a valid state to a valid state.
• Isolation means that if multiple transactions run concurrently, the resulting state of the database is as if the transactions ran in some sequential order.
• Durability means that if a transaction has committed, it stays committed, even if the machine crashes or there is a power outage.

Just looking at the case of a single transaction for a moment, and think about durability. How do we ensure that once a transaction has committed (that is, the client has been told that it has committed), it stays committed, even when multiple page updates are involved?

The approach is this:

• We write all changes to the log and ensure that the log has been flushed to disk. When this is done, the transaction can be considered committed.
• We queue dirty the pages for writing. This can happen in any order, which is important for performance on rotating platter disks.
• Once the dirty pages have been written, we no longer need the log.

If a crash occurs, it doesn't matter that not all dirty pages were written, because all of the information is in the log. It can simply be replayed when the database starts up again.

Allowing writes a dirty page before it is written would interfere with the already-committed transaction, because of the way that replay works. The log records just enough information in the log to be able to tell if the modified page has been written or not, and if not, to reconstruct the change given the old page.

Committing a different version of the page to disk, which contains the change and even more changes on top if it, would mean that the replay mechanism would now have to contend with three or more possible versions of the page, and sort them all out.

• Thank you. I have learned about the above points. But unfortunately, I could not get the core idea in your answer as an argument to:In database buffering why is it important: no writes to a block (which is to be moved from the disk buffer section of main memory) be in progress? Could please elaborate a little more, pinpointing the reasons which you say regarding my question. [I have started learning the DBMS subject, that's why maybe my intuitions are not so strong yet]. Jun 1 '21 at 20:53
• Let me go at this from another angle. If you only allow writing pages to disk after the log has been written to disk, and if you disallow modifying pages between the time that the log has been written and the page has been written to disk, then any given page can only be in two states: either what it was before the transaction occurred, or what it was after the transaction occurred. Jun 2 '21 at 0:45
• ...which means that it cannot contain any modifications from subsequent transactions. This makes the replay mechanism much simpler, because it only has to deal with two cases. Jun 2 '21 at 0:46
• Do remember that this is only one possible algorithm. In this algorithm, the purpose of disallowing writes to dirty pages is so that the log can be cleared. Today, disk is relatively cheap but still slow relative to RAM, and so you can get more performance by allowing updates to dirty pages at the cost of using larger logs. Rather than locking pages after every transaction, you do it at fixed intervals (e.g. every 10 seconds) or when the log grows past some size. Jun 2 '21 at 0:56
• The textbook has a specific scenario in mind, where "the input of block B2 causes block B1 to be chosen for output". I assume that means that it wants to read in B2, so must write B1 to make room for it. A transaction can't be modifying B1 at the same time, otherwise it wouldn't be chosen for writing. And real database systems tend to use a "not recently used" page-out policy, which would mean that an "active: page would not be chosen for paging out. It must be locked from becoming "active". Jun 3 '21 at 7:13