Shulou(Shulou.com)06/01 Report--

Checkpoints, redo, and undo for SQLServer

The relationship between checkpoint and recovery efficiency

Checkpoints flush dirty data pages from the buffer cache of the current database to disk. This minimizes the amount of modification that must be redone (Redo) during recovery.

Why can log-based recovery mechanisms be more efficient after checkpoints are set in log files? The figure shows the state of the transaction that may occur when the checkpoint occurs.

① transaction 1

Both its start and commit logging take place before the checkpoint, and the result of such a transaction has been reflected on the physical media (because the checkpoint guarantees the WAL protocol and ensures that the data is written), so there is no need to Redo the transaction during recovery.

② transaction 2

Its start logging occurs before the checkpoint, and its commit recording occurs before the point of failure, indicating that the transaction in the log has been perfectly committed, but the data may not have been written, so it belongs to a satisfactory transaction and requires Redo operation.

③ transaction 3

Its start logging occurs after the checkpoint, and its commit recording occurs before the point of failure, indicating that the transaction in the log has been perfectly committed, but the data may not have been written, so it belongs to a satisfactory transaction and requires Redo operation.

④ transaction 4

Its start logging occurs after the checkpoint, and its commit recording does not occur before the point of failure, indicating that the transaction in the log is aborted and requires Undo operation.

⑤ transaction 5

Its start logging occurs before the checkpoint, and its commit recording does not occur before the point of failure, indicating that the transaction in the log needs Undo operation to abort the transaction.

As can be seen from the mechanism of CheckPoint, because the data in memory is often newer than that in persistent storage, and CheckPoint ensures that this part of data can be persisted to disk, the data before CheckPoint will no longer need to be Redo.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.