Checkpoints in DBMS

Pre-Requisite: Transaction Management 

The Checkpoint is used to declare a point before which the DBMS was in a consistent state, and all transactions were committed. During transaction execution, such checkpoints are traced. After execution, transaction log files will be created. Upon reaching the savepoint/checkpoint, the log file is destroyed by saving its update to the database. Then a new log is created with upcoming execution operations of the transaction and it will be updated until the next checkpoint and the process continues.

Why do We Need Checkpoints?

Whenever transaction logs are created in a real-time environment, it eats up lots of storage space. Also keeping track of every update and its maintenance may increase the physical space of the system. Eventually, the transaction log file may not be handled as the size keeps growing. This can be addressed with checkpoints. The methodology utilized for removing all previous transaction logs and storing them in permanent storage is called a Checkpoint. 

Steps to Use Checkpoints in the Database

1. Write the begin_checkpoint record into a log.
2. Collect checkpoint data in stable storage.
3. Write the end_checkpoint record into a log.

The behavior when the system crashes and recovers when concurrent transactions are executed is shown below:

Understanding Checkpoints in multiple Transactions

Transactions and operations of the above diagram:

• The recovery system reads the logs backward from the end to the last checkpoint i.e. from T4 to T1.
• It will keep track of two lists – Undo and Redo.
• Whenever there is a log with instructions <Tn, start>and <Tn, commit> or only <Tn, commit> then it will put that transaction in Redo List. T2 and T3 contain <Tn, Start> and <Tn, Commit> whereas T1 will have only <Tn, Commit>. Here, T1, T2, and T3 are in the redo list.
• Whenever a log record with no instruction of commit or abort is found, that transaction is put to Undo List <Here, T4 has <Tn, Start> but no <Tn, commit> as it is an ongoing transaction. T4 will be put on the undo list.

All the transactions in the redo list are deleted with their previous logs and then redone before saving their logs. All the transactions in the undo list are undone and their logs are deleted. 

Types of Checkpoints

There are basically two main types of Checkpoints:

1. Automatic Checkpoint
2. Manual Checkpoint

1. Automatic Checkpoint: These checkpoints occur very frequently like every hour or every day. These intervals are set by the database administrator. They are generally used by heavy databases as they are frequently updated, and we can recover the data easily in case of failure.

2. Manual Checkpoint: These are the checkpoints that are manually set by the database administrator. Manual checkpoints are generally used for smaller databases. They are updated very less frequently only when they are set by the database administrator.

Relevance of Checkpoints

A checkpoint is a feature that adds a value of C in ACID-compliant to RDBMS. A checkpoint is used for recovery if there is an unexpected shutdown in the database. Checkpoints work on some intervals and write all dirty pages (modified pages) from logs relay to data file from i.e from a buffer to a physical disk. It is also known as the hardening of dirty pages. It is a dedicated process and runs automatically by SQL Server at specific intervals. The synchronization point between the database and transaction log is served with a checkpoint. 

Advantages of Checkpoints

• Checkpoints help us in recovering the transaction of the database in case of a random shutdown of the database.
• It enhancing the consistency of the database in case when multiple transactions are executing in the database simultaneously.
• It  increasing the data recovery process.
• Checkpoints work as a synchronization point between the database and the transaction log file in the database.
• Checkpoint records in the log file are used to prevent unnecessary redo operations.
• Since dirty pages are flushed out continuously in the background, it has a very low overhead and can be done frequently.
• Checkpoints provide the baseline information needed for the restoration of the lost state in the event of a system failure.
• A database checkpoint keeps track of change information and enables incremental database backup. 
• A database storage checkpoint can be mounted, allowing regular file system operations to be performed.
• Database checkpoints can be used for application solutions which include backup, recovery or database modifications.

 Disadvantages of Checkpoints

1. Database storage checkpoints can only be used to restore from logical errors (E.g. a human error).

2. Because all the data blocks are on the same physical device, database storage checkpoints cannot be used to restore files due to a media failure.

Real-Time Applications of Checkpoints

1. Backup and Recovery
2. Performance Optimization
3. Auditing

1. Checkpoint and Recovery

A checkpoint is one of the key tools which helps in the recovery process of the database. In case of a system failure, DBMS can find the information stored in the checkpoint to recover the database till its last known stage.

The speed of recovery in case of a system failure depends on the duration of the checkpoint set by the database administrator. For Example, if the checkpoint interval is set to a shorter duration, it helps in faster recovery and vice-versa. If more frequent checkpoint has to be written to disk, it can also impact the performance.

2. Importance of Checkpoint in Performance Optimization

Checkpoint plays an essential role in the Recovery of the database. Still, it also plays a vital role in improving the performance of DBMS, and this can be done by reducing the amount of work that should be done during recovery. It can discard any unnecessary information which helps to keep the database clean and better for optimization purposes. 

Another way in which checkpoint is used to improve the performance of the database is by reducing the amount of data that is to be read from the disk in case of recovery. Analyzing the checkpoints clearly helps in minimizing the data that is to be read from the disk, which improves the recovery time. and in that way, it helps in Performance Optimization.

3. Checkpoints and Auditing

Checkpoints can be used for different purposes like Performance Optimization, it can also be used for Auditing Purposes. Checkpoints help view the database’s history and identify any problem that had happened at any particular time.

In case of any type of failure, database administrators can use the checkpoint to determine when it has happened and what amount of data has been affected.

Conclusion

Checkpoints are one of the essential elements of a Database Management System. The major contribution of Checkpoints in DBMS is, it helps in faster recovery of the system in case of system failure or crash of the system. Checkpoints mark the position till the consistency of the transaction is maintained. It is a useful component in the recovery of the database.