MySQL's checkpoint

At the end of the previous chapter, we left the question that in the model described in the previous chapter, the recovery manager had to perform a log recovery through the entire Undolog scan, which is obviously not necessary because the system outage must have been affected in the last few transactions. The previous transaction should have completed commit or rollback, no abort, then how do we know which transactions are affected, and if we know which ones are affected, then we can scan without a complete sweep, and just scan a small part of it. The following describes how the database knows which transactions are affected, and the database has introduced the concept of Checkpoint (checkpoint) for this purpose.

Checkpoint Checkpoint

Checkpoint, which is the checkpoint. Writing a checkpoint in Undolog indicates that the transaction before checkpoint has completed commit or rollback, that is, the transaction before the checkpoint already has no data consistency problem. So how does this checkpoint come true? In fact, the mechanism of implementation is very simple, is periodic to undolog inside write. Of course, this write must not be casually written in, in the writing, it is sure to check whether the previous transaction is completed.

This time there is a problem, because the database is always running, that is, the transaction is constantly started, and there may be n transactions are already in the start state. When the checkpoint is written, there may be a new transaction started, and if the checkpoint waits until there is no new transaction to start and all the previous transactions have been submitted, the basic checkpoint will not have to be written. So, in this case, you can only stop accepting new transactions when the checkpoint is written, wait for the started transaction to complete, and then write the checkpoint. Then continue to accept the new transaction. Similar to this: for example, there are now T1 T2 two transactions, then Undolog writes:

Undolog
Start T1
<T1,A,x>
Start T2
<T2,B,y>

At this point in the checkpoint cycle, to write to the checkpoint, you have to wait until t1,t2 all submitted, and then write to Checkpoint Chkpoint. That is, if there is a T3 to open now, it cannot be opened. The system is in a tamper state. After writing, turn on T3, logging as follows:

Undolog
Start T1
<T1,A,x>
Start T2
<T2,B,y>
End T1
End T2
Chkpoint
Start T3

At this point, if the system hangs, the recovery manager will scan forward from the tail of Undolog, scan to checkpoint, will not scan forward, because the previous transactions have been submitted, there is no data consistency problem. So you just have to redo it from checkpoint.

This is good, eliminating the need to undolog scanning from the trouble, but the shortcomings of this is also very obvious, that is, in the process of writing checkpoint, the system is in the state of compaction, all the write to pause. Can there be a better way to write checkpoint without the need for a system pause, which, of course, is the non-static checkpoint to be said below.

Non-static checkpoint

A non-static checkpoint is relative to a static checkpoint, as mentioned above is a static checkpoint because the system cannot write while the checkpoint is written. The introduction of non-static checkpoints is to solve this problem.

The policy of a non-static checkpoint is to record the currently active transaction while writing the Chkpoint. For example, in the current state, T1 and T2 are active, then Undolog will be written to start Checkpoint (T1,T2), then the overall system is still normal write, that is, after this log write, still can continue to open other transactions. When T1,T2 is complete, the record for end checkpoint is written. For example, the following records:

Undolog
Start T1
<T1,A,x>
Start T2
<T2,B,y>
Start Checkpoint (T1,T2)
Start T3
End T1
End T2
End Chkpoint
Start Checkpoint (T3)
End T3
End Chkpoint

The above log record is that after T1,T2 begins, Undolog writes the checkpoint for start checkpoint (T1,T2), which is still acceptable at the beginning of other transactions, with the opening of the T3 transaction.

This mechanism can effectively avoid the need to stop the service when the checkpoint is written, but now the problem has come, so it is good to write checkpoints, but how the recovery manager through such a undolog to the data recovery operation? Because, if the checkpoint is still, after finding the checkpoint, you do not have to look forward, and now it is different, because after finding the end checkpoint, there may still be unfinished transactions, then the data recovery is how to recover it?

In this case, after the database goes down, the recovery manager still scans the undolog from the tail, and if "End Chkpoint" is encountered, it does not mean that all the transactions before checkpoint have been committed, but we can know that All uncommitted transactions are after the last start checkpoint, so go ahead and find the start checkpoint, find the start checkpoint, such as Start Checkpoint (T1,T2), Because the end chkpoint has already been found, t1,t2 these two transactions can guarantee the consistency of the data, the need to redo is in the start Checpoint (T1,T2) to end chkpoint between the non-T1,T2 transactions, which need to be redo, So we're going to redo this.

In another case, when the recovery manager is scanning, it first encounters the log of start Checkpoint (T1,T2), in which case we first know that T1,t2 is probably an unfinished business, which is required at the start After checkpoint, find out if there is a transaction end statement, if there is, that the transaction is complete, if not, the description is not completed, then you need to look back from Check point, find the start of this transaction, and then redo from start. To be more verbose, let's illustrate the situation in the last example.

For example, after the database goes down, scan Undolog to get the following fragment:

Undolog
Start T1
<T1,A,15>
Start T2
<T2,B,39>
Start Checkpoint (T1,T2)
Start T3
<T3,C,40>
End T1
<T3,C,50>

At this point, the recovery manager to take this fragment after the scan, encountered in the end Chkpoint before the start checkpoint (T1,T2), which shows that T1,T2 is likely to not complete the transaction, and before this also encountered T3 start, no end T3, There is no T3 of the beginning of the checkpoint, which shows that T3 must be unfinished business, so T3 must be redo. Why was it that T1,t2 was probably unfinished? Because encountered the start checkpoint (T1,T2), did not encounter end Chkpoint, does not mean that T1 and T2 must be unfinished, there may be a commit, because there is no commit two, so it led to no end Chkpoint, so at this time to find the start below the log, found the "End T1", indicating that the T1 transaction is completed. That just need to find T2 to open and then start redo on it, and then through the start Checkpoint (T1,T2) to look up, found the start T2, and then began to redo T2, that is, this log, T2 and T3 is needed to redo, and then redo. (note: Just said to do T3, and then have said Redo T2, does not mean that the real order is this, in fact, the recovery manager is the first to analyze the need to redo the transaction, and then a piece to do off. )

MySQL checkpoint